Excel - Test if two consecutive digits in the range's address are the same

user_19005072 0
Viewed 72

I want to offset a range if the numerical part of a range's address can be divided by 11. (A11, A22, A33, and so forth).

Take a range in a given sheet, for example Range("A2"). Could I do ...

        Dim isRng as Range
        Dim rngAddress as String
        Dim tstAddress as Integer, nsnAddress as Integer
 
        isRng = Range("A2")
        isRng.Select 
        rngAddress = isRng.Address

Currently, rngAddress = $A$2 (I think). So then, could I ...

        tstAddress = Right(rngAddress, 2)
        nsnAddress = Right(tstAddress, 1)

        If tstAddress / nsnAddress = 11 Then
             'whatever code
             Set isRng = ActiveCell.Offset(4,0).Select
        Else
        End If

I want it to skip down 4 rows after hitting any range like A11 or A22. Would this work? Is there a better way of doing this? I really appreciate the help.

offset range vba excel
2 Answers

Divisible: Using Mod

If cCell.Row Mod 11 = 0 Then

Option Explicit

Sub Divisible()
    
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    
    Dim rg As Range: Set rg = ws.Range("A2:A33")
    
    Dim cCell As Range
    Dim r As Long
    
    For Each cCell In rg.Cells
        r = r + 1
        If cCell.Row Mod 11 = 0 Then
            Debug.Print r, cCell.Offset(4).Address, "***"
        Else
            Debug.Print r, cCell.Address
        End If
    Next cCell
    
End Sub
user_98140690

This should do the trick...

Sub sully_was_here()
    Dim r As Range
    
    Set r = [a22]
    With r
        .Select
        If .Row Mod 11 = 0 Then
            'whatever code here
            .Offset(4).Select
        End If
    End With
End Sub
user_35669980
Related