VLookUp Syntax Error ?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

u473 - 23 Nov 2007 00:25 GMT

I pasted in a new module the following code from Patrick Molloy
in this group and the line
myformula ="=IF(ISERROR...... was immediately highlighted in Error.
I did not bother in this first try to Dim my variables.
I did not think that would generate an error.
Which raises this question : Are there any potential triggered errors
before code run ? For instance, a Keyword not existing in my
Visual Basic Version ? What did I miss ?

Sub LookupFill()
FirstRow = 20
LastRow = 70
WhichCol = "K"

myformula ="=IF(ISERROR(VLOOKUP(C[1],Sheet1!RC"[-1]: _
R[155]C,2,0),"",VLOOKUP(C[1], _
Sheet1!RC[-1]:R[155]C,2,0))"

With Range(Cells(Firstrow,WhichCol), _
Cells(Lastrow,WhichCol))

.Formular1C1 = myformula

End With
End Sub

Thank you for your help
Wayne

Reply to this Message

OssieMac - 23 Nov 2007 02:18 GMT

Hi,

You cannot have space and underscore line breaks in the middle of code
enclosed in double quotes. Try deleting the line breaks and make it one line.

There is a double quote after RC in the following code which I do not think
should be there:-
myformula ="=IF(ISERROR(VLOOKUP(C[1],Sheet1!RC"[-1]:

There should also be additional double quotes around the double quotes
representing the null.

I have not tested the code so I don't know if anything else is wrong.

If you want to insert line breaks in a long line of code which is enclosed
in double quotes then it is necessary to close the double quotes, insert an
ambersand then the space and underscore and start the next line with double
quotes again like the following example.

myformula = "=IF(ISERROR(VLOOKUP(C[1],Sheet1!RC[-1]:" & _
"R[155]C,2,0),"""",VLOOKUP(C[1]," & _
"Sheet1!RC[-1]:R[155]C,2,0))"

Signature

Regards,

OssieMac

> I pasted in a new module the following code from Patrick Molloy
> in this group and the line
[quoted text clipped - 24 lines]
> Thank you for your help
> Wayne

Reply to this Message

u473 - 23 Nov 2007 18:17 GMT

Thank you, I do appreciate the education,
Wayne

Reply to this Message

u473 - 24 Nov 2007 01:55 GMT

This code on VLookUp with R1C1 which I picked up in this group was of
interest to me.
However I was trying in reverse, to populate a worksheet with 3 rows
of test data and then
execute this code but I got bogged down.
Can you help me populate a test Worksheet before I execute the code ?
Thank you for your help,
Regards,
Wayne

Reply to this Message