VLookUp Syntax Error ?
 

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

VLookUp Syntax Error ?
 

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 the-
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))"

--
Regards,

OssieMac


"u473" wrote:

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

VLookUp Syntax Error ?
 

Thank you, I do appreciate the education,
Wayne

VLookUp Syntax Error ?
 

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