Overflow Error

[Bishop]

Ok, I've tried everything. I can't figure out why this is giving me an
overflow error. I've tried every combination I can think of. Changing dim
datatypes, forcing datatypes... nothing works. I have the following code:

Dim varSum As Integer, varCount As Integer
Dim ScoringAve As Double
Dim i As Integer
For i = 1 To 18
If Me.Controls(ScoreBox(i)).Text <> "" Then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
End If
Next

.Cells(LastRow + 1, 30) = (CLng(varSum) / CLng(varCount))

Every combination gives me Overflow errors. How do I fix this?!

 

[Joel]

Dim varSum As Integer, varCount As Integer
Dim ScoringAve As Double
Dim MyNumber as Variant
Dim i As Integer
For i = 1 To 18
If Me.Controls(ScoreBox(i)).Text <> "" Then
MyNumber = Me.Controls(ScoreBox(i)).Text
if isnumeric(MyNumber) then
varSum = varSum + Val(MyNumber)
varCount = varCount + 1
end if
End If
Next

.Cells(LastRow + 1, 30) = (CLng(varSum) / CLng(varCount))

 

[Jacob Skaria]

Since it is not a type mismatch error I hope you have numeric values in your
text boxes. So changing the type of sum variable should work..as below..

Dim varSum As Variant, intCount As Integer
Dim ScoringAve As Double
Dim i As Integer
For i = 1 To 18
If Isnumeric("0" & Trim(Me.Controls(ScoreBox(i)).Text)) Then
varSum = varSum + ("0" & Me.Controls(ScoreBox(i)).Text)
intCount = intCount + 1
End If
Next


If this post helps click Yes