Increment excel formula by 1

user_8895098 0
Viewed 1158

I'm trying to copy and paste a few cells keeping the format and them ebing linked to a table.

Currently i have a table but i am referencing it from another sheet e.g:

=Sheet2!A1  =Sheet2!B1  =Sheet2!C1
=Sheet2!D1  =Sheet2!E1

I'm trying to copy it down 4 lines at a time and paste multiple times, the values keep going to the current cell ID but i'm wanting to increment them by 1 each time, e.g. when i paste i would like them to be:

=Sheet2!A2  =Sheet2!B2  =Sheet2!C2
=Sheet2!D2  =Sheet2!E2

Is there any quicker way than manually changing all of the cell ids to +1?

Thanks!

excel-formula vba excel
4 Answers

Select the cells you want and run the following in VBA:

Public Sub TestMe()

    Dim myCell  As Range

    For Each myCell In Selection
        If myCell.HasFormula Then myCell.Formula = myCell.Formula & "+1"
    Next myCell

End Sub

If you press it too many times, this is how to remove it the last +1:

Public Sub UnTestMe()

    Dim myCell As Range

    For Each myCell In Selection
        If myCell.HasFormula Then myCell.Formula = Left(myCell.Formula, _
                                                Len(myCell.Formula) - 2)
    Next myCell

End Sub

Concerning the comment of @SJR, if you want to change the reference address of the cell, this is one possible workaround:

Public Sub TestMe()

    Dim myCell As Range

    For Each myCell In Selection
        If myCell.HasFormula Then myCell.Formula = Left(myCell.Formula, _
                                                Len(myCell.Formula) - 1) & 2
    Next myCell

End Sub

Simply change the &2 to the number you want to refer to.

user_54486260

A math trick.

=INDIRECT("Sheet2!A"&INT(ROW()/4)-*offset*)

Addjust offset first and then you can copy and paste it down as you wish.

The ROW() returns the row number where the formula located. For example, if the formula you type is in cell A20, ROW() returns 20. i.e. if you need to show Sheet2!A1 in A20, the offset should be 4. The formula becomes =INDIRECT("Sheet2!A"INT(ROW()/4)-4)

user_85556480 
ThisWorkbook.Sheets("Your_Sheet_Name").Range("A99999").End(xlUp).Offset(1, 0)

Modify the above code. What this is doing is selecting cell A99999 and then using the Ctrl and up arrow command to select the last row with a value and offsetting by one so it actually selects the cell below that. You can then paste whatever you want into that cell.

user_89583830

Here's some Code I've Written for myself. It's fairly simple to edit with changeable increments and Row spacings.

Sub MakeFormulas()
    Dim sh As Worksheet: Set sh = ActiveWorkbook.Sheets("YourWorksheet")
    Dim field As String
    Dim formula As String
    Dim i As Integer: i = 2 'Every x Line you want to Paste your Formula to
    Dim a As Integer: a = 7 'Starting Row Number of Data on the Sheet you want to fetch
    Do While i < 1000 'How many Rows down you wan't to paste it to
        field = "C" + CStr(i) 'Column where you paste to + The Row Number
        formula = "=Switches!A" + CStr(a) 'The Reference Column + Row Number which gets incremented.
        sh.Range(field) = formula

        a = a + 1 'By which Number your FOrmula should get incremented
        i = i + 2 'Every i (Second) row gets the Formula Pasted.
    Loop
End Sub
Admin1
Related