Dynamic Loop + Dynamic range to sum

[nonono]

Thank you for reading my post, I am new to VBA and macros...
I am having difficulty trying to solve the loop and dynamic sum value

As you can see I would like to have sum the values in blue and have the total in orange but I am having trouble to sum it up at the macro since it is a dynamic range. and I would like to loop the amount of times in the red cell to repeat the sum value process where it will sum up the value in the grey cell and have the total in the gold cell next

 

[MickG]

Perhaps this ???

[COLOR="Navy"]Sub[/COLOR] MG25Jul38
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, nSum [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Set[/COLOR] Rng = Range("c:C").SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Dn
        R.Offset(, 1).Value = R.Value * R.Offset(, -1)
        nSum = nSum + R.Value * R.Offset(, -1)
        [COLOR="Navy"]If[/COLOR] R.Address = Dn(Dn.Count).Address [COLOR="Navy"]Then[/COLOR]
            R.Offset(, 2) = nSum
            nSum = 0
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] R
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[nonono]

Hory **** MICK G THANK YOU SO MUCH WTFESH
GENIUS :CHRIST thank qqqqqqqqqqqqqqqqqqqqqq

 

[nonono]

Sorry Mr Mick G,
If you may, can you explain your coding?
I would like to understand the coding better as I do not ... know how you did it

 

[footoo]

Here's code that puts formulas on the worksheet :

Sub v()
Dim Rng As Range, a As Range
Set Rng = [C:C].SpecialCells(xlCellTypeConstants)
For Each a In Rng.Areas
    Range(a(1, 2), a(a.Cells.Count, 2)).FormulaR1C1 = "=RC[-2]*RC[-1]"
    a(a.Cells.Count, 3).Formula = "=Sum(" & a(1, 2).Address & ":" & a(a.Cells.Count, 2).Address & ")"
Next
End Sub

 

[MickG]

No problem :-

[COLOR=navy]Sub[/COLOR] MG25Jul56
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, R [COLOR=navy]As[/COLOR] Range, nSum [COLOR=navy]As[/COLOR] Double
'[COLOR=green][B]set "Rng" as range object Areas[/B][/COLOR]
'[COLOR=green][B]In your specific case that is range  "C5:C9" and "C11:C14"[/B][/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range("C:C").SpecialCells(xlCellTypeConstants)
'[COLOR=green][B]Loop through those individual range Area[/B][/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng.Areas
 
 '[COLOR=green][B]Loop through the each of the Range Areas. Th first being "C5:C9"[/B][/COLOR]
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Dn
      '[COLOR=green][B]loop through each r in range("C3:C9")[/B][/COLOR]= "Dn"
      '[COLOR=green][B]for each "R" in  range "dn" multiply the r.value by
      'the cell to its left in column "B".[/B][/COLOR]
       
       '[COLOR=green][B]Then place that value in column "D"[/B][/COLOR]
        R.Offset(, 1).Value = R.Value * R.Offset(, -1)
        
        
        '[COLOR=green][B]Sum all the values in specific areas and leave the[/B][/COLOR]
        '[COLOR=green][B]result in  in last cell column "E"[/B][/COLOR]
        '[COLOR=green][B] That when :-  R.Address = Dn(Dn.Count).Address[/B][/COLOR]
        nSum = nSum + R.Value * R.Offset(, -1)
        [COLOR=navy]If[/COLOR] R.Address = Dn(Dn.Count).Address [COLOR=navy]Then[/COLOR]
            R.Offset(, 2) = nSum
            '[COLOR=green][B]set nsum to 0 and proceed to next Area.[/B][/COLOR]
            nSum = 0
        [COLOR=navy]End[/COLOR] If
    [COLOR=navy]Next[/COLOR] R
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick