Count Columns in-between ranges

Modfish

New Member
Joined
Apr 22, 2009
Messages
9
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
This might be a bit of overkill:

Code:
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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Each range name refers to a single cell.

Hi Modfish

In this case you can use:

Code:
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

New Member
Joined
Apr 22, 2009
Messages
9
Nice one thanks for prompt replies.

Understand the long one but used the short version

Works a dream

Cheers
Modfish
 

Watch MrExcel Video

Forum statistics

Threads
1,123,517
Messages
5,602,113
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top