Finding the right column

[JaMark]

I have a row, that looks like this:

| Fender bb | Fender ee | Fender ff | Line 1 |

Now, the number of fender columns could vary, so my question is, how do
I get the column number/letter of the column that contains "Line" as
the first four letters (because it is not always line 1)?

 

[Norman Jones]

Hi JaMark,

Try something like:

'=============>>
Public Sub Tester()
Dim rng As Range
Dim col As Long

With ActiveSheet
On Error Resume Next
Set rng = .Cells.Find(What:="Line*", _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
On Error GoTo 0

If Not rng Is Nothing Then MsgBox rng.Column

End Sub
'<<=============

 

[JaMark]

Thanks Norman, that's just what I need )

Hi JaMark,

Try something like:

'=============>>
Public Sub Tester()
Dim rng As Range
Dim col As Long

With ActiveSheet
On Error Resume Next
Set rng = .Cells.Find(What:="Line*", _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
On Error GoTo 0

If Not rng Is Nothing Then MsgBox rng.Column

End Sub
'<<=============

 

[JaMark]

If need to refer to that column, do I write like this?

..Range(ColLet(Tester())&"3") 'I have a function (ColTest()) that
converts the number into a letter

 

[Norman Jones]

Hi JaMark,

There is no need to convert the column number to a letter.

Try something like:

Cells(3, ColNumber)

 

[JaMark]

Ok, thanks. That was really helpful, but still, how do I refer to the
function? Like this?

..Range(Tester()&"3")

 

[Norman Jones]

Hi JaMark,

Ok, thanks. That was really helpful, but still, how do I refer to the
function? Like this?

.Range(Tester()&"3")

If I understand you correctly, try something like:

Sub Macro1()
Dim rng As Range
Dim col As Long

With ActiveSheet
On Error Resume Next
col = .Cells.Find(What:="Line*", _
After:=.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, _
MatchCase:=False).Column
End With
On Error GoTo 0

If col >= 1 Then
Set rng = Cells(3, col)
End If

MsgBox rng.Address(0, 0)
End Sub