Returning column number within a range

[OssieMac]

With the following Find is there any way of returning the column number
within the assigned range instead of the column number of the worksheet.

Sub RangeColumns1()

Dim rng As Range
Dim cFound As Range
Dim strColHead As String
Dim lngColNumb As Long

strColHead = "MyHeader"

With Sheets("Sheet1")
Set rng = .Range("D1:M1")
End With

With rng
Set cFound = .Find(What:=strColHead, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

'******************************
'Returns the sheet column number _
not the column number within rng.
If Not cFound Is Nothing Then
lngColNumb = cFound.Column
MsgBox lngColNumb
End If
'******************************
End With

End Sub

I can use the following workaround but would like to know the answer to the
above if there is one.

Sub RangeColumns2()
Dim rng As Range
Dim i As Long
Dim strColHead As String
Dim lngColNumb As Long

strColHead = "MyHeader"

With Sheets("Sheet1")
Set rng = .Range("D1:M1")
End With

With rng
For i = 1 To .Columns.Count
If .Cells(1, i) = strColHead Then
Exit For
End If
Next i
End With

lngColNumb = i

MsgBox lngColNumb

End Sub

 

[paul.robinson]

Hi
not tested but would
lngColNumb = cFound.Column - .column + 1

do the job?
Paul

 

[Peter T]

FWIW your RangeColumns2 function looks for an exact and case sensitive match
whereas your RangeColumns1 looks for a part match and one which is not case
sensitive.

Paul's suggestion looks fine.

Regards,
Peter T

Paul's suggestion looks fine.

 

[OssieMac]

Thanks Paul. Also thanks Peter for your input because it could make a
difference but in this case both examples were just simplified code for the
purpose of the column number in the range and I included the second example
so I did not get it back as the answer and the text comparison was irrelevant
for the exercise.