Programmatically increase the row # in a formula
 

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($C30,$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("D3:D4"), Type:=xlFillDefault
Range("D3:D4").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).Of fset(1, 0))
+ 1 & "0,$A" & Rows(Sheets("Sheet3").Range("D65536").End(xlUp).Of fset(1,
0)) + 1 & "=""),"""",0)"

Any suggestions?

Thank you so much
Terry V

Programmatically increase the row # in a formula
 

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

--

HTH

RP

"Terry V" wrote in message
...
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($C30,$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("D3:D4"), Type:=xlFillDefault
Range("D3:D4").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).Of fset(1,
0))
+ 1 & "0,$A" & Rows(Sheets("Sheet3").Range("D65536").End(xlUp).Of fset(1,
0)) + 1 & "=""),"""",0)"

Any suggestions?

Thank you so much
Terry V

Programmatically increase the row # in a formula
 

Bob
This gives me an application defined error.

Thank you :)
Terry
"Bob Phillips" wrote in message
...
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

--

HTH

RP

"Terry V" wrote in message
...
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($C30,$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("D3:D4"), Type:=xlFillDefault
Range("D3:D4").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).Of fset(1,
0))
+ 1 & "0,$A" &
Rows(Sheets("Sheet3").Range("D65536").End(xlUp).Of fset(1,
0)) + 1 & "=""),"""",0)"

Any suggestions?

Thank you so much
Terry V

Programmatically increase the row # in a formula
 

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
--

HTH

RP

"Terry V" wrote in message
...
Bob
This gives me an application defined error.

Thank you :)
Terry
"Bob Phillips" wrote in message
...
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

--

HTH

RP

"Terry V" wrote in message
...
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($C30,$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("D3:D4"), Type:=xlFillDefault
Range("D3:D4").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).Of fset(1,
0))
+ 1 & "0,$A" &
Rows(Sheets("Sheet3").Range("D65536").End(xlUp).Of fset(1,
0)) + 1 & "=""),"""",0)"

Any suggestions?

Thank you so much
Terry V

Programmatically increase the row # in a formula
 

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
"Bob Phillips" wrote in message
...
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
--

HTH

RP

"Terry V" wrote in message
...
Bob
This gives me an application defined error.

Thank you :)
Terry
"Bob Phillips" wrote in message
...
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

--

HTH

RP

"Terry V" wrote in message
...
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($C30,$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("D3:D4"), Type:=xlFillDefault
Range("D3:D4").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).Of fset(1,
0))
+ 1 & "0,$A" &
Rows(Sheets("Sheet3").Range("D65536").End(xlUp).Of fset(1,
0)) + 1 & "=""),"""",0)"

Any suggestions?

Thank you so much
Terry V