filldown using macros

Q

qiong

hi

I would like to create a macro that selects a range according to a
calculated result, and then filldowns a formula.

My calculated result is displayed in a cell whose forumla
={Max(IF(ISNUMBER(B3:B1055),ROW(B3:B1055)))}

Say this displays 87.

I would like the macro to select E3:BM3 to filldown (87-3=84) rows.
(Cells E3:BM3 contains Vlookup formulas.)
Is this possible?

Thanks in advance
Keng
 
R

Ron de Bruin

Try this with in cell A1 the formula
={Max(IF(ISNUMBER(B3:B1055),ROW(B3:B1055)))}
Click to expand...


Sub test2()
Dim Lrow As Long
Lrow = Range("A1").Value
Range("E3:BM" & Lrow).FillDown
End Sub
 
Q

qiong

Thks!

A follow up question:

Does the & Lrow work if i want to use it in a formula code?

I have a formula:
ActiveCell.FormulaR1C1 = "=RANK(RC2, R3C2:R86C2)"

I tried inserting the & Lrow like this
ActiveCell.FormulaR1C1 = "=RANK(RC2, R3C2:R & Lrow & C2)". It doesnt
seem to work.
 
Top