Getting when trying to add cells

Question

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

Do nto use xldown. [Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and then use `lastrow`,`lastrow-1`, `lastrow-2` and so on — Siddharth Rout, Nov 08 '21 at 19:59

score 0 · Answer 1 · answered Nov 08 '21 at 20:12

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.

Spinner · Accepted Answer · 2021-11-09T20:13:35.847

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

Notes: This code fully replaces your Sub (in the OP).
Based on the name of your Sub and the default control naming convention, your 'CommandButton3' button is either:
a) an in-sheet ActiveX command button or
b) a UserForm command button

If a): The code goes to the Sheet code module where the button exists
If b): The code goes to UserForm code module

Thank you for this! would you be able to clarify what part of my code I need to place this in? i have tried a few different ways and seem to be getting errors? — Shane Cahalane, Nov 09 '21 at 14:15
Sure. Have added notes to the above on how to use. Let me know if still having issues :) — Spinner, Nov 09 '21 at 20:14

Getting when trying to add cells

2 Answers2