Count Columns in-between ranges

[Modfish]

Help.. I know its simple but annoying me

I'm trying to count the number of columns betwen to rangenames,. Each range name refers to a single cell.

e.g.

Sub test()

Application.Goto Reference:="Start_Outline"
Application.Goto Reference:="Start_TaskName"
int_col = 3

End Sub


i.e trying to make the variable int_col refer to the number of columns between Start_Outline Start_TaskName rather than a constant.

Many thanks
Modfish

 

[Ron Coderre]

This might be a bit of overkill:

Function ColDiff(rStartRng As Range, rEndRng As Range) As Integer
Dim iRng1Col As Integer
Dim iRng2Col As Integer
 
On Error GoTo CrashTrap
 
iRng2Col = rEndRng.Areas(1).Column
iRng1Col = rStartRng.Areas(1).Column
 
If iRng2Col = iRng1Col Then
   ColDiff = 0
Else
   If iRng2Col > iRng1Col Then
      ColDiff = iRng2Col - iRng1Col - 1
   Else
      ColDiff = -(iRng1Col - iRng2Col - 1)
   End If
End If
Exit Function
CrashTrap:
ColDiff = xlErrRef
End Function

...but, is that something you can work with?

 

[pgc01]

Each range name refers to a single cell.

Hi Modfish

In this case you can use:

int_col = Abs(Range("Start_Outline").Column - Range("Start_TaskName").Column)

Notice that a value equal to zero means they are in the same column and a value 1 means they are in contiguous columns.

 

[Modfish]

Nice one thanks for prompt replies.

Understand the long one but used the short version

Works a dream

Cheers
Modfish