Fill down VLOOKUP

[JUAN]

Hello,
I want to see if its possible to fill down my VLOOKUP
formula, which I created in my macro. This is part of the
macro:
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[1],Sheet1!RC[-1]:R
[155]C,2,0)"

My file will vary in rows so if I do in my macro a range
example, B2:B40 as an autofill, it will put #NA error
because there is no data. So is there a way to copy my
vlookup and not get an error. As I said my file might be
over 50 rows or less than that.

Please advise any help.
thanks,
Juan

 

[Guest]

Change your formula to "=IF(ISERROR(VLOOKUP(C[1],Sheet1!RC
[-1]:R[155]C,2,0),"",VLOOKUP(C[1],Sheet1!RC[-1]:R[155]
C,2,0))" and you won't get any error messages when you
autofill down.

 

[JUAN]

Hello,
Here's what I have:
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C
[1],Sheet1!RC"[-1]:R[155]C,2,0),"",VLOOKUP(C[1],Sheet1!RC[-
1]:R[155]C,2,0))"
End If
But get an error saying Compile error Expected end of
statement.

Please advise any info.
thanks,
juan

-----Original Message-----
Change your formula to "=IF(ISERROR(VLOOKUP(C[1],Sheet1!RC
[-1]:R[155]C,2,0),"",VLOOKUP(C[1],Sheet1!RC[-1]:R[155]
C,2,0))" and you won't get any error messages when you
autofill down.

-----Original Message-----
Hello,
I want to see if its possible to fill down my VLOOKUP
formula, which I created in my macro. This is part of the
macro:
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[1],Sheet1!RC[-1]:R
[155]C,2,0)"

My file will vary in rows so if I do in my macro a range
example, B2:B40 as an autofill, it will put #NA error
because there is no data. So is there a way to copy my
vlookup and not get an error. As I said my file might be
over 50 rows or less than that.

Please advise any help.
thanks,
Juan


.

.

 

[JUAN]

Hello,
I figured it out what was wrong. So thank you for the help.

JUAN

-----Original Message-----
Hello,
Here's what I have:
Range("B2").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(C
[1],Sheet1!RC"[-1]:R[155]C,2,0),"",VLOOKUP(C[1],Sheet1!RC [-
1]:R[155]C,2,0))"
End If
But get an error saying Compile error Expected end of
statement.

Please advise any info.
thanks,
juan

-----Original Message-----
Change your formula to "=IF(ISERROR(VLOOKUP(C[1],Sheet1! RC
[-1]:R[155]C,2,0),"",VLOOKUP(C[1],Sheet1!RC[-1]:R[155]
C,2,0))" and you won't get any error messages when you
autofill down.

-----Original Message-----
Hello,
I want to see if its possible to fill down my VLOOKUP
formula, which I created in my macro. This is part of the
macro:
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(C[1],Sheet1!RC[-1]:R
[155]C,2,0)"

My file will vary in rows so if I do in my macro a range
example, B2:B40 as an autofill, it will put #NA error
because there is no data. So is there a way to copy my
vlookup and not get an error. As I said my file might be
over 50 rows or less than that.

Please advise any help.
thanks,
Juan


.

.

.

 

[Patrick Molloy]

You can enter the formula without using the FillDown
method...

example

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


Patrick Molloy
Microsoft Excel MVP