Programmatically increase the row # in a formula

[Terry V]

Hello
This is a really silly questions but here it goes.
I have a sheet where in a cell I have this formula:
=IF(AND($C3>0,$A3=""),"",0)
It works fine and does what I want. However, programmatically, I place the
formula in the next cell down from it (just like the copy handle dragging
down 1 row).

This is what the macro recorder gives me:
Selection.AutoFill Destination:=Range("D34"), Type:=xlFillDefault
Range("D34").Select

Which makes sense, but I cannot hard code the Cell addresses (except in the
first on which is already in the cell).

Programmatically, how can I increase the row number by 1?

I tried this ** LOL ** but of course it did not work: ======= what a mess

Sheets("Sheet3").Range("D65536").End(xlUp).Offset(1, 0).Formula =
"=IF(AND($C" & Rows(Sheets("Sheet3").Range("D65536").End(xlUp).Offset(1, 0))
+ 1 & ">0,$A" & Rows(Sheets("Sheet3").Range("D65536").End(xlUp).Offset(1,
0)) + 1 & "=""),"""",0)"

Any suggestions?

Thank you so much
Terry V

 

[Bob Phillips]

Terry,

Try this

Selection.AutoFill Destination:=Selection.Resize(2, 1), Type:=xlFillDefault

The 2 is the number of rows to copy the formula to, 1 is the number of
columns. Adjust to suit

 

[Terry V]

Bob
This gives me an application defined error.

Thank you
Terry

 

[Bob Phillips]

Really, it worked for me.

What cells are you selecting, what formula does it hold, and what Excel
version?

Oh by the way, it might be wrap-arouund. Try

Selection.AutoFill Destination:=Selection.Resize(2, 1), _
Type:=xlFillDefault

 

[Terry V]

What I ended up doing is finding the cell using .Range("D65536").End(xlUp),
then placed that cell into a variable and used the .copy then offset by 1
and used PasteSpecial xlPasteFormulas and it did all I needed to do.

But thank you so much Bob

Terry V