I thought this would be a simple answers but cant find it anywhere

I have a macro based on the selected cell which is run in a right click context menu.

I need to get the column number based on where the cell is in a named range.

Example Named Range = C4:H9 selected cell = E6 Value i am trying to get is = 3

E which is 5th column - C which is 3rd column + 1 because column number is not 0 based.

Is there an easy way to do this on a line like Range("namedRange").columnDif(selectedCell)

  • Why not just look for its own column and then subtract the offset of A to the start? – Raystafarian Feb 26 '16 at 17:21

Try something like

Dim namedRangeFirstCol: namedRangeFirstCol = Range("Test").Column
Dim namedRangeLastCol: namedRangeLastCol = namedRangeFirstCol + Range("Test").Columns.Count - 1

If Selection.Column >= namedRangeFirstCol And Selection.Column <= namedRangeLastCol Then
   MsgBox Selection.Column - Range("Test").Column + 1
End If

The IF statement is just to check that the selected cell is actually within the range. You may only need Selection.Column - Range("Test").Column + 1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.