Dynamically Sum a column

[Ian Bertie]

Good afternoon Guru's

I am trying to dynamically sum a column (code below), however I am getting an error due to the cells having a formula (example: =IF(H47<>"",G47*H47,""))

Private Sub CommandButton1_Click()

lastrow = Worksheets("Invoice").Cells(Rows.Count, 9).End(xlUp).Row
Dim a As Integer
a = 0

For b = lastrow To 40 Step by - 1

a = a + Worksheets("Invoice").Cells(b, 9).Value

Next

Worksheets("invoice").Cells(lastrow + 2, 9).Value = a

End Sub

How can I create the total where the last cells is not blank

Thanks in advance

 

[Joe4]

The issue is that your formula sometimes returns a blank, which is a text value, and cannot be used in addition.
You could change your formula to return 0 instead of a blank, i.e.

=IF(H47<>"",G47*H47,0)

and use Formatting or Conditional Formatting on the column to hide those 0 values, if you do not want to see them.

Or how about just using the SUM function instead?
The big two advantages to using SUM is:
- you do not need to loop, so code will be faster
- SUM automatically ignores Text values, so you do not need to alter your original formula.

To use SUM in VBA, preface it with:

Application.WorksheetFunction.Sum(...)