Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2

[skyshow1]

I am writing a reusable VBA routine to copy data and formulas from one Excel workbook to another. I am working with large amounts of data and formulas and have found that copying by assigning values (Range2.Value2 = Range1.Value2 vs. Cut/Paste) has better performance. However, I can't figure out how to do this to copy formulas. I believe copying using the R1C1 reference notation should do this correctly (e.g., Range2.FormulaR1C1 = Range1.FormulaR1C1), as using the straight Range2.Formula = Range1.Formula method does not increment the cell references in the copied formulas. Does anybody know why the .FormulaR1C1 method increments rows and columns by 2??!!!? I am using the latest Office 365. The below example illustrates the issue:

Option Explicit

Sub TestAssignFormulaR1C1_Click()

  Range("A1:E5").Clear

  ' Place formula in a single cell
  Range("A1").Formula = "=ADDRESS(ROW(A1), COLUMN(A1))"
 
  ' Copy formula to the right
  Dim source_range As Range: Set source_range = Range("A1")
  Dim target_range As Range: Set target_range = Range("B1:E1")
  target_range.FormulaR1C1 = source_range.FormulaR1C1
 
  ' Copy formulas from top row down
  Set source_range = Range("A1:E1")
  Set target_range = Range("A2:E5")
  target_range.FormulaR1C1 = source_range.FormulaR1C1
 
  ' This gives an incorrect result starting at row 3:
  '      A     B     C     D     E
  '   ------------------------------
  ' 1 | $A$1  $B$1  $C$1  $D$1  $E$1
  ' 2 | $A$2  $B$2  $C$2  $D$2  $E$2
  ' 3 | $A$4  $B$4  $C$4  $D$4  $E$4
  ' 4 | $A$6  $B$6  $C$6  $D$6  $E$6
  ' 5 | $A$8  $B$8  $C$8  $D$8  $E$8
 
  MsgBox "Row 1 formulas copied down via assignment .FormulaR1C1 = .FormulaR1C1." & _
         vbCrLf & vbCrLf & "Notice the rows are off starting at row 3!", vbCritical
 
  ' Alternative method which uses clipboard BUT is slower:
  source_range.Copy
  target_range.PasteSpecial xlPasteFormulas
 
  ' Of course gives the expected result:
  '      A     B     C     D     E
  '   ------------------------------
  ' 1 | $A$1  $B$1  $C$1  $D$1  $E$1
  ' 2 | $A$2  $B$2  $C$2  $D$2  $E$2
  ' 3 | $A$3  $B$3  $C$3  $D$3  $E$3
  ' 4 | $A$4  $B$4  $C$4  $D$4  $E$4
  ' 5 | $A$5  $B$5  $C$5  $D$5  $E$5
 
  MsgBox "The standard Copy with PasteSpecial works as expected.", vbInformation
 
  ' Now try the same thing but copying a single column of data
  Range("A1:E5").Clear
  Range("A1").Formula = "=ADDRESS(ROW(A1), COLUMN(A1))"
 
  ' Copy formula down
  Set source_range = Range("A1")
  Set target_range = Range("A2:A5")
  target_range.FormulaR1C1 = source_range.FormulaR1C1
 
  ' Copy formulas from left column over
  Set source_range = Range("A1:A5")
  Set target_range = Range("B1:E5")
  target_range.FormulaR1C1 = source_range.FormulaR1C1
 
  ' This gives an incorrect result starting at column C:
  '      A     B     C     D     E
  '   ------------------------------
  ' 1 | $A$1  $B$1  $D$1  $F$1  $H$1
  ' 2 | $A$2  $B$2  $D$2  $F$2  $H$2
  ' 3 | $A$3  $B$3  $D$3  $F$3  $H$3
  ' 4 | $A$4  $B$4  $D$4  $F$4  $H$4
  ' 4 | $A$5  $B$5  $D$5  $F$5  $H$5
 
  MsgBox "Column A formulas copied to right via assignment .FormulaR1C1 = .FormulaR1C1." & _
         vbCrLf & vbCrLf & "Notice the columns are off starting at column C!", vbCritical
 
End Sub

Am I doing something wrong? Any help or insights on this would be much appreciated!!!

 

[StephenCrump]

