Calculating in VBA with changing criteria

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TomorrowsMan - 12 Sep 2006 20:11 GMT

- 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

Reply to this Message

Karen - 12 Sep 2006 20:20 GMT

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

- 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

Reply to this Message

Jay Freedman - 12 Sep 2006 20:26 GMT

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.

Signature

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.

> Hiya TomorrowsMan,
>
[quoted text clipped - 55 lines]
>
> Chris

Reply to this Message

Karen - 12 Sep 2006 20:41 GMT

Yep, should have caught that :)

Karen Hagerman

Faculty

University of Phoenix

kahager@email.uophx.edu

khagerman@email.wintu.edu

206-309-0438 (Leave a Message)

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 wrote:
> 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" <tomorrowsman@gmail.com> wrote in message
> news:1158088263.759420.45110@i42g2000cwa.googlegroups.com...
> - 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

Reply to this Message

Karen - 12 Sep 2006 21:23 GMT

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
kahager@email.uophx.edu
khagerman@email.wintu.edu
206-309-0438 (Leave a Message)
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

- 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

Reply to this Message

TomorrowsMan - 21 Sep 2006 15:46 GMT

Thanks so much!

Reply to this Message

Rate this thread: