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!

 

[Micron]

With that, I get $X$1 when I select Z10? The code is in the same sheet module as the sheet where Z10 is selected. Maybe the wb is corrupted?

 

[StephenCrump]

With that, I get $X$1 when I select Z10?

If you're referring to @DanteAmor's Post #10, try instead:

MsgBox Cells(1, ActiveCell.Column - (ActiveCell.Column - 4) Mod 32).Address

EDIT: And Oops, my clunkier version in Post #8 should be:

currentshow = Cells(1, ColumnStart + N * Int((ActiveCell.Column - ColumnStart) / N)).Value

 

[DanteAmor]

With that, I get $X$1 when I select Z10?

X, Y, Z and AA
They belong to X1

Of course you can shorten the code without using a variable.
I just wanted to make it more elegant.

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

or:

Sub Active_Column()
  MsgBox Cells(1, ActiveCell.Column - ActiveCell.Column Mod 4).Address
End Sub

In both cases the result is X1.

 

[Artik]

It came out to me on this way

Sub WhichJob3()
    Dim rng As Range
    Dim rngAC As Range
    Dim lCol As Long
    Dim k As Long

    Set rngAC = ActiveCell
  
    If Not Intersect(rngAC.EntireColumn, ActiveSheet.UsedRange.EntireColumn) Is Nothing Then
        lCol = rngAC.Column
        k = lCol Mod 5

        Set rng = Cells(1, lCol - IIf(k = 0, 5, k) + 1)
        rng.Select
      
        MsgBox "The range address of the active cell is " & rng.Resize(, 5).EntireColumn.Address(0, 0)
      
    Else
        MsgBox "You are out of the used range!", vbExclamation
    End If
End Sub

Micron, instead of Chr(64 + colNum) use Split(Cells(1, colNum).Address(, 0), "$")(0)

Artik

 

[Micron]

instead of Chr(64 + colNum) use Split(Cells...

I did find and use that later but haven't been working on it since there seems to be far better approaches at present.

 

[jolivanes]

Sub Maybe()
Select Case ActiveCell.Column Mod 5
    Case 2 To 4
        Cells(1, ActiveCell.Column - (ActiveCell.Column Mod 5 - 1)).Select
    Case Is = 0
        Cells(1, ActiveCell.Column - 4).Select
    Case Is = 1
        Cells(1, ActiveCell.Column).Select
    Case Else
End Select
End Sub

 

[jolivanes]

If you don't need to select the cell in the first row, what I initially thought you wanted, put the cell address or its value in a cell or in a message box. Let us know if you need any help with any of this.

 

[jolivanes]

Another possibility maybe

Sub Or_So()
Dim col As Long
col = ActiveCell.Column
    MsgBox Cells(1, Choose(ActiveCell.Column Mod 5 + 1, col - 4, col, col - 1, col - 2, col - 3)).Value
End Sub

 

[bhsoundman]

Thank you everyone for your ideas! Definitely some useful info.

 

[bhsoundman]

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

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