Does anybody know why the .FormulaR1C1 method increments rows and columns by 2??!!!?

Am I doing something wrong?

The quick answer is no to both questions. That's just the annoying way FormulaR1C1 behaves when you copy it like this. Not what you'd expect, and not very helpful.

Interestingly, it works OK for a single column copied down, e.g. if you change your code to:

' Copy formulas from top row down
Set source_range = Range("A1:A1")
Set target_range = Range("A2:A5")
target_range.FormulaR1C1 = source_range.FormulaR1C1

Do you need to use .FormulaR1C1, rather than .Formula, to do your copying?

(And in the bigger picture, perhaps there is also the option of saving and editing a copy of your workbook, or copying entire sheet(s)?)

 

[skyshow1]

Interestingly, it works OK for a single column copied down, e.g. if you change your code to:

' Copy formulas from top row down
Set source_range = Range("A1:A1")
Set target_range = Range("A2:A5")
target_range.FormulaR1C1 = source_range.FormulaR1C1

That is true. In fact, you can use it to copy a single cell to an entire range. E.g.:

  Set source_range = Range("A1")
  Set target_range = Range("A1:E5")
  target_range.FormulaR1C1 = source_range.FormulaR1C1

I consider this a "special case" I can use to my advantage!

Do you need to use .FormulaR1C1, rather than .Formula, to do your copying?

Can you expand on this more? As soon as I use .Formula, the references come out wrong, unless my target includes my source. E.g., this works:

  Set source_range = Range("A1")
  Set target_range = Range("A1:E5")
  target_range.Formula = source_range.Formula

But this does not:

  Set source_range = Range("A1")
  Set target_range = Range("A3:E7")
  target_range.Formula = source_range.Formula

The latter does work when using .FormulaR1C1.

(And in the bigger picture, perhaps there is also the option of saving and editing a copy of your workbook, or copying entire sheet(s)?)

In some cases, yes. In the particular problem I am currently working on, I need to copy pieces of information. to an existing worksheet.

Thanks so much for your reply, it has been immensely helpful. I was pulling my hair out about the incrementing by 2! It is great to have validation that that is how it works, even though it doesn't seem to make sense. I'd love to hear the actual logic behind it, and I am definitely open for additional suggestions in relation to this. However, I think I can develop some workarounds for the behavior.

 

[StephenCrump]

Can you expand on this more? As soon as I use .Formula, the references come out wrong, unless my target includes my source.

True. If target is in a different row/column to source, you'll need to use .FormulaR1C1 to correctly adjust the row/column references (and even this won't work if you have locked row/column references that you want to move). You could use .FormulaR1C1 to set the initial cell(s) of target, and then use target.Formula to populate the rest of target.

This is all predicated on your assumption that using .Formula = .Formula will be faster than a copy/paste. I don't know that this is correct. It would be relatively simple to run a timer on the two alternatives to compare.

If you're not already doing it, you could also speed up execution by turning off ScreenUpdating, and depending on whether you need to update values before pasting, set Calculation to xlManual.

 

[skyshow1]

True. If target is in a different row/column to source, you'll need to use .FormulaR1C1 to correctly adjust the row/column references (and even this won't work if you have locked row/column references that you want to move). You could use .FormulaR1C1 to set the initial cell(s) of target, and then use target.Formula to populate the rest of target.

Ah yes, a two-step process like that would be a good approach to get around the "incrementing by 2" issue.

This is all predicated on your assumption that using .Formula = .Formula will be faster than a copy/paste. I don't know that this is correct. It would be relatively simple to run a timer on the two alternatives to compare.

True, it will be easy to do some timer comparisons. I agree that it is currently unknown whether copy/paste will be slower, especially after implementing the additional logic/steps to workaround the goofiness of the .FormulaR1C1 copy behavior!

If you're not already doing it, you could also speed up execution by turning off ScreenUpdating, and depending on whether you need to update values before pasting, set Calculation to xlManual.

Yes, those are critical functions to speed things up, which I always use. Thanks again for your help and insights!

 

[Alex Blakenburg]

I also don't know what the logic is behind the row incrementing by 2 using FormulaR1C1 but here is another option:

  ' Copy formulas from top row down
  Set source_range = Range("A1:E1")
  Set target_range = Range("A1:E5")
  target_range.FillDown