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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

put a break on the line and check the values of the variables.

"Bishop" wrote in message
...
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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

I don't get the error when I step through it. Using random sample data I
ended up with:

30/9=3.3333333333333

another random sample yielded:

27/11=2.454545454545

Do I need to restrict the output? I will be assigning the value to a label
caption and will only need it accurate to one decimal place.



"Patrick Molloy" wrote:

put a break on the line and check the values of the variables.

"Bishop" wrote in message
...
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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

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))

How do I fix this?!

"Patrick Molloy" wrote:

put a break on the line and check the values of the variables.

"Bishop" wrote in message
...
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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

Could it be because varCount is 0?

And could that have something to do with ScoreBox(i) ?

Is that an array or are you trying to refer to controls on a userform
or worksheet?

On Jun 25, 7:34*pm, Bishop wrote:
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
* * ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
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
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.



Bishop wrote:

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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

--

Dave Peterson

The only thing going in the textboxes are integers 1-5 (a rating like 5 stars
kind of thing)

"Dave Peterson" wrote:

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
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
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.



Bishop wrote:

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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

--

Dave Peterson

I think the problem is when all of the textboxes are empty. Your suggestion
works... but only if at least one of the textboxes has a value in it.

"Dave Peterson" wrote:

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
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
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.



Bishop wrote:

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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

--

Dave Peterson

I'd check to see if the values in the textboxes are really numeric:

If Me.Controls(ScoreBox(i)).Text < "" Then
if isnumeric(me.controls(Scorebox(i)).text then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
end if
End If

It could stop other typos, too.

Bishop wrote:

I think the problem is when all of the textboxes are empty. Your suggestion
works... but only if at least one of the textboxes has a value in it.

"Dave Peterson" wrote:

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
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
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.



Bishop wrote:

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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

--

Dave Peterson


--

Dave Peterson

In fact...

If Me.Controls(ScoreBox(i)).Text < "" Then
if isnumeric(me.controls(Scorebox(i)).text then
varSum = varSum + cdbl(Me.Controls(ScoreBox(i)).Text)
varCount = varCount + 1
end if
End If

Dave Peterson wrote:

I'd check to see if the values in the textboxes are really numeric:

If Me.Controls(ScoreBox(i)).Text < "" Then
if isnumeric(me.controls(Scorebox(i)).text then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
end if
End If

It could stop other typos, too.

Bishop wrote:

I think the problem is when all of the textboxes are empty. Your suggestion
works... but only if at least one of the textboxes has a value in it.

"Dave Peterson" wrote:

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
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
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.



Bishop wrote:

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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

And adding Keiji's response:

if varcount = 0 then
'what should happen to scoringave
else
ScoringAve = varSum) / varCount
end if

I would keep the tests for numbers, too.

Dave Peterson wrote:

In fact...

If Me.Controls(ScoreBox(i)).Text < "" Then
if isnumeric(me.controls(Scorebox(i)).text then
varSum = varSum + cdbl(Me.Controls(ScoreBox(i)).Text)
varCount = varCount + 1
end if
End If

Dave Peterson wrote:

I'd check to see if the values in the textboxes are really numeric:

If Me.Controls(ScoreBox(i)).Text < "" Then
if isnumeric(me.controls(Scorebox(i)).text then
varSum = varSum + Me.Controls(ScoreBox(i)).Text
varCount = varCount + 1
end if
End If

It could stop other typos, too.

Bishop wrote:

I think the problem is when all of the textboxes are empty. Your suggestion
works... but only if at least one of the textboxes has a value in it.

"Dave Peterson" wrote:

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
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
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.



Bishop wrote:

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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?

--

Dave Peterson


--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

I might be wrong, but when all of the textboxes are emtpty, varCount
become 0. so, ScoringAve = (varSum / varCount) is equivalent to
ScoringAve = (varSum / 0 ). the result will end up with Overflow error.

Keiji

Bishop wrote:
I think the problem is when all of the textboxes are empty. Your suggestion
works... but only if at least one of the textboxes has a value in it.

"Dave Peterson" wrote:

First, I would never use "as Integer". I'd always use "as Long". Same with "As
Single". I'd use "As Double".

Second, you never posted what was in those 18 textboxes. You could add:
Debug.print "i=" & i & " -- " & Me.Controls(ScoreBox(i)).Text
And copy from the immediate window and then paste into any followup message.

So without knowing what's in those textboxes, I'd suggest:

Dim varSum As Long, varCount As Long
Dim ScoringAve As Double
Dim i As Long
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
ScoringAve = cdbl(varSum) / cdbl(varCount)

But I didn't test any of it. I'm sure my values wouldn't match what you're
seeing.



Bishop wrote:
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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?
--

Dave Peterson

Things to check:

1. You are using the ".Text" attribute of your controls. Should you be
using ".Value" instead? What type of controls are they?
2. Put in a check for varCount = 0 before the line where you calculate
ScoringAve. Something like "If (varCount = 0) Then msgBox "OMG! varCount =
0!"
3. You're doing integer math all the way until the end of the calculation
for ScoringAve. Perhaps try converting the Integers to Doubles first, i.e.
cDbl(varSum) / cDbl(varCount)
4. Check to see if your controls have a Null value in them - that can screw
up most calculations.

HTH,

Eric

"Bishop" wrote:

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
ScoringAve = (varSum / varCount)

Why am I getting an Overflow error for ScoringAve?