Problem with Code Below
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.
--
Regards,
Tom Ogilvy
"Ben H" wrote in message
...
I forgot to mention this work perfectly last week and its giving me a
Runtime error '1004'
Method 'Cells' of object '_Global' failed
"Ben H" wrote:
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.
|