VBA to increase cell reference by 1

Question

VBA to increase cell reference by 1

CamaDragon 25

I want to copy into my ActiveCell a formula that is 36 rows previous to the ActiveCell. But I want to increase that cell reference by 1. This cell reference is on a different sheet.

For example, I am in ActiveCell B200 on Sheet1. I want to copy the cell reference that is 36 rows previous (B164) but add 1. This cell is referencing Sheet2, Cell A20 and I want to change the formula to A21 on Sheet 1 in the ActiveCell.

0 comments

3 answers

Your answer

Answer 1

Translate the formula from 36 rows above as though it moved down only one row, then assign that translated formula to the active cell.

Sub CopyFormulaAndIncrementReference()
    Dim target As Range
    Dim source As Range
    Dim r1c1Formula As Variant

    Set target = ActiveCell
    If target.Row <= 36 Then Exit Sub

    Set source = target.Offset(-36, 0)

    r1c1Formula = Application.ConvertFormula( _
        source.Formula, xlA1, xlR1C1, xlRelative, source)

    target.Formula = Application.ConvertFormula( _
        r1c1Formula, xlR1C1, xlA1, xlRelative, source.Offset(1, 0))
End Sub

For your example:

Active cell: Sheet1!B200
Source formula cell: Sheet1!B164
Formula in B164: =Sheet2!A20
Result placed in B200: =Sheet2!A21

Offset(-36, 0) identifies the cell 36 rows above without selecting it.^1^ ConvertFormula first represents the references relative to B164, then reconstructs them relative to B165; this advances relative row references by exactly one rather than by the 36-row distance to B200.^2^

Important limitations

References with an absolute row, such as =Sheet2!A$20 or =Sheet2!$A$20, will remain on row 20. Use a relative reference such as A20 if it must increment.
Every relative reference in the formula is translated one row down, not only the first cross-sheet reference.
Application.ConvertFormula has a 255-character formula limit.^2^

References

Answer 2

Brian Pham (WICLOUD CORPORATION) 1,425 Microsoft External Staff Moderator

Hi @CamaDragon ,

You can use VBA's Application.ConvertFormula method to copy the formula from the cell 36 rows above the active cell while incrementing its relative row references by 1.

For example, if B164 contains = Sheet2!A20, running the macro with B200 as the active cell will place=Sheet2!A21 in B200.

You can use the following VBA code:

Sub CopyFormulaAndIncrementReference()
    Dim target As Range
    Dim source As Range
    Dim r1c1Formula As Variant

    Set target = ActiveCell
    
	If target.Row <= 36 Then 
		MsgBox"The active cell must be below row 36."
		Exit Sub
	End If

    Set source = target.Offset(-36, 0)
    
	r1c1Formula = Application.ConvertFormula( _
        source.Formula, xlA1, xlR1C1, xlRelative, source)
    
	target.Formula = Application.ConvertFormula( _
        r1c1Formula, xlR1C1, xlA1, xlRelative, source.Offset(1, 0))
End Sub

Few additional things for you to considerate is that: the macro uses ActiveCell to determine the target, so make sure you select intended destination cell before running it. And this approach increments all relative row references in the formula. Absolute row references, such as $A$20 or A$20, remain unchanged.- If the source formula is: =Sheet!A20+$B$30

The resulting formula will be: =Sheet2!A21+$B$30

Here, A20 changes to A21, while $B$30 remains unchanged because its row and column are absolute references. I f your formula contains multiple references, and you only want to increment a specific one, the logic would need to be adjusted accordingly.

If you found my response helpful or informative, I would greatly appreciate it if you could follow this guide for your confirmation.

Thank you.

CamaDragon 25 Reputation points

2026-11-13T03:58:16.55+00:00

See my additional comment below Marcin's answer.
Brian Pham (WICLOUD CORPORATION) 1,425 Reputation points Microsoft External Staff Moderator

2026-11-13T04:04:49.96+00:00
The AI answer mostly works, except I forgot that target cell to be copied is two rows previous. (B198). This is to be used in more that just this one cell, it will be repeated every time a set of 35 rows are inserted. In the AI answer it sets the target = ActiveCell. I need it 2 rows previous to the ActiveCell and I'm not getting the proper syntax.

Hi @CamaDragon ,

I see your comment, for your scenario:

You only need to change one line. Instead of Set target = ActiveCell, use Offset to move up two rows from the active cell:

Set target = ActiveCell.Offset(-2, 0)

Since you'll run this again every time you insert a new block, here is the full macro. The two distances are now settings at the top, so you can change them easily:

Sub CopyFormulaAndIncrementReference() Const ROWS_UP_TO_TARGET As Long = 2 Const ROWS_UP_TO_SOURCE As Long = 36 Dim target As Range Dim source As Range Dim r1c1Formula As Variant If ActiveCell.Row <= ROWS_UP_TO_TARGET + ROWS_UP_TO_SOURCE Then MsgBox "Not enough rows above the active cell.", vbExclamation Exit Sub End If Set target = ActiveCell.Offset(-ROWS_UP_TO_TARGET, 0) Set source = target.Offset(-ROWS_UP_TO_SOURCE, 0) If Not source.HasFormula Then MsgBox "Cell " & source.Address(False, False) & " has no formula.", vbExclamation Exit Sub End If r1c1Formula = Application.ConvertFormula( _ source.Formula, xlA1, xlR1C1, xlRelative, source) target.Formula = Application.ConvertFormula( _ r1c1Formula, xlR1C1, xlA1, xlRelative, source.Offset(1, 0)) End Sub

For example, with the active cell at B200:

The new formula goes into B198 (2 rows up).

It is copied from B162 (36 rows above B198).

If B162 contains =Sheet2!A20, then B198 gets =Sheet2!A21.

If you meant something different, for example that the formula should be copied from B198 itself, just change the two settings at the top. ROWS_UP_TO_TARGET is where the result goes and ROWS_UP_TO_SOURCE is how far above that to copy from. You don't need to change anything else.

Hope this helps!

Answer 3

If B164 contains the formula:

=Sheet2!A20

and you want B200 to contain:

=Sheet2!A21

then you should be able to accomplish this using FORMULATEXT, SUBSTITUTE, and INDIRECT. Enter this formula in B200:

=INDIRECT(SUBSTITUTE(FORMULATEXT(B164),"20","21"))

However, this is a literal text replacement. It assumes the row number 20 appears only where you intend to change it.

If you want to extract the referenced cell address and increment its row number automatically, use:

=LET(f,FORMULATEXT(B164),s,TEXTAFTER(f,"!"),c,TEXTBEFORE(s,"!"),r,ROW(INDIRECT(c))+1,INDIRECT("Sheet2!"&LEFT(c,MIN(FIND({0,1,2,3,4,5,6,7,8,9},c&"0123456789"))-1)&r))

However, this approach is too complicated for a direct cell reference. If B164 always references a cell on Sheet2, a more appropriate solution is to use:

=INDIRECT("Sheet2!A"&(ROW(B164)-180))

In B200, this produces Sheet2!A21. The row calculation is based on B164 being 36 rows above B200 and the original reference being A20.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

CamaDragon 25 Reputation points

2026-11-13T02:27:56.9333333+00:00

The AI answer mostly works, except I forgot that target cell to be copied is two rows previous. (B198). This is to be used in more that just this one cell, it will be repeated every time a set of 35 rows are inserted.

In the AI answer it sets the target = ActiveCell. I need it 2 rows previous to the ActiveCell and I'm not getting the proper syntax.