Solved: Summing a dynamic range

[Aussiebear]

I would like to enter a formula in cell D2 which sums up a dynamic range starting in column G2 and extending to the right. Is this the correct approach in line #9

Private Sub cmdNewEntry_Click()
Dim NewRow As Long, LastCol as Long
With Worksheets("Results")
NewRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
LastCol = Cells(Columns.Count,"G").End(xlRight).Column
Cells(NewRow, 1).Formula = "=Row()-1"
Cells(NewRow, 2).Value = Me.txtCaptain.Value
Cells(NewRow, 3).Value = Me.txtPOB.Value
Cells(NewRow, 4).Formula = WorksheetFunction.Sum(Range(Range("$G2"), Range("$G2").End(xlRight)))
'Cells(NewRow, 5).Formula = Lookup header for each row in Fish_Table to allocate points.
Cells(NewRow, 6).Formula = "=IF($E2>0,RANK(Table2[[#This Row],[Points]],[Points],1),"")"
End With
End Sub

[p45cal]

Cells(NewRow, 4).Formula = WorksheetFunction.Sum(Range(Range("$G2"), Range("$G2").End(xlToRight)))will work, only if there are no spaces within the range of cells you want to sum.

It will not necessarily put it into D2, depending on what NewRow is.

NewRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 will give you the row number of the first blank row in column A, where you use column A, rather than any other column, to decide that.

I see that you're trying to do something similar with getting the last column.
Which row do you want to use to determine the last column? At the moment you're using row 256 (xl2003) or row 16384 if using xl2007!
Edit post posting: Perhaps you're wanting to use NewRow to determine the last column?:
LastCol = Cells(NewRow,Columns.Count).End(xlToLeft).Column
End of Edit post posting.
If you're working in the same way as finding the last row you need something like:
LastCol = Cells(2,Columns.Count).End(xlToLeft).Columnwhere the 2 means your using what's in row to 2 determine the last column. If you want to use LastCol in your sum formula then

Cells(NewRow, 4).Formula = WorksheetFunction.Sum(Range("G2"),Cells(2,LastCol))) or perhaps
Cells(NewRow, 4).Formula = WorksheetFunction.Sum(cells(NewRow,6),Cells(NewRow,LastCol))) where the 6 represents column G
(All untested)

[Aussiebear]

Cells(NewRow, 4).Formula = WorksheetFunction.Sum(Range(Range("$G2"), Range("$G2").End(xlToRight)))will work, only if there are no spaces within the range of cells you want to sum.

It will not necessarily put it into D2, depending on what NewRow is.
Thank you for the correction to the line. There will be no spaces and yes I am starting this formula in cell D2 and then will fill down as my table grows.


NewRow = Cells(Rows.Count, "A").End(xlUp).Row + 1 will give you the row number of the first blank row in column A, where you use column A, rather than any other column, to decide that.
Yes, I am using column A to determine the last filled row


I see that you're trying to do something similar with getting the last column.
Which row do you want to use to determine the last column? At the moment you're using row 256 (xl2003) or row 16384 if using xl2007!
The columns from G onwards (Maximum possible 10 columns to be added from and including column G) are added by another piece of code supplied by Bob and works well. All rows in the table will have the same length.


Edit post posting: Perhaps you're wanting to use NewRow to determine the last column?:
LastCol = Cells(NewRow,Columns.Count).End(xlToLeft).Column
End of Edit post posting.
Up till this post I wasn't.


If you're working in the same way as finding the last row you need something like:
LastCol = Cells(2,Columns.Count).End(xlToLeft).Columnwhere the 2 means your using what's in row to 2 determine the last column. If you want to use LastCol in your sum formula then

Cells(NewRow, 4).Formula = WorksheetFunction.Sum(Range("G2"),Cells(2,LastCol)))
or perhaps
Cells(NewRow, 4).Formula = WorksheetFunction.Sum(cells(NewRow,6),Cells(NewRow,LastCol))) where the 6 represents column G
(All untested)
The LastCol bit I forgot to take out. I had initially intended to use it to do something else and still may. I thank you for the hints.

[mdmackillop]

As you are using WITH, I would qualify the ranges to prevent possible errors if the macro is run from another sheet

With Worksheets("Results")
NewRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
LastCol = .Cells(Columns.Count,"G").End(xlRight).Column
.Cells(NewRow, 1).Formula = "=Row()-1"
.Cells(NewRow, 2).Value = Me.txtCaptain.Value
.Cells(NewRow, 3).Value = Me.txtPOB.Value
.Cells(NewRow, 4).Formula = WorksheetFunction.Sum(Range(.Range("$G2"), .Range("$G2").End(xlRight)))
'etc.

[p45cal]

True enough. I wonder whether Aussiebear realises that despite using .Formula (.Value is used elsewhere) he is still going to get a value and not a formula in that cell, which won't update as other cells change.

[Aussiebear]

@ p45cal: I am wanting the value for the cells.

Some of the values needed, will be generated by the use of a formula/s ,whilst other values will come from a form. The form will be called from a button on the Results worksheet and will be used only on the Results worksheet.

@ mdmackillop: Can you qualify your comment further please?

[mdmackillop]

With Worksheets("Results")
NewRow = .Cells(1,1).value
end with
will return the value in Results cell A1


With Worksheets("Results")
NewRow = Cells(1,1).value
end with
will return the value of A1 on the active sheet.

[Aussiebear]

Thanks Malcolm