Problem with Code Below

[Guest]

Hi all

Can someone please explain why this isn't working? It is called from a
different Sub marco with What_I_Want being a text string I need to match. It
should return the value of hte column as well

Function Look_Up_Accross(ByVal What_I_Want)

Dim sStr As String, rng As Range

Set rng = Range(Cells(1, 1), Cells(1, 256).End(xlToLeft))
res = Application.Match(What_I_Want, rng, 0)
If Not IsError(res) Then
MsgBox "column: " & res
Else
MsgBox "Not found"
End If

Look_Up_Accross = res

End Function

Thanks, Ben H.

 

[Tom Ogilvy]

Possibly your lookup row contains numbers and you are searching for a string

"123" <> 123

as an example.

 

[Guest]

I think that what you have will work if you...
Replace "application.match"
With "application.worksheetfunction.match"

Also, I would place the statement "On Error Goto Next" after the Dim
statement.

 

[Tom Ogilvy]

That is not required. As written it will work fine.

Using Application.WorksheetFunctionMatch raises a trappable error requiring
the additional error handling you describe (when a match is not made).
Using Application.match does not raise such an error and can be tested as
shown in the code.

 

[Guest]

I forgot to mention this work perfectly last week and its giving me a

Runtime error '1004'
Method 'Cells' of object '_Global' failed

 

[Tom Ogilvy]

possibly you need to qualify the range elements - perhaps you have a
different sheet active this week than last.


Function Look_Up_Accross(ByVal What_I_Want)

Dim sStr As String, rng As Range
With Worksheets("Sheet3")
Set rng = .Range(.Cells(1, 1), .Cells(1, 256).End(xlToLeft))
End with
res = Application.Match(What_I_Want, rng, 0)
If Not IsError(res) Then
MsgBox "column: " & res
Else
MsgBox "Not found"
End If

Look_Up_Accross = res

End Function

Change to reflect the sheet that contains the lookup table.

 

[Guest]

When I debug it, its giving me an error right at the "Set rng=..." the same
one as before. I modified my code to read as such

Function Look_Up_Accross(ByVal What_I_Want)

Dim sStr As String, rng As Range
With Worksheets(1)
Set rng = .Range(Cells(1, 1), Cells(1, 256).End(xlToLeft))
End With
res = Application.Match(What_I_Want, rng, 0)
If Not IsError(res) Then
MsgBox "column: " & res
Else
MsgBox "Not found"
End If

Look_Up_Accross = res

End Function

Thanks

 

[Tom Ogilvy]

I copied it from your email and added the extra periods I suggest in the
previous post and it worked fine for me:

Set rng = .Range(.Cells(1, 1), .Cells(1, 256).End(xlToLeft))
has 3 periods added - you left two out.

Function Look_Up_Accross(ByVal What_I_Want)

Dim sStr As String, rng As Range
With Worksheets(1)
Set rng = .Range(.Cells(1, 1), .Cells(1, 256).End(xlToLeft))
End With
res = Application.Match(What_I_Want, rng, 0)
If Not IsError(res) Then
MsgBox "column: " & res
Else
MsgBox "Not found"
End If

Look_Up_Accross = res

End Function