Debugging help insert formula into cell

[desert_dweller5]

Sub Rangeformula()
range("E3").Formula = "=IF(ISERROR(VLOOKUP(D3,$B$3:$C$19037,2,FALSE)),,VLOOKUP(D3,$B$3:$C$19037,2,FALSE)"
Range(e3:e20000).filldown
End Sub

This is my code. it wont run.

Run-time Error '1004': Application-defined or object-defined error.

*Frustrated*I'm simply trying to put the formula into the cell and fill down until there are no more entries in the column to the left.

it has to be the formula because when i put a value it works. for example:
Range("e3".Formula= 0

not sure why it wont take. maybe if i put the formula as a variable?
I dunno. any suggestions would be helpful.
thanks
Desert_dweller5

 

[igold]

Hi,

You need to a second close bracket at the end of your formula...

"=IF(ISERROR(VLOOKUP(D10,$B$3:$C$19037,2,FALSE)),,VLOOKUP(D10,$B$3:$C$19037,2,FALSE))"


igold

 

[dchaney]

As the 2nd poster pointed out the second ) was missing. Also unless you ALWAYS have 20000 rows to place the code on and you ALWAYS have 19037 rows of data in column C you could use the following.

Sub Rangeformula()

Dim lastRow As Long

lastRow = Range("D" & Rows.Count).End(xlUp).Row
   
Range("E3:E" & lastRow).Formula = "=IF(ISERROR(VLOOKUP(D3,B:C,2,FALSE)),,VLOOKUP(D3,B:C,2,FALSE))"

End Sub

This code will only place the formula in the areas it is needed, if you have 10 lines it places it in 10 lines, if you have 30000 lines it places it in 30000 lines and so on...

 

[desert_dweller5]

Thanks for the prompt replies.
i have one more challenge. I need to have the cells that don't have a match or #N/A return as blank. when I use the double quotation marks the compiler is confused. is there a way to include these "" in the formula?

 

[desert_dweller5]

I answered my own question by looking on the forum.
range("E3:E" & lastRow).Formula = "=IF(ISERROR(VLOOKUP(D3,B:C,2,FALSE)),"""",VLOOKUP(D3,B:C,2,FALSE))"

I had to put the double quotation marks in side of a second set of double quotation marks to let the compiler know that it was a character to pass along instead of code to compile. I'm not 100% sure this is correct but it seems to work. So I'm happy.

Thanks for your help guys!

see here for more details:
http://www.mrexcel.com/forum/excel-...within-formula-visual-basic-applications.html

 

[igold]

Great. I am glad you got it working. Thanks for the feedback!

igold