Computing Average

B

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.
 
J

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
 
B

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?
 
E

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
 
B

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.
 
S

Sam Wilson

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

Sam

Bishop said:
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.
 
E

EricG

You have a parenthesis problem in two places.

Instead of:

Me.Controls(ScoreBox(i).Value)

It should be:

Me.Controls(ScoreBox(i)).Value
 
B

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?!
 
S

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.
 
B

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.
 
J

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
 
B

Bishop

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top