• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to add cells in a Row

deyo001

New Member
I am having trouble writing the code so that a loop will add the cells in a row and place the sum on the first open cell on the right. I need it to be in a loop and also work with any amount and size of other rows. I have the code to work so that it will place the column cells at the bottom but I am unable to figure out how to just add the cells in the row.

Here is the code that I have so far
Code:
Option Explicit

Sub RowsGetStats()


Dim LastRow As Long, LastColumn As Long, lngRow As Long

LastRow = Cells.Find(What:="*", After:=Range("A1"), _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
       
For lngRow = 1 To LastRow

    LastColumn = Cells(lngRow, Columns.Count).End(xlLeft).Column
   
    With Cells(lngRow, LastColumn + 1)
        .FormulaR1C1 = "=SUM(R" & LastRow & "C:RC2)"
    End With
Next lngRow


End Sub
 
Last edited by a moderator:
hi deyo001, and welcome to the forum! :awesome:

You were pretty close. Try this:
Code:
Sub RowsGetStats()
Dim LastRow As Long, LastColumn As Long, lngRow As Long

LastRow = Cells.Find(What:="*", After:=Range("A1"), _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Application.ScreenUpdating = False
For lngRow = 1 To LastRow

    'Need to be xlToLeft, not just xlLeft. VBA is weird...
    LastColumn = Cells(lngRow, Columns.Count).End(xlToLeft).Column
   
    'We want to alter which column is last value, not the row
    With Cells(lngRow, LastColumn + 1)
        .FormulaR1C1 = "=SUM(RC1:RC" & LastColumn & ")"
    End With
Next lngRow
Application.ScreenUpdating = True

End Sub
 
@Luke M Thank You! It works wonderfully. I have a semester in VB and this semester is VBA so it is a confusing time right now :). I'll most likely be back on here again soon with questions.
 
FWIW you can replace the With..End With block by this:
Code:
   Cells(lngRow, LastColumn + 1).FormulaR1C1 = "=SUM(RC1:RC[-1])"
 
Back
Top