frubeng
05-19-2009, 11:25 AM
I have the following in Sheet1:
Private Sub worksheet_change(ByVal Target As Range)
Call main
End Sub
And in module 1
Sub main()
ActiveWorkbook.ActiveSheet.Cells(2, 7) = 0
sumtotal = 0
For i = 1 To Month(Now)
row2 = row2 + Row
Sum = 0
Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 2), Cells(row2 + 200, 2)), 0)
If Not IsEmpty(ActiveWorkbook.ActiveSheet.Cells(Row + row2 - 1, 1)) Then
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 1).EntireRow.Insert
Row = Row + 1
End If
'Summing all the comms for the monthly total
For j = 1 To Row - 3
Sum = Sum + ActiveWorkbook.ActiveSheet.Cells(row2 + j + 1, 4)
Next j
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 4) = Sum
sumtotal = sumtotal + Sum
Next i
ActiveWorkbook.ActiveSheet.Cells(2, 7) = sumtotal
End Sub
When I change a cell in the spreadsheet, it seems the program runs forever. When i stop it (by ctrl+break) it gives me a few error messages, and excel even crashes sometimes.
What is wrong?
(When i ink the main function to a button it works perfectly)
Thank you for your help!!
Private Sub worksheet_change(ByVal Target As Range)
Call main
End Sub
And in module 1
Sub main()
ActiveWorkbook.ActiveSheet.Cells(2, 7) = 0
sumtotal = 0
For i = 1 To Month(Now)
row2 = row2 + Row
Sum = 0
Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 2), Cells(row2 + 200, 2)), 0)
If Not IsEmpty(ActiveWorkbook.ActiveSheet.Cells(Row + row2 - 1, 1)) Then
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 1).EntireRow.Insert
Row = Row + 1
End If
'Summing all the comms for the monthly total
For j = 1 To Row - 3
Sum = Sum + ActiveWorkbook.ActiveSheet.Cells(row2 + j + 1, 4)
Next j
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 4) = Sum
sumtotal = sumtotal + Sum
Next i
ActiveWorkbook.ActiveSheet.Cells(2, 7) = sumtotal
End Sub
When I change a cell in the spreadsheet, it seems the program runs forever. When i stop it (by ctrl+break) it gives me a few error messages, and excel even crashes sometimes.
What is wrong?
(When i ink the main function to a button it works perfectly)
Thank you for your help!!