Calculating in VBA with changing criteria

[TomorrowsMan]

- I have a table in Word 2000, 6 rows, 2 columns.
- In the first column, rows 1-5 are for review criteria, and in the
second column is a dropdown box in each cell with rating values from
0-4.
- The user selects a rating from the dropdown box, then in the last
cell of the table (row 5, col 2), a formula calculate the average.

The problem is, not all of the fields are mandatory, so the number of
criteria can range from 1 to 5. Also, it is possible to get a "0.0"
rating.

I had been using this; obviously, my question involves how to make the
value of i dynamic based on the number of criteria used:

Sub QTotal()

Dim aa As Single, i As Single

aa = 0
For i = 1 To 5
aa = aa + Val(ActiveDocument.FormFields("Qt0" & i).Result)
Next i
aa = aa / 5

ActiveDocument.FormFields("QTotal").Result = _
Format(aa, "#.##")

End Sub

Thank you,

Chris

[Karen]

Hiya TomorrowsMan,

You could check each field first to see if it is empty or using another
variable (j), test for an empty field, increment 'j' and use it as your
divisor. With your current code you could try this:

Sub QTotal()

Dim aa As Single, i As Single, j As Single

aa = 0
j = 0

For i = 1 To 5

if Val(ActiveDocument.FormFields("Qt0" & i).Result) <> "" Then

aa = aa + Val(ActiveDocument.FormFields("Qt0" & i).Result)

j = j + 1
end if
Next i
aa = aa / j

ActiveDocument.FormFields("QTotal").Result = _
Format(aa, "#.##")

End Sub

Karen

"TomorrowsMan" <tomorr...@gmail.com> wrote in message
news:1158088263....@i42g2000cwa.googlegroups.com...

[Jay Freedman]

Just be careful of data types... the If statement should be

if Val(ActiveDocument.FormFields("Qt0" & i).Result) <> 0 Then

because Val returns a number, not a string.

--
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

[Karen]

Yep, should have caught that :)

 

Karen Hagerman

Faculty

University of Phoenix

kah...@email.uophx.edu

khag...@email.wintu.edu

206-309-0438 (Leave a Message)

"Jay Freedman" <jay.fr...@verizon.net> wrote in message news:%23Y4vTFq...@TK2MSFTNGP04.phx.gbl...

[Karen]

Hi TomorrowsMan,

Have to modify that suggested code given Jay's comment and your comment that
a field can be 0.00. We still have to check for no entry so.....

Sub QTotal()

Dim aa As Single, i As Single, j As Single

aa = 0
j = 0
For i = 1 To 5

if ActiveDocument.FormFields("Qt0" & i).Result <> "" Then

aa = aa + Val(ActiveDocument.FormFields("Qt0" & i).Result)
j = j + 1
end if
Next i
aa = aa / j

ActiveDocument.FormFields("QTotal").Result = _
Format(aa, "#.##")

End Sub

Karen Hagerman

Faculty
University of Phoenix
kah...@email.uophx.edu
khag...@email.wintu.edu
206-309-0438 (Leave a Message)

"Karen" <wonde...@functiy.com> wrote in message
news:OjSjGCq1...@TK2MSFTNGP05.phx.gbl...

[TomorrowsMan]

Thanks so much!