Compile Error: "Sub or Function not defined" - 3-line macro

[roc_on_the_rocks]

I'm trying to use MATCH to identify the Column number that contains "DL_Error", then I'll use this Column # to select a cell (found Column # & "34"), but I get the error shown in the title of this post.

Here's my current code:

Sub Macro15()
' Macro recorded 6/18/2009 by me
Range(Match("DL_ERROR", "A31:CW31", 0) & "34").Select
End Sub

Please help, thanks!

 

[Colin Legg]

Hi,

MATCH() is a worksheet function, not a VBA function, hence the error. If you want to use the MATCH() worksheet function in VBA you can call it via the worksheetfunction object. For example:

Sub foo()
    Dim lCol As Long
    
    lCol = Application.WorksheetFunction.Match("DL_ERROR", Sheet1.Range("A31:CW31"), 0)
    
End Sub

Hope that helps...

 

[Jonmo1]

Your problem is 2fold..

1. The RANGE object cannot use a column NUMBER, it must have a column LETTER.
The Cells object can use Either column Number or Letter
Cells(Row#, Column# or letter)

2. to use match, you have to write it like this
Application.Match("DL_ERROR", RANGE("A1:CW31"),0)

Try this

CELLS(34, Application.Match("DL_ERROR", RANGE("A31:CW31"), 0)).Select

 

[roc_on_the_rocks]

Thank you so much Colin_L and jonmo1 for the speedy response. Both solutions worked like a charm!

I sure appreciate the education. I didn't HAVE to use MATCH, but since my VBA skills are limited, it was just trying to make it work.

 

[roc_on_the_rocks]

In the same line of thought, what's this simplest way to select all CELLS between - including - Row 34 and the last populated Row, still in the Column that contains the value "XPTO" (made up).

I'll use this selection to apply a Condition Format, among other things.

Thanks in advance,

 

[roc_on_the_rocks]

Any idea folks? Please help, I'm stuck.

 

[Jonmo1]

MyCol = Application.Match("DL_ERROR", RANGE("A31:CW31"), 0)
LR = Cells(Rows.Count, MyCol).End(xlup).Row
Range(Cells(34,MyCol),Cells(LR,MyCol)).Select

 

[roc_on_the_rocks]

Thank you so much jonmo1, you just saved my life again!

 

[roc_on_the_rocks]

Two quick questions please: How to:

1) Instead of hard-coding the Column CW in the Application.Match, make that RANGE to go from A31 - including - until all the way to the last populated Column?

Application.Match("DL_ERROR", RANGE("A1:CW31"),0)

2) How to I put a formula to average these cells:
MyCol = Application.Match("DL_ERROR", RANGE("A31:CW31"), 0)
LR = Cells(Rows.Count, MyCol).End(xlup).Row
Range(Cells(34,MyCol),Cells(LR,MyCol)).Select

As always, big thanks in advance,

 

[roc_on_the_rocks]

Can someone please help me?

Two quick questions please: How to:

1) Instead of hard-coding the Column CW in the Application.Match, make that RANGE to go from A31 - including - until all the way to the last populated Column?

Application.Match("DL_ERROR", RANGE("A1:CW31"),0)

2) How to I put a formula to average these cells:
MyCol = Application.Match("DL_ERROR", RANGE("A31:CW31"), 0)
LR = Cells(Rows.Count, MyCol).End(xlup).Row
Range(Cells(34,MyCol),Cells(LR,MyCol)).Select

As always, big thanks in advance,