shockalock
New Member
- Joined
- Aug 17, 2010
- Messages
- 1
Hi everyone,
I have a VERY simplistic macro which uses
Range("D21").FormulaLocal = "=D13-'" & Range("D6").Value & "'!D13"
Range("E21").FormulaLocal = "=E13-'" & Range("D6").Value & "'!E13"
and so on, to fill an entire row with a formula.
D6 is an absolute reference, it's a cell which contains information which identifies a "previous" sheet and is also it's name; the result of the above is
"=D13-'somesheetname'!D13" in cell D21
"=E13-'somesheetname'!E13" in cell E21
and so on.
I was under time pressure, so I just put a row like above into my macro for every cell in the sheet that needed the treatment.
That way, my user merely needs to make a fresh copy of the sheet to work on, name it correctly, and enter the name of the previous sheet. Then they can just press a button I added to execute my macro and it will rebuild all formulas that need to be rebuilt. Unless they screw up filling D6 of course
What I've been wondering about now, is if there's any more elegant way to fill an entire row. I know I could use Range("D21:E21"), but beyond that, my scant understanding of vba fails utterly. Should this be a "while" loop? Puzzled newbie is puzzled. Halp! >_< I get the impression I should go back to basics and find a vba course...
I have a VERY simplistic macro which uses
Range("D21").FormulaLocal = "=D13-'" & Range("D6").Value & "'!D13"
Range("E21").FormulaLocal = "=E13-'" & Range("D6").Value & "'!E13"
and so on, to fill an entire row with a formula.
D6 is an absolute reference, it's a cell which contains information which identifies a "previous" sheet and is also it's name; the result of the above is
"=D13-'somesheetname'!D13" in cell D21
"=E13-'somesheetname'!E13" in cell E21
and so on.
I was under time pressure, so I just put a row like above into my macro for every cell in the sheet that needed the treatment.
That way, my user merely needs to make a fresh copy of the sheet to work on, name it correctly, and enter the name of the previous sheet. Then they can just press a button I added to execute my macro and it will rebuild all formulas that need to be rebuilt. Unless they screw up filling D6 of course
What I've been wondering about now, is if there's any more elegant way to fill an entire row. I know I could use Range("D21:E21"), but beyond that, my scant understanding of vba fails utterly. Should this be a "while" loop? Puzzled newbie is puzzled. Halp! >_< I get the impression I should go back to basics and find a vba course...
Last edited:
