using (Range) with .FormulaLocal

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...
 
Last edited:

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Watch MrExcel Video

Forum statistics

Threads
1,122,484
Messages
5,596,407
Members
414,064
Latest member
Duncthegreat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top