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!

 

[bhsoundman]

Well, for what it's worth, I think I figured out a reasonably compact script. This is adjusted to the actual application. I'm certainly open to better ideas though.

Sub WhichJob2()

Dim ColumnStart As Long
Dim ColumnEnd As Long

ColumnStart = 4

    For ActiveRange = 4 To 3202
            ColumnEnd = ColumnStart + 31
                    If ActiveCell.Column >= ColumnStart And ActiveCell.Column <= ColumnEnd Then
                            currentshow = Cells(1, ColumnStart).Value
                            MsgBox currentshow
                            MsgBox ActiveCell.Column & vbNewLine & ColumnStart & vbNewLine & ColumnEnd
                    End If
            ColumnStart = ColumnStart + 32
    Next

End Sub

 

[Micron]

Your ranges are in groups of 5? Then this should get you a "range group" value (my term):
-Int(Cells(5, i).Column / 5 * -1) If in A to E I get 1. If 6 to 10 I get 2 and so on.
I see you posted while I was posting this so I'll stop here to see if your idea is better. I was thinking to use the ASCII values for a, b, c etc. along with using the number returned by that expression to get a letter, but I'm not there yet.

EDIT - or will your column label go beyond Z, like AA?

 

[jolivanes]

I am on the way out of the door but in the meantime have a look at using the MOD operator in conjunction with you activecell column.
I am sure Micron has a solution for you.
If you don't have one by tomorrow, I might have some time but can't promise for now.

 

[bhsoundman]

Your ranges are in groups of 5? Then this should get you a "range group" value (my term):
-Int(Cells(5, i).Column / 5 * -1) If in A to E I get 1. If 6 to 10 I get 2 and so on.
I see you posted while I was posting this so I'll stop here to see if your idea is better. I was thinking to use the ASCII values for a, b, c etc. along with using the number returned by that expression to get a letter, but I'm not there yet.

EDIT - or will your column label go beyond Z, like AA?

Thank you for having a look! In the actual application (the script I posted), there are 3202 columns that make up the 100 groups, so I up in column DSD

 

[bhsoundman]

I am on the way out of the door but in the meantime have a look at using the MOD operator in conjunction with you activecell column.
I am sure Micron has a solution for you.
If you don't have one by tomorrow, I might have some time but can't promise for now.

Thanks for the idea. I'll look into the MOD operator. I did end up posting a script that works FWIW.

 

[Micron]

I started out with MOD but dropped it because each group of 5 caused the remainders to repeat. I couldn't help myself and played with my expression. this

Sub getColumn()
Dim i As Integer, colNum As Integer

For i = 1 To 25
colNum = -Int(Cells(5, i).Column / 5 * -1)
Debug.Print "column " & i & "   " & Chr(64 + colNum) & "   " & -Int(Cells(5, i).Column / 5 * -1)
Next

End Sub

outputs
column 1 A 1
column 2 A 1
column 3 A 1
column 4 A 1
column 5 A 1
column 6 B 2
column 7 B 2
column 8 B 2
column 9 B 2
column 10 B 2
column 11 C 3
column 12 C 3
column 13 C 3
column 14 C 3
column 15 C 3
column 16 D 4
column 17 D 4
column 18 D 4
column 19 D 4
column 20 D 4
column 21 E 5
column 22 E 5
column 23 E 5
column 24 E 5
column 25 E 5

but would only work up to Z so it's likely not usable (at least not so far).

 

[StephenCrump]

I did end up posting a script that works FWIW.

Without looping:

 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 + 1) / N)).Value

End Sub

 

[StephenCrump]

there are 3202 columns that make up the 100 groups, so I up in column DSD

Yes, DSD is column 3,202, but you're looping from 4 to 3022, i.e. 3,199 times, so ColumnStart ends up with the value 102,372 (4+32*3,199), well beyond the last column of an Excel worksheet.

 

[DanteAmor]

If the initial column is going to be 4, then it could be like this:

Sub Active_Column()
  Dim col As Long
  
  col = ActiveCell.Column - ActiveCell.Column Mod 4
  MsgBox Cells(1, col).Address
End Sub