Auto Calculate Macros when worksheet data updated or changes in multiple cells

[Chenj]

Hi,

I would appreciate assistance with my workbook. I am an extreme novice with Excel Macros and VBA. I developed a tool for use in my work as a compliance officer.

My brother-in-law who is a programmer assisted with developing the macros for the workbook, but wasnt too keen on assisting me with completing it. He last used Macros and VBA many moons ago. Anyway, I have been trying to read around for solutions, without much luck.

The ultimate aim is to have the macros contained in the QA Scoring worksheet, run when the data from the consultant worksheets 1 - 20 is updated. The scores from the consultant worksheets update the cells in column F18 through to F197 contained in the QA Scoring worksheet are meant to automatically update the macros contained in cells Q2 to Q7 and K7 (Agents in Selection) of the QA Scoring worksheet. Alternatively, all macros would be updated after all data has been inputted and a calculate button is clicked.

The aim is for clients to use the tool for QA scoring and not have to run each and every macro to update the QA Scoring sheet, thus their input will be limited to inputting the raw data in consultant worksheets 1 to 20 and clicking on the calculate button (which I have no idea how to develop) in cell K10.

Below is the code that was used.

Sub OpeningDisclosures()
'
' Opening Disclosure
'

Dim Total As Double
Dim i As Integer

For i = 18 To 189 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(2, 17) = Total
End Sub
Sub InformationGiven()
'
' InformationGiven Macro
'
Dim Total As Double
Dim i As Integer

For i = 19 To 190 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(3, 17) = Total
End Sub

Sub BenefitsandCovers()
'
' BenefitsandCovers Macro
'
Dim Total As Double
Dim i As Integer

For i = 20 To 191 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(4, 17) = Total
End Sub
Sub ConditionsandExclusions()
'
' ConditionsandExclusions Macro
'
Dim Total As Double
Dim i As Integer

For i = 21 To 192 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(5, 17) = Total
End Sub
Sub PolicyDocumentandCoolingOff()
'
' PolicyDocumentandCoolingOff Macro
'
Dim Total As Double
Dim i As Integer

For i = 22 To 193 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(6, 17) = Total
End Sub
Sub FinancialNeedsAnalysis()
'
' FinancialNeedsAnalysis Macro
'
Dim Total As Double
Dim i As Integer

For i = 23 To 194 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(7, 17) = Total
End Sub
Sub Termination()
'
' Termination Macro
'
Dim Total As Double
Dim i As Integer

For i = 24 To 195 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(8, 17) = Total
End Sub
Sub Closings()
'
' Closings Macro
'
Dim Total As Double
Dim i As Integer

For i = 25 To 196 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(9, 17) = Total
End Sub
Sub DeviationfromScript()
'
' DeviationfromScript Macro
'
Dim Total As Double
Dim i As Integer

For i = 26 To 197 Step 9
Total = Total + Sheet2.Cells(i, 6)
Next i

Sheet2.Cells(10, 17) = Total
End Sub
Sub AgentsinSelection()
'
' AgentsinSelection Macro
'
Dim Count As Integer
Dim i As Integer

For i = 18 To 189 Step 9

If Sheet2.Cells(i, 6) > 0 Then
Count = Count + 1
End If

Next i

Sheet2.Cells(7, 11) = Count

End Sub

Now these are the various attempts at auto calculating.

Private Sub Workbook_Open()

End Sub



Sub Calculate()
'
' Calculate Macro
' Final Calculation of all Macro's
'

'
Application.Run "'Direct Marketing QA Scoring Tool.xlsm'!AgentsinSelection"
Application.Run "'Direct Marketing QA Scoring Tool.xlsm'!BenefitsandCovers"
Application.Run "'Direct Marketing QA Scoring Tool.xlsm'!Closings"
Application.Run _
"'Direct Marketing QA Scoring Tool.xlsm'!ConditionsandExclusions"
Application.Run "'Direct Marketing QA Scoring Tool.xlsm'!DeviationfromScript"
Application.Run _
"'Direct Marketing QA Scoring Tool.xlsm'!FinancialNeedsAnalysis"
Application.Run _
"'Direct Marketing QA Scoring Tool.xlsm'!PolicyDocumentandCoolingOff"
Application.Run "'Direct Marketing QA Scoring Tool.xlsm'!InformationGiven"
Application.Run "'Direct Marketing QA Scoring Tool.xlsm'!Termination"
End Sub



Anyone who could assist me with this, it would be most appreciated.

Chenj.

[AlphaFrog]

Put this formula in Sheet2 cell P2 and copy\drag down to P10
=SUM(F18,F27,F36,F45,F54,F63,F72,F81,F90,F99,F108,F117,F126,F135,F144,F153,F162,F171,F180,F189)

Put this in cell L7
=COUNT(F18,F27,F36,F45,F54,F63,F72,F81,F90,F99,F108,F117,F126,F135,F144,F153,F162,F171,F180,F189)

They would update as the data updates.

[fredlo2010]

Hi,

You can use "Call" Statement to run the different part of the code. Also the code looks a little complicated and repetitive. I would say it can be optimized but its hard to tell without the file.

And last please use the # tags around your text to improve readability.

Please Login or Register  to view this content.