VBA to increase cell reference by 1

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.

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