CODEWITHSUNDEEP
vbaruntime-error
baconbacon
baconbacon

Reputation: 27

visual basic error with trying to calculate average

I am trying to create a macro that gathers both a flat # count, and an average of existing values in a sheet. I'm getting an error at the line where i try to calculate the avgScore variable by dividing my summed up vulnScore by the counter value. I'm getting a 'runtime 6 error' at the 'avgScore = vulnScore / counter' line. It's working with a data set thats 77 lines long. I've tried converting the variables to Long (the only real suggestion I've found) but that has not worked. the 'element' variable is for an array thats about 26 in size containing string variables.

Dim counter As Long
Dim vulnScore As Long
Dim avgScore As Long
vulnScore = 0
counter = 0
avgScore = 0

Dim rowNum As Integer
''''''''''''''''''''
For Each element In arr
vulnScore = 0
counter = 0
avgScore = 0
'get #'s for putting into Treg
'''''''''
For i = 2 To Cells(Rows.Count, 3).End(xlUp).Row
If Cells(i, 3).Value = element Then
'# of times that server is listed
counter = counter + 1
'combining vulnerability scores for that server
vulnScore = vulnScore + Cells(i, 7).Value
End If
Next i
avgScore = vulnScore / counter
'''''''''

'find Treg column/row
colNum = WorksheetFunction.Match("Findings Variance (4/15 vs Current)", ActiveWorkbook.TREG.Range("1:1"), 0)
rowNum = WorksheetFunction.Match(element, ActiveWorkbook.TREG.Range("1:1"), 0)
Cells(rowNum, colNum - 1).Value = avgScore
Cells(rowNum, colNum - 2).Value = counter
'''''''''
Next element

Sample: it's pulling data from this sheet which would be the activeSheet when the macro is run enter image description here

Sample: it's pasting the data into the two blank columns in this sheet (TREG) enter image description here

Upvotes: 0

Views: 110

Answers (2)

FunThomas
FunThomas

Reputation: 29612

You state in your comment to NautMeg's answer that both variables are 0 when it happens. That already gives you half of the answer: If counter is 0, you will get an divide by zero error (runtime error 11). With the exception of dividing 0 by 0, in that case VBA will not throw an divide by zero, it will throw an overflow error (runtime error 6).

You can try it by opening the immediate window (Ctrl+G) and enter:

? 1/0

-->Runtime error 11

? 0/0

-->Runtime error 6

In any case, you have to make up your mind what you want to see in your sheet. Maybe change your code to something like

colNum = WorksheetFunction.Match("Findings Variance (4/15 vs Current)", ActiveWorkbook.TREG.Range("1:1"), 0)
rowNum = WorksheetFunction.Match(element, ActiveWorkbook.TREG.Range("1:1"), 0)
if counter > 0 then 
    avgScore = vulnScore / counter        
    Cells(rowNum, colNum - 1).Value = avgScore
Else
    Cells(rowNum, colNum - 1).Value = ""
End IF
Cells(rowNum, colNum - 2).Value = counter

Upvotes: 0

NautMeg
NautMeg

Reputation: 141

try adding this code:

debug.print("Vulnscore: " & vulnScore & " Counter: " & counter & " avgScore: " & vulnScore / counter)

right before your line

avgScore = vulnScore / counter

And have a look at what get's printed

Upvotes: 1

Related Questions