SUM stored as variable?

[NinaE_11]

Hello!
I'm attempting to sum a column of values using the following code: ws.Range("G" & lastrow).Offset(2).Value = Application.Sum(Range("G7:G" & lastrow)) which seems to work as I expect. However, I am now looking to take the calculated value in this previous formula and use it in a new formula in Column H.

All of the constituent values in Column G, need to be summed, then divided by the sum to find the % of each constituent. In Excel terms "H7 =G7 / $G$100"

So to accomplish this, I believe I need to store the sum value in Column G as a variable to use in Column H formula - but I'm not sure how/when to declare it, and unclear how to incorporate it into Column H Code.

I hope I was able to demonstrate this properly. Any help would be appreciated - thank you!

 

[iggydarsa]

would this help?

For i = 7 To lastrow
    ws.Range("H" & i).Value = ws.Range("G" & i).Value / ws.Range("G" & lastrow).Offset(2).Value
Next i

 

[DanteAmor]

Try:

  Dim tot As Double
  tot = Application.Sum(Range("G7:G" & lastrow))
  ws.Range("G" & lastrow).Offset(2).Value = tot
  
  With ws.Range("H7:H" & lastrow)
    .Formula = "=G7/" & tot
    .Value = .Value
  End With

 

[NinaE_11]

Try:

  Dim tot As Double
  tot = Application.Sum(Range("G7:G" & lastrow))
  ws.Range("G" & lastrow).Offset(2).Value = tot
 
  With ws.Range("H7:H" & lastrow)
    .Formula = "=G7/" & tot
    .Value = .Value
  End With

Thank you so very much! This worked perfectly.