Forum Discussion

Excel's avatar
Excel
Iron Contributor
Jun 26, 2022
Solved

Loop in Sum with the help of VBA code

Hello Everyone,    When we run the loop multiple time, its adding up and filling the next cells down. what function to be given to to get the sum value which sticks to single cell ?   Please help...
Macros and VBA
  • OliverScheurich's avatar
    OliverScheurich
    Jun 26, 2022

    Excel 

    Sub AutomateSum()
    
    Dim lastrow As String
    
    Dim ws As Worksheet
    
    For Each ws In Worksheets
    
    Worksheets(ws.Name).Select
    
    Range("E2").Select
    
    Selection.End(xlDown).Select
    
    lastrow = ActiveCell.Row
    
    ActiveCell.Offset(1, 1).Select
    
    ActiveCell.Value = "=sum(F2:F" & lastrow & ")"
    
    Next ws

End Sub

    You can try the attached file with these lines of code.

     

     

OliverScheurich's avatar
OliverScheurich
Gold Contributor
Jun 26, 2022

Excel 

Sub AutomateSum()
    
    Dim lastrow As String
    
    Dim ws As Worksheet
    
    For Each ws In Worksheets
    
    Worksheets(ws.Name).Select
    
    Range("E2").Select
    
    Selection.End(xlDown).Select
    
    lastrow = ActiveCell.Row
    
    ActiveCell.Offset(1, 1).Select
    
    ActiveCell.Value = "=sum(F2:F" & lastrow & ")"
    
    Next ws

End Sub

You can try the attached file with these lines of code.

 

 

Excel's avatar
Excel
Iron Contributor
Jun 26, 2022
It work...

Thank you so much sir...

Resources