Wrong answer with SUM and empty cells

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If I use the following:

Code:
Sheets("Sheet1").Range("D13:D" & Cells(Rows.Count, "D").End(xlUp).Row).Select

It selects the right range, including some empty cells. In other words, it
selects from D13 to the last cell with a value in it.

However, when I use the following:

Code:
Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("D13:D" & Cells(Rows.Count, "D").End(xlUp).Row)
............
here some code
............
Worksheets("Sheet1").Range("D10").Value = _
    Application.WorksheetFunction.Sum(myRange)

It gives the wrong answer. It must be the right answer but not what I am after. It does not
include the amount of cells from the bottom up equal to the amount of empty cells (rows).
I have tried all kind of different approaches found on this forum but I can't get it.
Could someone please tell me where I am going wrong.

Thanks and regards.

John
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
It gives the wrong answer.

I don't really see how it doesn't give you the right answer.

As it sits it will provide you the sum of all cells in D13:D?, where ? is where your data ends.

It must be the right answer but not what I am after.

What exactly are you after?
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Hi Smitty.
Thanks for looking into this.
I have numbers, amounts with the cells formatted to currency 2 decimal places, in
cells D13 to D42 except cells (rows) D21, D27, D33, D39 and D41 which are blank.
If I total the cells manually, it adds up to 20,278.04. With the previous mentioned
VBA it totals to 15,181.25. The total of the last 5 amounts is 5,096.79. This is
also the difference in the manually totalled and VBA totalled amounts.
If I use

Code:
Set myRange = Worksheets("Sheet1").Range("D13:D250")

the answer is the same as the manually totalled amount.

This can't be coincidence, can it?

Hope to have answered your questions and that it makes it a little clearer.

Following is the complete macro.

Code:
Public Sub CreateInvoiceB()
    
    Dim NextRow As Long
    Dim i As Long
    Dim a As Variant
    Dim myRange As Range
    Set myRange = Worksheets("Sheet1").Range("D13:D" & Cells(Rows.Count, "D").End(xlUp).Row)
    'Set myRange = Worksheets("Sheet1").Range("D13:D250")
    'Dim Sum As Double
    NextRow = 13
     
    With Worksheets("Sheet1")
         
        .Range("A11:D250").ClearContents
        
        For Each a In Worksheets("Data").Range("A6:A" & Cells(Rows.Count, "A").End(xlUp).Row) 
            If Value <> a Then
                 
                For i = 1 To 4
                    .Cells(NextRow, i).Value = a.Offset(0, i).Value
                    .Cells(NextRow, i).Value = a.Offset(0, i).Value
                Next i
                 
                NextRow = NextRow + 1
            End If
        Next a
       
    End With
    
    Worksheets("Sheet1").Range("D10").Value = _
    Application.WorksheetFunction.Sum(myRange)
    'Worksheets("Sheet1").Range("D10").Value = _
    'Application.WorksheetFunction.Sum(Range("D13", Range("D65536").End(xlUp)))
    
    
End Sub

I am still working on it so you more then likely see other things that need fixing/changing.

Thanks

John
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
What do you get if you try this:

<font face=Tahoma>    <SPAN style="color:#00007F">Dim</SPAN> myRange <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Set</SPAN> myRange = Worksheets("Sheet1").Range("D13:D" & Cells(Rows.Count, "D").End(xlUp).Row)<br>    MsgBox myRange.Address</FONT>

I get $D$13:$D$42, which ends up summing correctly both with code and manually.

And what are you trying to do here:

<font face=Tahoma>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> a <SPAN style="color:#00007F">In</SPAN> Worksheets("Data").Range("A6:A" & Cells(Rows.Count, "A").End(xlUp).Row)<br>            <SPAN style="color:#00007F">If</SPAN> Value <> a <SPAN style="color:#00007F">Then</SPAN><br>                 </FONT>
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Smitty.
I do get D13:D42, the same as when I select it, it selects the proper range.

The macro checks in a sheet named Data for cells with an a (Marlett Font that shows a checkmark) in column A and if it has an a, it copies cells B, C, D and E over to Sheet1 starting at A13. For appearance sakes, it also includes empty rows. I just tried it without copying empty rows, which I had not tried before, and it still comes up with the wrong answer (15,181.25) so the problem is not in the empty rows by the looks of it.
Column D has two other entries in it. D1 has a string (Co name) and cell D10 the amount I am trying to get right.
I moved the

Code:
Set myRange = Worksheets("Sheet1").Range("D13:D" & Cells(Rows.Count, "D").End(xlUp).Row)

to just before

Code:
Worksheets("Sheet1").Range("D10").Value = _
    Application.WorksheetFunction.Sum(myRange)

but that did not make a difference either.

Thanks.

John
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Feel free to send me a copy of your wb and I'll try to take a look at it. Just note that if I don't get back to you it's because I'm going climbing for a week starting tomorrow and will not have my laptop. :)
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Smitty.

I e-mailed the file via your hotmail address.

Have a good rock climbing week.

Regards.

John
 

jolivanes

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
In sheet Data I have hidden rows 5 to 11 and so far it works. Don't know why though.
Thanks for your help.
John
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top