Returning column number within a range

O

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
 
P

paul.robinson

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

do the job?
Paul
 
P

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.
 
O

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top