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

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

--
Regards
Frank Kabel
Frankfurt, Germany
"JimFor" schrieb im Newsbeitrag
...
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

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" wrote in message
...
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

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

Or maybe:

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

JimFor wrote:
Thanks for the info. I'll work around the limitation.

Chip wrote:
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 Newsfeeds.Com - Unlimited-Uncensored-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 100,000 Newsgroups
---= East/West-Coast Server Farms - Total Privacy via Encryption =---


-----------== 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 =-----