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


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")
        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
        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.


This should do the trick…

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

Answered By – Excel Hero

Answer Checked By – Cary Denson (AngularFixing Admin)

Leave a Reply

Your email address will not be published.