Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help understanding Variables in an IF/VLOOKUP statement
Hello, newbe here...
I need to understand why i can't use this? Inside the [VLOOKUP(Cells(i,16)]??? What can i use there? Any help would be most appreciated. here is the code i have so far... Sub fillformat () Dim FinalRow As Long FinalRow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To FinalRow If Cells(i, 2).Value = "SUP" Then Cells(i, 7).Clear Cells(i, 7) = Left(Cells(i, 4), 5) myvar = Cells(i, 7).Value Cells(i, 16).Value = myvar Cells(i, 7).Clear Cells(i, 7).Formula = "=IF(VLOOKUP(Cells(i,16),ClientList,1,False)" _ & ", ""ERROR"",VLOOKUP(Cells(i,16),ClientList,1,False)) " End If End Sub Thank you so much. Keri |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help understanding Variables in an IF/VLOOKUP statement
Keri,
In you Vlookup, I assume that your "ClientList" is a named range in a worksheet. If this is the case then you need to code it to say Application.Vlookup(Cells(i,16),Range("ClientList" ),1,False) Thanks Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help understanding Variables in an IF/VLOOKUP statement
You are sending a string to the target cells formula, noted by the ""
marks, Therefore VB sees the Cells(i,16) as text and not a range. Try this instead: "=IF(VLOOKUP(" & Cells(i,16).Address & ",ClientList,1,False)" _ & ", ""ERROR"",VLOOKUP(" & Cells(i,16).Address & ",ClientList,1,False))" HTH Die_Another_Day FurRelKT wrote: Hello, newbe here... I need to understand why i can't use this? Inside the [VLOOKUP(Cells(i,16)]??? What can i use there? Any help would be most appreciated. here is the code i have so far... Sub fillformat () Dim FinalRow As Long FinalRow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To FinalRow If Cells(i, 2).Value = "SUP" Then Cells(i, 7).Clear Cells(i, 7) = Left(Cells(i, 4), 5) myvar = Cells(i, 7).Value Cells(i, 16).Value = myvar Cells(i, 7).Clear Cells(i, 7).Formula = "=IF(VLOOKUP(Cells(i,16),ClientList,1,False)" _ & ", ""ERROR"",VLOOKUP(Cells(i,16),ClientList,1,False)) " End If End Sub Thank you so much. Keri |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help understanding Variables in an IF/VLOOKUP statement
Cells(i, 7).Formula = _
"=IF(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" If ClientList is a named range, then that should work. If ClientList is a vba variable, then you should add a line above this to make it a named range as well. ClientList.Name = "ClientList" A good approach is to test you string in the immediate window and see whether it produces a good formula. For illustration: i = 10 ? "=IF(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" =IF(VLOOKUP($P$10,ClientList,1,False),"ERROR",VLOO KUP($P$10,ClientList,1,False)) so we see it does. But not perhaps what you want. I think you want =IF(IsError(VLOOKUP($P$10,ClientList,1,False)),"ER ROR",VLOOKUP($P$10,ClientList,1,False)) so the formula would be: Cells(i, 7).Formula = _ "=IF(ISERROR(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False)),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" -- Regards, Tom Ogilvy "FurRelKT" wrote: Hello, newbe here... I need to understand why i can't use this? Inside the [VLOOKUP(Cells(i,16)]??? What can i use there? Any help would be most appreciated. here is the code i have so far... Sub fillformat () Dim FinalRow As Long FinalRow = Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To FinalRow If Cells(i, 2).Value = "SUP" Then Cells(i, 7).Clear Cells(i, 7) = Left(Cells(i, 4), 5) myvar = Cells(i, 7).Value Cells(i, 16).Value = myvar Cells(i, 7).Clear Cells(i, 7).Formula = "=IF(VLOOKUP(Cells(i,16),ClientList,1,False)" _ & ", ""ERROR"",VLOOKUP(Cells(i,16),ClientList,1,False)) " End If End Sub Thank you so much. Keri |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help understanding Variables in an IF/VLOOKUP statement
Keri, I think I mis-understood your intention with the code: If you use .Value instead of .Formula and your concatenation without using Cells "=IF(Iserror(VLOOKUP(" & Cells(i,16).Address & ",ClientList,1,False))" _ & ", ""ERROR"",VLOOKUP(" & Cells(i,16).Address & ",ClientList,1,False))" Let me know Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help understanding Variables in an IF/VLOOKUP statement
Tom, Wow, that was it, all of you thankyou so very much.
I understand how to add a variable to the watch window. how do you do that to look at a formula? thankyou for your help. Keri Tom Ogilvy wrote: Cells(i, 7).Formula = _ "=IF(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" If ClientList is a named range, then that should work. If ClientList is a vba variable, then you should add a line above this to make it a named range as well. ClientList.Name = "ClientList" A good approach is to test you string in the immediate window and see whether it produces a good formula. For illustration: i = 10 ? "=IF(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" =IF(VLOOKUP($P$10,ClientList,1,False),"ERROR",VLOO KUP($P$10,ClientList,1,False)) so we see it does. But not perhaps what you want. I think you want =IF(IsError(VLOOKUP($P$10,ClientList,1,False)),"ER ROR",VLOOKUP($P$10,ClientList,1,False)) so the formula would be: Cells(i, 7).Formula = _ "=IF(ISERROR(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False)),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" -- Regards, Tom Ogilvy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help understanding Variables in an IF/VLOOKUP statement
Possibly set a variable to the cell that contains the formula, then watch
that variable. I guess it depends on what you want to observe. An alternative is to write informationt to the Immediate window. -- Regards, Tom Ogilvy "FurRelKT" wrote: Tom, Wow, that was it, all of you thankyou so very much. I understand how to add a variable to the watch window. how do you do that to look at a formula? thankyou for your help. Keri Tom Ogilvy wrote: Cells(i, 7).Formula = _ "=IF(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" If ClientList is a named range, then that should work. If ClientList is a vba variable, then you should add a line above this to make it a named range as well. ClientList.Name = "ClientList" A good approach is to test you string in the immediate window and see whether it produces a good formula. For illustration: i = 10 ? "=IF(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" =IF(VLOOKUP($P$10,ClientList,1,False),"ERROR",VLOO KUP($P$10,ClientList,1,False)) so we see it does. But not perhaps what you want. I think you want =IF(IsError(VLOOKUP($P$10,ClientList,1,False)),"ER ROR",VLOOKUP($P$10,ClientList,1,False)) so the formula would be: Cells(i, 7).Formula = _ "=IF(ISERROR(VLOOKUP(" & Cells(i,16).Address & _ ",ClientList,1,False)),""ERROR"",VLOOKUP(" & _ Cells(i,16).Address & ",ClientList,1,False))" -- Regards, Tom Ogilvy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement with two variables | Excel Worksheet Functions | |||
how do i if statement for 3 variables | Excel Worksheet Functions | |||
Understanding macro statement | Excel Programming | |||
Is there any who can help in understanding the VLOOKUP function | Excel Discussion (Misc queries) | |||
Use variables in sql statement | Excel Programming |