formula in vba

[rafuk73]

Hi all,

Please advise what I am doing wrong.

In column Z I want to add the following formula which looks up values in sheet "Jan 18"

=IF(ISERROR(VLOOKUP(A2,'Jan 18'!A:A,1,FALSE)),"New Employee","OK") and works ok as a formula in the spreadsheet, but as soon as I put it in vba code it gives a syntax error:

With ActiveSheet.Range("Z2:Z" & LastRow)
.Formula = "IF(ISERROR(VLOOKUP(A2,'Jan 18'!A:A,1,FALSE)),"New Employee","OK")"
End With

Thanks!

[kev_]

Try

Please Login or Register  to view this content.

A string in VBA requires to be enclosed in " "
- which causes a problem if you want to put " inside the string
- so the solution is to double them up

Also you omitted the "=" at the beginning

[rafuk73]

Thanks kev_!

[kev_]

Please mark thread as SOLVED (ThreadTools@TopOfThread)

[rafuk73]

Hi,

Could you please what I am doing wrong again. I put an input box to invite a user to enter worksheet name (in this case the user will enter Jan 18 which a name of a sheet in the same workbook)

and then I wanted to use that worksheet name in the below formula, but the macro tried to open some window to search for files and after I closed that window I got formula with Wsheet in the cell, not Jan 18


WSheet = InputBox("Enter Worksheet for lookup")

With ActiveSheet.Range("Z2:Z" & LastRow)
.Formula = "=IF(ISERROR(VLOOKUP(A2, WSheet!A:A,1,FALSE)),""New Employee"",""OK"")"
End With

Result was this
=IF(ISERROR(VLOOKUP(A2, WSheet!A:A,1,FALSE)),"New Employee","OK")

But I want this:
=IF(ISERROR(VLOOKUP(A2, 'Jan 18'!A:A,1,FALSE)),"New Employee","OK")

Please advise

[xlnitwit]

You're putting the variable name into the formula. To get the variable value, you must concatenate

Please Login or Register  to view this content.

[rafuk73]

Brilliant! Thanks!

[kev_]

Please Login or Register  to view this content.

Concatenate the different elements together
- wSheet is a variable holding a string and so it needs to be integrated with & on either side
- then you ned to teminate the preceding string with " and introduce a " to begin the followingh string

[rafuk73]

Thanks Kev!