Excel Macro question? Column position

[Guest]

Say I have a column name "TEST". I would like to find the position of this
column and then assign this number to a variable.

If "TEST" is the fifth column in the spreadsheet...then the resulting value
I'm looking for is var = 5

Anyone?

Thanks

 

[JE McGimpsey]

one way:

Dim var As Long
var = Range("TEST").Column

 

[Guest]

Thanks for the response...

I'm getting the following error...
Method 'Range' of object '_Global' failed

Any idea why?

 

[Tom Ogilvy]

do you have an Insert=>Name=>Define
Name:= Test
Refersto:=Sheet1!$E:$E

or something like that. If not, that is why you get the error.

If by named test, you mean E1 has the word Test in it.

Sub AAA()
Dim res As Variant, vVar As Variant

res = Application.Match("Test", Rows(1), 0)
If Not IsError(res) Then
vVar = res
Debug.Print vVar
Else
MsgBox "Not found"
End If

End Sub