How can I increment the range in a cell to shift right one cell

Question

I am attempting to create an excel macro to shift selected ranges in cell as I require.

For this example, say I select a cell and it contains =sum(A1:A6), I would like the code to see =sum(A1:A6), then shift it by one to =sum(A2:A7).

I would like the code to be able to work in any active cell selected which would have a similar sum(range), then just click a button.

My second question adding on to this is, for example, =sum(A2:A7) I would like to add or minus 1 making it =sum(A2:A8) or =sum(A1:A7). Add or Minus as separate macros.

I appreciate the help, I am not sure if I was searching the correct terminology for solutions but thought I would ask.

Thank you in advance.

[This](https://stackoverflow.com/questions/13694345/converting-a-cells-formula-to-text-using-excel-vba) should get you started. — findwindow, May 04 '22 at 20:26

karma · Accepted Answer · 2022-05-06T02:11:51.823

If I understand you correctly, maybe something like this ?

Sub IncreaseRowFormulaBy1()
Dim v1 As String
Dim v2 As String
Dim x1 As String
Dim x2 As String

With ActiveCell
    v1 = Split(Split(.Formula, ":")(0), "(")(1)
    v2 = Replace(Split(.Formula, ":")(1), ")", "")
    x1 = Range(v1).Offset(1, 0).Address(0, 0)
    x2 = Range(v2).Offset(1, 0).Address(0, 0)
    .Replace What:=v1, Replacement:=x1, LookAt:=xlPart
    .Replace What:=v2, Replacement:=x2, LookAt:=xlPart
End With
End Sub

Sub DecreaseRowFormulaBy1()
Dim v1 As String
Dim v2 As String
Dim x1 As String
Dim x2 As String

With ActiveCell
    v1 = Split(Split(.Formula, ":")(0), "(")(1)
        If Range(v1).Row > 1 Then
            v2 = Replace(Split(.Formula, ":")(1), ")", "")
            x1 = Range(v1).Offset(-1, 0).Address(0, 0)
            x2 = Range(v2).Offset(-1, 0).Address(0, 0)
            .Replace What:=v1, Replacement:=x1, LookAt:=xlPart
            .Replace What:=v2, Replacement:=x2, LookAt:=xlPart
        End If
End With
End Sub

The code assumed that the activecell will always contains a cell address which looks like this "(cell address1 : cell address2)". Example : (A1:A10) ... (B5:B12) ... (C2:C9), etc.

The v1 variable split the formula string with ":" separator and get the first result, then split the result with "(" separator and get the second result. The final result is the cell address1.

The v2 variable split the formula string with ":" separator and get the second result, then replace the result which has ")" into "". The final result is the cell address2.

The x1 variable is to get the v1 address after offset it by one (or minus one). The x2 variable is to get the v2 address after offset it by one (or minus one).

Then finally the code replace the formula in the active cell, from variable v1 to varibale x1 and from variable v2 into variable x2.

For the DecreaseRowFormulaBy1 Sub, it will check first if the cell-address1 row is greater than 1 (because if it's row 1 then it cannot be decreased to row zero).

Run the macro debugging (step by step) by press F8, and see what is the value of each variable in the Locals Window.

That if I'm not mistaken to get what you mean.

I've been thinking for a shorter code, and this is what I get :

Sub test1()
Dim rgOld As String
Dim rgNew As String
    With ActiveCell
    rgOld = Split(Split(.Formula, "(")(1), ")")(0)
    rgNew = Range(rgOld).Offset(1, 0).Address(0, 0)
    .Replace what:=rgOld, replacement:=rgNew, lookat:=xlPart
    End With
End Sub

Sub test2()
Dim rgOld As String
Dim rgNew As String
    With ActiveCell
    rgOld = Split(Split(.Formula, "(")(1), ")")(0)
        If Range(Split(rgOld, ":")(0)).Row > 1 Then
            rgNew = Range(rgOld).Offset(-1, 0).Address(0, 0)
            .Replace what:=rgOld, replacement:=rgNew, lookat:=xlPart
        End If
    End With
End Sub

The process is similar with the previous sub, but use less variables. The rgOld variable is to get the range of cell address in the original formula, rather than get the cell-address1 and then get the cell-address2. To increase/decrease one row, it offset 1 (or -1) of that range and then it get the address as rgNew variable.

This provided me with the right framework to understand what I was trying to do, thanks for understanding what I meant. I adjusted the coding for columns and it works perfectly. — Jazye, May 05 '22 at 13:21

How can I increment the range in a cell to shift right one cell

1 Answers1