Which range is the active cell in?

[bhsoundman]

Hi All,

I'm trying to determine which range my active cell is in. I have a working method, however I'm trying to avoid having to name a 100 ranges & have 100 cases in VBA.

For example if the active cell is within columns A:E, then A1 should be selected when the macro runs. If it's within columns F:J, then F1 should be selected. It would follow the same pattern for 100 possible range options. Every range option would be the same number of columns each time. & the cell that gets selected will always be in the same relative location (A1, F1 etc etc)

Do I just have to bite the bullet & start naming ranges & cases?

Thanks in advance for you expertise!

 

[jolivanes]

It's an interesting thread, for me anyway, but what do you want the result to be? If I remember right, originally it was to select the cell but in some of the follow ups I see that a value is the result.
I don't know if you have tried the two suggestions in Posts 16 and 18 and I don't even know if the results of these is what you actually want because there is no reference saying that it works or not, just some useful info. Anyway, neither of these have any looping.

 

[StephenCrump]

After testing some more, your non-looping version is great! Thanks so much for your advice!

OK, thanks, but please see correction in Post #12.

There was an incorrect "+1" in the formula. To match your results, the code should be:

Sub Test()

    Dim ColumnStart As Long, N As Long
    Dim currentshow As Variant
  
    ColumnStart = 4
    N = 32
  
    currentshow = Cells(1, ColumnStart + N * Int((ActiveCell.Column - ColumnStart) / N)).Value
    
End Sub