Excel-VBA. Moving formulas without copying them

Question

I have the following code taken from my macro which works fine:

.Cells(curRow, "M").Copy
.Range("N" & curRow & ":R" & curRow).PasteSpecial Paste:=xlPasteFormulas

I would like to ask if there is a way to move formulas without copy-pasting them? I need them moved with changed references.

I assume copy-pasting is not the fastest way to do that.

score 3 · Accepted Answer · answered Apr 07 '16 at 08:08

3

If you want to change references, the only way I know is copy and paste. If you do not want that, you may use:

.Range("N" & curRow & ":R" & curRow).FormulaR1C1 = .Cells(curRow, "M").FormulaR1C1

answered Apr 07 '16 at 08:08

Vityata

42,633
8
55
100

that's what I was looking for. Thanks! – Sergey Ryabov Apr 07 '16 at 08:12

score 2 · Answer 2 · answered Apr 07 '16 at 08:10

2

.Range("N" & curRow & ":R" & curRow).Cells.Formula = .Cells(curRow, "M").Formula

answered Apr 07 '16 at 08:10

It puts the same formula which I have in the source cell, I'd need it to change references – Sergey Ryabov Apr 07 '16 at 09:06

score 0 · Answer 3 · answered Apr 07 '16 at 11:54

0

try this

.Cells(curRow, "M").Copy .Range("N" & curRow & ":R" & curRow)

answered Apr 07 '16 at 11:54

Karthick Gunasekaran

2,697
1
15
25

hymced · Answer 4 · 2022-09-18T10:22:15.623

Here is the best way to move/shift references in a formula, and it uses the function ConvertFormula()!

Function MoveFormulaReferences$(strFormula$, rngRelativeTo As Range, lngOffsetRows&, lngOffsetColumns)
    MoveFormulaReferences = Application.ConvertFormula( _
                                                       Application.ConvertFormula( _
                                                                                  strFormula, _
                                                                                  XlReferenceStyle.xlA1, _
                                                                                  XlReferenceStyle.xlR1C1, _
                                                                                  , _
                                                                                  rngRelativeTo), _
                                                       XlReferenceStyle.xlR1C1, _
                                                       XlReferenceStyle.xlA1, _
                                                       , _
                                                       rngRelativeTo.Offset(lngOffsetRows, lngOffsetColumns))
End Function

Now some examples:

?MoveFormulaReferences("=C3*2+C3", Range("$D$4"), -2, -2)

returns =A1*2+A1

?MoveFormulaReferences("=$C3*2+C3", Range("$D$4"), -2, -2)

returns =$C1*2+A1

It is also possible to "force-move" all references, even the ones made absolute by a leading $, using the ToAbsolute parameter of the ConvertFormula() function: pass XlReferenceType.xlRelative as argument in the nested ConvertFormula() call

Edit:

One could argue the use of the rngRelativeTo parameter: it appears it can be confounding... At first I thought that the above behaves exactly as if a cell containing the formula was moved, but @GSerg, in its own way, made me realize that is not entirely correct, here an example:

If cell C1 is copied and pasted in B1, the A1 reference in the formula being moved becomes #REF!. I expected my function with ConvertFormula() to have the same behavior than Excel's copy/paste but in fact, it "rotates the references from start to end" instead of "blocking" them when out of boundaries:

?MoveFormulaReferences("=A1*2", Range("C1"), 0, -2)

returns =XFC1*2

So one might think the rngRelativeTo parameter is useless in that case since it does not prevent the "blocking", and that internally it could be replaced by Range("A1") as a proxy, but it is incorrect: considering the same example: Range("A1").Offset(0, -2) cannot be resolved. In the end, a user-provided relative range is required to simulate a real scenario.

Range("A1") would work if both offsets are >= 0 Range("XFD1048576") would work if both offsets are <= 0 No single Range would work in any feasible case where one offset is > 0 and the other < 0, so rngRelativeTo is required to simulate a real copy/paste situation.

Edit 2:

Here is a function whose usage might be a little more intuitive for the op particular situation (still not using a copy/paste operation):

Function MoveCellFormulaReferences$(rngCell, lngOffsetRows&, lngOffsetColumns&)
    MoveCellFormulaReferences$ = Application.ConvertFormula( _
                                                       Application.ConvertFormula( _
                                                                                  rngCell.Formula, _
                                                                                  XlReferenceStyle.xlA1, _
                                                                                  XlReferenceStyle.xlR1C1, _
                                                                                  , _
                                                                                  rngCell), _
                                                       XlReferenceStyle.xlR1C1, _
                                                       XlReferenceStyle.xlA1, _
                                                       , _
                                                       rngCell.Offset(lngOffsetRows, lngOffsetColumns))
