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

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

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

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

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

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

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