Getting when trying to add cells-CodePudding

What i am trying to do on VBA is to select only the four most recent data entry's and add them together and place the result to the right of the fourth data entry, when the next four data entry's are input i want the formula to only add these entry's and input that result to the right of the last entry. can someone be able to tell me where I'm going wrong please!

    Private Sub CommandButton3_Click()
Dim wks As Worksheet
Dim addnew As Range
Set wks = Sheet2
Dim x

x = wks.Range("G2").End(xlDown).Value   wks.Range("G2").End(xlDown).Offset(-1, 0).Value   wks.Range("G2").End(xlDown).Offset(-2, 0).Value   wks.Range("G2").End(xlDown).Offset(-3, 0).Value
Set addnew = wks.Range("H65356").End(xlUp).Offset(1, 0)
addnew.Offset(3, 0).Value = x

CodePudding user response：

Shane, if I get you right, you just want to get sum of a range consists of four rows. This can be done easily like this:

Assuming there is a sum formula of yours in cell H5;

Select range of H2:H5,

copy,

select range of H6 to H9 (or to H13, or H17. Just make sure you select a range in lenght of 4 rows or multiple of 4s),

and paste. Thats all.

CodePudding user response：

On the surface of it, your code works just fine.
One concern is that the first use will write to row 1 (not beside the last 'active' row in column G).
Below is one approach that does what you want and avoids that issue.
Note:
o Generally speaking, it's better practice to use sheet names (rather than sheet code names).
o The code below assumes sheet code-named 'Sheet2' is also named 'Sheet2'

Private Sub CommandButton3_Click()

    Dim rgColGLast As Range
    
''' Get last 'active' (i.e. non-empty) cell in Column G
''' o  If the target sheet isn't named Sheet2: Amend the line below to the correct name.
''' o  If you need to use code name, change the line to 'With Sheet2.Columns("G")'
    With Worksheets("Sheet2").Columns("G")
        Set rgColGLast = .Cells(.Cells.Count).End(xlUp)
    End With

''' Put the sum of the last four cells to column H (beside last cell in G)
    rgColGLast.Offset(0, 1) = WorksheetFunction.Sum(Range(rgColGLast, rgColGLast.Offset(-3)))

End Sub