End Function

EDIT 3:

and even, reduced to its simplest form:

Function MoveCellFormulaReferences2$(rngCell, lngOffsetRows&, lngOffsetColumns&)
    MoveCellFormulaReferences2$ = Application.ConvertFormula( _
                                                       rngCell.FormulaR1C1, _
                                                       XlReferenceStyle.xlR1C1, _
                                                       XlReferenceStyle.xlA1, _
                                                       , _
                                                       rngCell.Offset(lngOffsetRows, lngOffsetColumns))
End Function

But the first version works with any String A1 formula, it does not suppose you have a cell holding the R1C1 translation or that you want to use a temp cell to translate it...

I don't know how to quickly prevent the "rotation" though, other than analyzing the formula string, but that defeats the purpose of my answer...

`ConvertFormula` is not a native VBA function. It is a part of the Excel object model which has nothing to do with VBA. The problem however is that this code is absolutely pointless because it performs a conversion that is not needed. If you observe carefully, you will notice that `=C3*2+C3` entered in `D4` and `=A1*2+A1` entered in `B2` (which is D4.Offset(-2, -2)) are exactly the same when viewed in R1C1: `=R[-1]C[-1]*2+R[-1]C[-1]`. So you could simply copy the R1C1 formula like the [accepted answer](https://stackoverflow.com/a/36470263/11683) suggests, and it would change in A1 accordingly. — GSerg, Sep 16 '22 at 22:21
Even if there was a point to this code, it would not be a reason to spam StackOverflow with four copies of it (https://stackoverflow.com/a/73750685/11683, https://stackoverflow.com/a/73750614/11683, https://stackoverflow.com/a/73750522/11683, https://stackoverflow.com/a/73750474/11683). — GSerg, Sep 16 '22 at 22:24
@GSerg you are absolutely correct, and unfailingly rigorous about the difference between VBA and the Excel object model. Nevertheless, I find this precision not really constructive to the problem, and not understandable by 80% of people that code in VBA (observation from some coworkers). I did observe carefully haha. You noticed the interesting part of my UDF: it does not use copy and a temp cell to move the references in a formula. And how can it be pointless if it solves the question of the op??? Please allow me to remind you the title: "Moving formulas without copying them" — hymced, Sep 17 '22 at 17:09
What I could agree on with you, it is that is seems a little superfluous. But unfortunately you got agressively beyond that agreement. Why? because you failed to see the point, and became mean as a classic defense mechanism. Now let me explain how this function, that you won't find anywhere else before yesterday is really helful: no copy needed, AND since Evaluate cannot evaluate R1C1 references, it can be used to evaluate a FormatCondition formula on each cell it AppliesTo. Yes, it has nothing to do with the op question, but hopefully you understand now :) Next time just ask ;) — hymced, Sep 17 '22 at 17:10
You call it spam, I simply call this answer to op questions with something new. Is it prohibited? — hymced, Sep 17 '22 at 17:11
That is why people don't find themselves in a need to "translate" formulas. Because to do so, you need to copy [`FormulaR1C1`](https://learn.microsoft.com/en-us/office/vba/api/excel.range.formular1c1), without translating it. And no, it is not a reason to spam. — GSerg, Sep 17 '22 at 20:26
https://stackoverflow.com/a/73757542/2981328 there also it doesn't do anything and I don't understand anything? Hope you will see the point. Again, no need to be so provocative without any reason. — hymced, Sep 18 '22 at 08:16
I am being suggested to move this extended discussion in a conversation. My post is an answer, and yet it is being unpromoted, so I fell comments are the right way to make (one) people understand its (relative) usefulness. Now a question: once a question is solved by a top answer, should we discredit and unpromote any other answer without even mentioning them for educational purposes? I don't think so. — hymced, Sep 18 '22 at 09:24
You seem to have found a hammer, and now everything looks like a nail to you. `Range`s speak in R1C1-English natively, so there is no need for conversion when working with ranges. Introducing a conversion where a simple copy will suffice is wrong, hence the downvotes. That other question about conditional formatting though, *there* your answer has merits, because conditional formatting and `FormulaArray` are two weird areas that only speak in A1-local. I suggest you delete the other instances of your answer and keep only the conditional formatting one, provided you fix the problems with it. — GSerg, Sep 18 '22 at 10:45

Excel-VBA. Moving formulas without copying them

4 Answers4

Linked