| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Programmatically add a Formula in a worksheet? | Excel Worksheet Functions | |||
| Formula to get increase by 80% | Excel Worksheet Functions | |||
| Increase by % Formula | New Users to Excel | |||
| Number in cell increase with increase in font size. | Excel Discussion (Misc queries) | |||
| Formula produces wrong result when data cells filled programmatically | Excel Programming | |||
Linear Mode
