Computing Average

[Bishop]

I have 10 textboxes that will contain values from 1-5. Not all the textboxes
HAVE to contain a value, though. How do I write code that will consider all
10 textboxes and give me the average of only the textboxes with values in
them?

For example, say boxes 1,2,3,4,5 have values of 4,5,3,4,4 respectively. The
average should show 4. But the next time boxes 1,3,5,7,9 have values
2,4,5,3,5 respectively. The average should show 3.8.

 

[Jacob Skaria]

If the textboxes are named TextBox1, TextBox2 etc;... try something like the
below

Dim varSum As Long, varCount As Long
For intTemp = 1 To 10
If Trim(Me.Controls("Textbox" & intTemp)) <> "" Then
varSum = varSum + ("0" & Me.Controls("Textbox" & intTemp))
varCount = varCount + 1
End If
Next
MsgBox "Average :" & (varSum / varCount)


If this post helps click Yes

 

[Bishop]

I see what you're doing but my textboxes all have specific names like
PlotTextBox, PaceTextBox, ActingTextBox, StoryTextBox, etc. and there are
other text boxes in the userform besides those that won't be used in the
average calculation. How would I set it up so that just these specific
textboxes are used for the calculation?

 

[EricG]

Dim myBoxes(10) as String
'
myBoxes(1) = "PlotTextBox"
myBoxes(2) = "PaceTextBox"
myBoxes(3) = "ActingTextBox"
etc...
(whatever your textbox names are)

Then in his loop, change "Textbox" & intTemp to myBoxes(intTemp)

HTH,

Eric

 

[Bishop]

I'm doing something wrong. Here's what I have:

Dim ScoreBox(18) As String
ScoreBox(1) = "ActingTextBox"
ScoreBox(2) = "ActionTextBox"
ScoreBox(3) = "ComedyTextBox"
ScoreBox(4) = "DialogTextBox"
..
..
..

Dim varSum As Long, varCount As Long, ScoringAve As Long
Dim i As Integer
For i = 1 To 18
If Trim(Me.Controls(ScoreBox(i).Value)) <> "" Then
varSum = varSum + ("0" & Me.Controls(ScoreBox(i).Value))
varCount = varCount + 1
End If
Next
ScoringAve = (varCount / varSum)

I'm getting Invalid Qualifier on ScoreBox in the If statement.

 

[Sam Wilson]

You want scorebox(i), not scorebox(i).value

Sam

I'm doing something wrong. Here's what I have:

Dim ScoreBox(18) As String
ScoreBox(1) = "ActingTextBox"
ScoreBox(2) = "ActionTextBox"
ScoreBox(3) = "ComedyTextBox"
ScoreBox(4) = "DialogTextBox"
.
.
.

Dim varSum As Long, varCount As Long, ScoringAve As Long
Dim i As Integer
For i = 1 To 18
If Trim(Me.Controls(ScoreBox(i).Value)) <> "" Then
varSum = varSum + ("0" & Me.Controls(ScoreBox(i).Value))
varCount = varCount + 1
End If
Next
ScoringAve = (varCount / varSum)

I'm getting Invalid Qualifier on ScoreBox in the If statement.

 

[EricG]

You have a parenthesis problem in two places.

Instead of:

Me.Controls(ScoreBox(i).Value)

It should be:

Me.Controls(ScoreBox(i)).Value

 

[Bishop]

Ok, really frustrated I can't make this work. I read the help file on Trim
and I don't think I need that. I just want to get an average of the values
(integers from 1-5) in the textboxes. This is what I have now:

Dim ScoreBox(18) As String
ScoreBox(1) = "ActingTextBox"
ScoreBox(2) = "ActionTextBox"
ScoreBox(3) = "ComedyTextBox"
ScoreBox(4) = "DialogTextBox"
..
..
..

Dim varSum As Long, varCount As Long, ScoringAve As Long
Dim i As Integer
For i = 1 To 18
If ScoreBox(i).Value <> "" Then
varSum = varSum + ScoreBox(i).Value
varCount = varCount + 1
End If
Next
ScoringAve = (varSum / varCount)

I keep getting Invalid Qualifier for SccoreBox. If i = 1 then
ScoreBox(i).Value should read the same as ActingTextBox.Value, right? And if
varSum is a Long and ActingTextBox.Value is an Integer they should be
compatible, right? Why won't this work?!

 

[Sam Wilson]

scorebox(i) is a text string, not the text box object.

it should be
me.controls(scorebox(i)).value

you had
me.controls(scorebox(i).value)

which is different - scorebox(i) doesn't have any properties, so the .value
threw up the invalid qualifier error when placed inside the bracket.

 

[Bishop]

Getting Invalid Argument now. I probably should have mentioned this before
but all of this is in a With statement. I have the following code:

With Sheets("Movies")
..
..
..
Dim ScoreBox(18) As String
ScoreBox(1) = "ActingTextBox"
ScoreBox(2) = "ActionTextBox"
ScoreBox(3) = "ComedyTextBox"
ScoreBox(4) = "DialogTextBox"
..
..
..

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

I tried putting .Me but that didn't work either.

 

[Jacob Skaria]

For the text box controls for which you need to sum set the tag property to
somthing say "x". Right click on each text box to be considered>From
properties windows in Tag type 'x'. Then use the below code

Dim varSum As Long, varCount As Long
Dim objTemp As Object

For Each objTemp In Me.Controls
If objTemp.Tag = "X" Then
If Trim(objtemp.Text) <> "" Then
varSum = varSum + ("0" & Trim(objtemp.Text))
varCount = varCount + 1
End If
End If
Next
Set objTemp = Nothing

MsgBox "Average :" & (varSum / varCount)


If this post helps click Yes

 

[Bishop]

Finally! I didn't use the tag property but using .Text instead of .Value did
the trick. Thank you!