VBA Code to fill down formulas

[bmontoni]

Hi All,

I have a table in excel where I am trying to write a script that inputs a column name into a cell, enters the formula for that column below, then drags the formula down to the last row of the tableau. This is the script I am using but, once it gets down to row 5, it starts to use the previous row for the lookup value. Do anyone know why this my be hapenning?

Range("N3").Select
ActiveCell.FormulaR1C1 = "Merchandise Category"
Range("N4").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-3],MARA!C[-13]:C[-12],2,0)"
lastRow = Cells(Rows.Count, "M").End(xlUp).Row
Range("N5:N" & lastRow).SpecialCells(xlCellTypeBlanks).Formula = Range("N4").Formula


Thanks in advance!

 

[Joe4]

Welcome to the Board!

You should be able to just use this simplified code (get rid of all those SELECTS!):

Range("N3").FormulaR1C1 = "Merchandise Category"
lastRow = Cells(Rows.Count, "M").End(xlUp).Row
Range("N4:N" & lastRow).FormulaR1C1 = "=VLOOKUP(RC[-3],MARA!C[-13]:C[-12],2,0)"

Note: I updated your thread title, because what you are showing in VBA code, something VERY DIFFERENT than VBScript code (which is a separate entity all its own, distinct from Excel).