How to find a column

[Jon Turner]

In VBA, how would I find a specific column ? I want the function
to return the column number of a column named "Sample"

Many Thanks

 

[Ron de Bruin]

Hi Jon

If it is a named range

MsgBox Range("Sample").Cells(1).Column


If you have a header with that name
Try this for the first row in Sheets("sheet1")

Sub test()
Dim FindString As String
Dim Rng As Range
FindString = "Sample"
Set Rng = Sheets("sheet1").Range("1:1").Find(What:=FindString, _
After:=Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then MsgBox Rng.Column
End Sub

 

[Tom Ogilvy]

Ron addressed one possibility - here is another:
If you mean sample is text contained in the first row

Dim rng as Range
set rng = rows(1).Find("Sample")
if not rng is nothing then
rng.Entirecolumn.Select
else
msgbox "Sample not found"
End if