Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Code Below
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Code Below
Possibly your lookup row contains numbers and you are searching for a string
"123" < 123 as an example. -- Regards, Tom Ogilvy "Ben H" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Code Below
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. "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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Code Below
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. -- Regards, Tom Ogilvy "JohnExcel" wrote in message ... 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. "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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Code Below
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Code Below
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" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with Code Below
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 -- Regards, Tom Ogilvy "Ben H" wrote in message ... 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code problem | Excel Discussion (Misc queries) | |||
CODE PROBLEM | Excel Discussion (Misc queries) | |||
XLS to CSV Code Problem | Excel Worksheet Functions | |||
code problem | Excel Programming | |||
Code Problem ! | Excel Programming |