Finding the right column
 

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

Finding the right column
 

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


--
---
Regards,
Norman



"JaMark" wrote in message
oups.com...
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)?

Finding the right column
 

Thanks Norman, that's just what I need :o)


Norman Jones wrote:
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
'<<=============


--
---
Regards,
Norman



"JaMark" wrote in message
oups.com...
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)?

Finding the right column
 

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 wrote:
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
'<<=============


--
---
Regards,
Norman



"JaMark" wrote in message
oups.com...
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)?

Finding the right column
 

Hi JaMark,

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

Try something like:

Cells(3, ColNumber)

---
Regards,
Norman



"JaMark" wrote in message
oups.com...
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 wrote:
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
'<<=============


--
---
Regards,
Norman



"JaMark" wrote in message
oups.com...
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)?

Finding the right column
 

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

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

Norman Jones wrote:
Hi JaMark,

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

Try something like:

Cells(3, ColNumber)

---
Regards,
Norman



"JaMark" wrote in message
oups.com...
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 wrote:
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
'<<=============


--
---
Regards,
Norman



"JaMark" wrote in message
oups.com...
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)?

Finding the right column
 

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




--
---
Regards,
Norman



"JaMark" wrote in message
oups.com...
Ok, thanks. That was really helpful, but still, how do I refer to the
function? Like this?

.Range(Tester()&"3")