0

I have seen this a couple of times and usually can work around it, but I have some international users that require me to figure out something different.

I have a row that contains several formulas unique to each column. I wish to duplicate across a range of data. Essentially this is the same as doing a copy paste formula, but I'm trying to speed up the process and then immediately set the formulas to values. However, when I execute the code that sets the range using FormulaR1C1, it only works on the first row.

Here's an extremely simplified illustration of the issue:

Range("C1:H1") has a formula of =$B1/8+ a different number for each column. I want to duplicate this formula across Range("C4:H12")

Using this vba code:

Range("C4:H12").FormulaR1C1 = Range("C1:H1").FormulaR1C1

works for the first row (4), but on row five is starts referencing the NEXT row (thus the formula reads =$B6/8+ and continues offsetting.

My usual workaround would be to type in the formula save it somewhere specific, but my issue with my foreign users is that the complex formula syntax I'm using needs to be set within a cell formula. Yes, I could use copy paste formula, but I'm mostly just curious why this offset is occurring.

You can download the simple file here (without the VBA).

enter image description here

AAA
  • 3,520
  • 1
  • 15
  • 31
pgSystemTester
  • 8,979
  • 2
  • 23
  • 49
  • Do you have to use R1C1? – AAA May 28 '19 at 20:36
  • 1
    I think it has to do with the way the formula is copied. Each successive row become R[1], R[2] etc. – AAA May 29 '19 at 00:02
  • @AAA, I can work around it with some techniques, I was just more curious as to a clear reason as to WHY this is happening. I appreciate the response, I'll give you a tick, but I think you kind of just restated my question – pgSystemTester May 29 '19 at 02:37

1 Answers1

0

While I can't explain the behaviour, I can offer this solution:

Do it in two steps

  • Use FormulaR1C1 to get the correct formula for the first target row
  • Then use Formula to propogate the formula down the target range
Sub Demo()
    Range("C4:H4").FormulaR1C1 = Range("C1:H1").FormulaR1C1
    Range("C4:H12").Formula = Range("C4:H4").Formula
End Sub
chris neilsen
  • 52,446
  • 10
  • 84
  • 123