Calculating in VBA with changing criteria

T

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
 
K

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

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

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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
 
K

Karen

Yep, should have caught that :)

Karen Hagerman

Faculty

University of Phoenix

(e-mail address removed)

(e-mail address removed)

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
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.
 
K

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
(e-mail address removed)
(e-mail address removed)
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
 

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

Similar Threads

VBA to Find Key Words in Word doc then Extract Content to Excel.xlsm file 3
Cell Counter/Increment 2
Drop down list control value in vba 2
complex sort search and replace macro 2
Calculating times in excel 1
Excel Visual Basic to get current row number 1
Dealing with the error: unable to filter on excel hc home school 0
Help with "Serialize" function in query 7

Top