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