Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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)? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding the largest value for a name in a column and then returningthe result from a different column | Excel Worksheet Functions | |||
Finding the closest number in column A and take the value in column B | Excel Worksheet Functions | |||
Finding all matches in column B with datalist in column A | Excel Programming | |||
Output from a userform finding the right column. column | Excel Programming | |||
finding and reporting in column A, where a series of column reaches zero | Excel Programming |