VBA help - sum variable column

[phamt137]

VBA beginner here, attempting to sum variable number of columns down each row.
I want to sum from column G to the next blank column (number of columns vary), with the sum of each row in column F (number of rows also varies)

This is as close as I've gotten:

Sub addcolumns()

   
    Dim i As Long
    LastColumn = ThisWorkbook.Sheets("sheet1").Cells(2, Columns.Count).End(xlToRight)
    
    For i = 2 To Cells(Rows.Count, "G").End(xlDown).Row
    Range("F" & i) = WorksheetFunction.Sum(Range("G" & i & ":" LastColumn & i))
 
    Next i
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
You need to use xlToLeft, not xlToRight
and xlUp rather than xlDown

 

[Joe4]

Welcome to the Board!

A few issues. One is that LastColumn returns a column number, not letter.
And you use the wrong wrong on your "xl" statements.

Try this:

Sub addcolumns()

    Dim i As Long
    Dim LastColumn As Long
    Dim rng As Range
   
    LastColumn = ThisWorkbook.Sheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column
   
    For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row
        Set rng = Range(Cells(i, "G"), Cells(i, LastColumn))
        Range("F" & i) = WorksheetFunction.Sum(rng)
     Next i
    
End Sub

Edit: Didn't see Fluff's comments before I posted my response, so sorry for echoing those statements.

 

[phamt137]

Thank you both for the input!

xlToLeft for LastColumn only returns column G, but I'd like to sum cells from column G to L. I tried changing to xlToRight, which sums the correct cells from G to L, but also sums all values to the end of the row.
Is there a way to sum only to the next blank column?

Sub addcolumns()

  Dim i As Long
    Dim LastColumn As Long
    Dim rng As Range
   
    LastColumn = ThisWorkbook.Sheets("Sheet1").Cells(2, Columns.Count).End(xlToRight).Column
   
    For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row
        Set rng = Range(Cells(i, "G"), Cells(i, LastColumn))
        Range("F" & i) = WorksheetFunction.Sum(rng)
     Next i
     
End Sub

 

[Joe4]

xlToLeft for LastColumn only returns column G, but I'd like to sum cells from column G to L. I tried changing to xlToRight, which sums the correct cells from G to L, but also sums all values to the end of the row.
Is there a way to sum only to the next blank column?

The way you have the original code (and I followed your lead) is that you are only looking at row 2 to determine the last column.
Are you wanting to find the last column on EACH individual row, or do all the rows end at the same column?

 

[Joe4]

Also note that "Columns.Count" takes you to the LAST possible column on an Excel file.
You would not want to use "xlToRight" on that, because you cannot go any further right if you are already in the last possible column!

The code that I gave you:

LastColumn = ThisWorkbook.Sheets("Sheet1").Cells(2, Columns.Count).End(xlToLeft).Column

finds the last column with data on row 2 by starting in the last possible column on the sheet, and then going to the left under it finds something.

If you wanted to get the last column with data before the first blank column AFTER column G, then you would use something like this:

LastColumn = ThisWorkbook.Sheets("Sheet1").Cells(2, "G").End(xlToRight).Column

(start in column G, and move to the right of that until you find a blank column).

If each row may end at a different column, then you will want this line inside of your loop, and change the hard-coded "2" (for row number) to "i", i.e.

    For i = 2 To Cells(Rows.Count, "G").End(xlUp).Row
        LastColumn = ThisWorkbook.Sheets("Sheet1").Cells(i, "G").End(xlToRight).Column
        ....

If you are still having issues, please post an example of what your data looks like, and explain exactly what you want to happen, based on that example.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[phamt137]

If you wanted to get the last column with data before the first blank column AFTER column G, then you would use something like this:

LastColumn = ThisWorkbook.Sheets("Sheet1").Cells(2, "G").End(xlToRight).Column

(start in column G, and move to the right of that until you find a blank column).

This worked for me, with some adjustments on my end. It is summing in the correct direction (I should have specified that I want to sum everything to the right of column G until the first blank, as you correctly assumed). But I had to rearrange columns because there is more data after the blank column and it's summing everything to the end of the column. Not sure how to get it to stop summing after the first blank column. But I am very happy with this result given that I've spent nearly 4 days trying to figure this out so I will cut my losses and settle for having to rearrange columns.
Thanks for your help, and thanks for letting me know about the other tools on here! Lots to be explored/learned.

 

[Joe4]

You are welcome.

If you still are having an issue, please post a sample of your data for us to see, and explain exactly which columns should be summed in that example.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a “Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.