VLOOKUP Compile/Syntax Error

[JimFor]

Hi,

I am working on a program which involves using the VLOOKUP function. I have
some account numbers on sheet2 and want to put inventory amounts associated
with those account numbers onto sheet 3. The inventory amounts are on sheet 1.
Right now, if there are no account numbers on sheet 1, "N/A" appears in a
cell in sheet 3. I need a blank to appear in sheet 3. When I use the
following program, I get a "Compile Error:Syntax Error':

Sub ZOO()

Sheet3.Cells(5, 8) = IF(ISNA(Application.VLookup(Sheet2.Cells(2,1 )), _
Worksheets("Sheet1").Range("A2:B4"), 2, 0)) _
,"", Application.VLookup(Sheet2.Cells(2, 1)), _
Worksheets("Sheet1").Range("A2:B4"), 2, 0))
End Sub

If I try putting an "ELSE" in the statement, I get "Expected Expression"
error and the "IF" is highlighted. Can anyone tell me how to correct this code
so I get a blank in a cell on sheet3 if there is an account number in sheet2
but account number does not appear in sheet1?

Thanks

 

[Frank Kabel]

Hi
you can't do it this way. Do you want to insert the formula or only the
value into this cell. You have used a combination of both:
IF, ISNA are not directly supported in VBA
If you want to insert the formula itself, don't use application.VLOOKUP

 

[Chip Pearson]

You can't use Excel functions like IF and ISNA directly in VBA.
Try writing your code like

Sheet3.Cells(5, 8) =
IIf(IsError(Application.VLookup(Sheet2.Cells(2, 1), _
Worksheets("Sheet1").Range("A2:B4"), 2, 0)), "", _
Application.VLookup(Worksheets("Sheet1").Range("A2:B4"), 2,
0))


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[JimFor]

Thanks for the info. I'll work around the limitation.

 

[Dave D-C]

Or maybe:

Sheet3.Cells(5, 8) = _
"=IF(ISNA(VLookup(Sheet2!a2,Sheet1!A2:B4, 2, 0))," & _
"""""," & _
"VLookup(Sheet2!a2, Sheet1!A2:B4, 2, 0))"

Thanks for the info. I'll work around the limitation.

Sheet3.Cells(5, 8) =

IIf(IsError(Application.VLookup(Sheet2.Cells(2, 1), _
Worksheets("Sheet1").Range("A2:B4"), 2, 0)), "", _
Application.VLookup(Worksheets("Sheet1").Range("A2:B4"), 2,
0))





-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
http://www.newsfeed.com The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----