| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Vlookup help, please
Hi Mike
What I'm trying to do with my formula is; if the value of "ShopName" matches one of the values in the list on the worksheet IllinoisShopNames, cells A2:A50, then proceed. I'm having trouble with the comparison. I tried your suggestion, and then compared: If rng = ShopName Then... but I do not get a value for rng. I'm getting a run time error #13, type mismatch. Any more thoughts? Fred "Mike H" wrote: Fred, I don't understand your formula. If I've understood you lookup the value in E(n) in A2 - A50 so the only thing you can return is what was in E(n) in the first place. However, here's the correct syntax Set rng = Range("IllinoisShopNames!A2:A50") Results = Application.VLookup(Range("E" & errw).Value, rng, 1, False) Mike "Fred" wrote: Hi I'm having problems with this function. I need to compare "ShopName" to an alphabetically sorted list on another worksheet. I'm getting a "Complie error: sub or function not defined" and the "VLookup" is highlighted. The variables have been properly declared. If ShopName = Range(VLookup("E" & ERRW & ",IllinoisShopNames!A2:A50,1")).Value Then blah blah blah End If What am I missing? Thanks in advance Fred |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup help, please
Fred,
I don't know from you snippet of code where you are geting the variable 'shopname' from but this may get you going. Note that I have manually set errw=1 but i suppose you do this in a loop. Sub sonic() errw = 1 Dim rng As Range Set rng = Range("IllinoisShopNames!A2:A50") If Application.VLookup(Range("E" & errw).Value, rng, 1, False) = shopname Then 'do something else 'do something else End If End Sub Mike "Fred" wrote: Hi Mike What I'm trying to do with my formula is; if the value of "ShopName" matches one of the values in the list on the worksheet IllinoisShopNames, cells A2:A50, then proceed. I'm having trouble with the comparison. I tried your suggestion, and then compared: If rng = ShopName Then... but I do not get a value for rng. I'm getting a run time error #13, type mismatch. Any more thoughts? Fred "Mike H" wrote: Fred, I don't understand your formula. If I've understood you lookup the value in E(n) in A2 - A50 so the only thing you can return is what was in E(n) in the first place. However, here's the correct syntax Set rng = Range("IllinoisShopNames!A2:A50") Results = Application.VLookup(Range("E" & errw).Value, rng, 1, False) Mike "Fred" wrote: Hi I'm having problems with this function. I need to compare "ShopName" to an alphabetically sorted list on another worksheet. I'm getting a "Complie error: sub or function not defined" and the "VLookup" is highlighted. The variables have been properly declared. If ShopName = Range(VLookup("E" & ERRW & ",IllinoisShopNames!A2:A50,1")).Value Then blah blah blah End If What am I missing? Thanks in advance Fred |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup help, please
Thanks Mike
I forgot to declare Dim rng as Range. Everything else is "working", just need to troubleshoot it. Have a great day. Fred "Mike H" wrote: Fred, I don't know from you snippet of code where you are geting the variable 'shopname' from but this may get you going. Note that I have manually set errw=1 but i suppose you do this in a loop. Sub sonic() errw = 1 Dim rng As Range Set rng = Range("IllinoisShopNames!A2:A50") If Application.VLookup(Range("E" & errw).Value, rng, 1, False) = shopname Then 'do something else 'do something else End If End Sub Mike "Fred" wrote: Hi Mike What I'm trying to do with my formula is; if the value of "ShopName" matches one of the values in the list on the worksheet IllinoisShopNames, cells A2:A50, then proceed. I'm having trouble with the comparison. I tried your suggestion, and then compared: If rng = ShopName Then... but I do not get a value for rng. I'm getting a run time error #13, type mismatch. Any more thoughts? Fred "Mike H" wrote: Fred, I don't understand your formula. If I've understood you lookup the value in E(n) in A2 - A50 so the only thing you can return is what was in E(n) in the first place. However, here's the correct syntax Set rng = Range("IllinoisShopNames!A2:A50") Results = Application.VLookup(Range("E" & errw).Value, rng, 1, False) Mike "Fred" wrote: Hi I'm having problems with this function. I need to compare "ShopName" to an alphabetically sorted list on another worksheet. I'm getting a "Complie error: sub or function not defined" and the "VLookup" is highlighted. The variables have been properly declared. If ShopName = Range(VLookup("E" & ERRW & ",IllinoisShopNames!A2:A50,1")).Value Then blah blah blah End If What am I missing? Thanks in advance Fred |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Vlookup help, please
Hi
Problems again. I declared: Dim ShopName as String If I use this line as you suggested: "If Application.VLookup(Range("E" & errw).Value, rng, 1, False) = shopname Then" then I get "error 13 type mismatch" If I use this other line and If statement: "Results = Application.VLookup(Range("E" & errw).Value, rng, 1, False)" "If ShopName = Results then" Then the results don't advance past the first item in the list, which is "Aurora" What am I missing now? Thanks in advance Fred "Mike H" wrote: Fred, I don't know from you snippet of code where you are geting the variable 'shopname' from but this may get you going. Note that I have manually set errw=1 but i suppose you do this in a loop. Sub sonic() errw = 1 Dim rng As Range Set rng = Range("IllinoisShopNames!A2:A50") If Application.VLookup(Range("E" & errw).Value, rng, 1, False) = shopname Then 'do something else 'do something else End If End Sub Mike "Fred" wrote: Hi Mike What I'm trying to do with my formula is; if the value of "ShopName" matches one of the values in the list on the worksheet IllinoisShopNames, cells A2:A50, then proceed. I'm having trouble with the comparison. I tried your suggestion, and then compared: If rng = ShopName Then... but I do not get a value for rng. I'm getting a run time error #13, type mismatch. Any more thoughts? Fred "Mike H" wrote: Fred, I don't understand your formula. If I've understood you lookup the value in E(n) in A2 - A50 so the only thing you can return is what was in E(n) in the first place. However, here's the correct syntax Set rng = Range("IllinoisShopNames!A2:A50") Results = Application.VLookup(Range("E" & errw).Value, rng, 1, False) Mike "Fred" wrote: Hi I'm having problems with this function. I need to compare "ShopName" to an alphabetically sorted list on another worksheet. I'm getting a "Complie error: sub or function not defined" and the "VLookup" is highlighted. The variables have been properly declared. If ShopName = Range(VLookup("E" & ERRW & ",IllinoisShopNames!A2:A50,1")).Value Then blah blah blah End If What am I missing? Thanks in advance Fred |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If (Vlookup 0) working, but what if Vlookup cell does not exist | Excel Worksheet Functions | |||
| using a vlookup to enter text into rows beneath the vlookup cell | Excel Programming | |||
| Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
| Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
| Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
Linear Mode
