Copy relative formulas without using the Copy command?

**OldManExcellor** · 07-16-2014, 10:39 AM

When I execute this command I get the exact same formulas in both columns:
wks.Columns(i + 1).Formula = wks.Columns("E").Formula

How can I tweak this to copy relative formulas? I don't want to use the copy command.

**Vikas_Gautam** · 07-16-2014, 10:42 AM

I think you have to use...

Copy command ... in vba..

No other way....

Don't forget to click *

**OldManExcellor** · 07-16-2014, 10:46 AM

There are always other ways

One option is to write each formula in VBA:

Range(i, 10).Formula = "=something" & something_dynamic & ")"
Range(i, 11).Formula = ...
...
...
...

**MrShorty** · 07-16-2014, 11:20 AM

This isn't the kind of VBA programming I do, but I think this is where using formulaR1C1 (using R1C1 formulas) can be very useful. The thing about R1C1 formulas is that, when they are copied, the actual "text string" for the formula doesn't change. So, in VBA, we can build the formula string once (or read it from another cell), and put the same formula text string into another cell/range without modifying the formula string.

As noted, I'm not real good with this kind of programming, but it might be worth the time and effort to research and test this kind of approach.