count the number of columns before the active column

[jgarland]

Hi all.

I'm trying to find the correct excel VBA code to count the number of columns in a named range, from the start of the range up to and including the current active cell location and return that value to a specific cell.

Example:
"mynamedrange" is columns AA thru AZ and the current active cell is in column AF. The returned number should be 6.

The returned number will be variable as there will be future columns inserted, and the active cell location will vary. I've scoured the web and have not found anything usable.

Ideas & suggestions?

 

[steve the fish]

Something like this?

Set rng = Intersect(Range("mynamedrange"), ActiveCell)
If Not rng Is Nothing Then
    myVal = ActiveCell.Column - Range("mynamedrange").Column + 1
    MsgBox myVal
End If

 

[jgarland]

Works perfect! Thanks stf.

Can the code be altered to return the value no matter what row the active cell is in?

For instance:
"mynamedrange" is row 5 in columns AA thru AZ, and the current active cell is in AF10, or AF11, ect.