Wrong answer with SUM and empty cells

[jolivanes]

If I use the following:

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:

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

 

[Smitty]

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]

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

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.

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]

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]

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

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

to just before

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

but that did not make a difference either.

Thanks.

John

 

[Smitty]

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]

Smitty.

I e-mailed the file via your hotmail address.

Have a good rock climbing week.

Regards.

John

 

[jolivanes]

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