sum the totals amount and Partial amounts

[jakjo]

Hello everyone
The task I am trying to accomplish is to sum the totals amount and Partial amounts In specific cells
I tried to complete my idea but could not do it .... for illustration
This code to transpose the last row from horizontal to vertical ( final total row - The row is not fixed )
from the two sheets cash sales & Deferred Sales to the two sheets Total cash sales & Total Deferred Sales.

Code: Select all

Option Explicit
   Sub TransposeTotals()
   Dim sourceRange As Range
   Dim sourceCell As Range
   Dim targetCell As Range
   Dim lastRow As Long
   Dim dValue
   Dim i As Long
   Application.ScreenUpdating = False

    For i = 1 To 8
    Select Case i
        Case 1
            With Sheets("cash sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("B1:AC1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total cash sales").Range("A8")
        
        Case 2
            With Sheets("cash sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("AI1:AN1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total cash sales").Range("G8")
        
        Case 3
            With Sheets("cash sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("AP1:AR1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total cash sales").Range("G15")
        
                Case 4
            With Sheets("cash sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("AV1:BW1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total cash sales").Range("G21")
        
                
                
                Case 5
            With Sheets("Deferred Sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("B1:AC1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total Deferred Sales").Range("A8")
        
        Case 6
            With Sheets("Deferred Sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("AI1:AN1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total Deferred Sales").Range("G8")
        
        Case 7
            With Sheets("Deferred Sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("AP1:AR1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total Deferred Sales").Range("G15")
        
                Case 8
            With Sheets("Deferred Sales")
                lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                Set sourceRange = .Range("AV1:BW1").Offset(lastRow - 1, 0)
            End With
            Set targetCell = Sheets("Total Deferred Sales").Range("G21")

    End Select

    For Each sourceCell In sourceRange
        If sourceCell.Value = "" Then
            targetCell.Resize(1, 2).Value = ""
        
        Else
            dValue = Round(sourceCell.Value, 2)
            targetCell.Value = Round((dValue - Int(dValue)) * 100, 2)
            
            
           targetCell.Offset(0, 1).Value = Int(dValue)
           End If
           Set targetCell = targetCell.Offset(1, 0)
           Next sourceCell
           Next i
           Application.ScreenUpdating = True
   End Sub

My question is : How can some lines is included with this code referred to above to sum the totals amount and Partial amounts
and Also the net amount as shown in the two sheets ( Total cash sales & Total Deferred Sales )
If you click on the button, you'll see what I mean ... Please see the yellow cells ... Thank you for any help on this in advance.

[HansV]

Can you explain your question in more detail? I don't understand what the problem is.

[jakjo]

Thanks for your reply Mr. Hans
There is no problem so far ... The code works perfectly
but I need to Include some lines from inside the code to sum the totals amount and Partial amounts
and Also the net amount as shown in the two sheets Total cash sales & Total Deferred Sales.
I have put the desired output in yellow cells manually
As for the colors used, they are for illustrate the required summation ranges in the yellow cells
Is there any additional lines should add to achieve that?.. Thanks again.

[HansV]

There is no need to do that in the code. Although the numbers will vary, the calculations remain the same, so you can use formulas in the yellow cells.
See the attached version.

Totals.xlsm

[jakjo]

Of course I know that very well using formulas but I wanted to achieve this using vba.
Anyway thank you very much Dear Hans and I welcome any other solutions in this point if possible.
Best Regards

[HansV]

What's the point of using vba to do this?

[jakjo]

Thanks a lot Mr. Hans, In fact this is a test I managed to solve it by 90%
but I'm having trouble getting it to the exact point to have it completely solved without using any formulas.
I've spent hours searching to achieve this ... but no joy as yet ... I don't know if this is possible or not? ...Thanks again.

[HansV]

Of course it's possible, but it makes no sense at all. VBA is the best solution for some problems, and formulas are the best solution for other problems. In this example, formulas are far more efficient than VBA code. Using VBA for the sums would only slow down performance.

[jakjo]

you are right Mr. Hans, but in the end it's just a test
It may be success or failure ... could you give me an example and I will try apply it on my case on my own ... Thanks again

[HansV]

You can use code to enter the formula in each of the cells. It shouldn't be hard to figure that out - you already know the formulas.

[jakjo]

This issue seems easy .... but it's still a bit blurry to me and can no longer brain so I would be grateful if you gave me starting point for the issue.
thank you very much for your time and effort. I am really appreciating that.