|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
- 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
|
|
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[ at ]gmail.com> wrote in message
news:1158088263.759420.45110[ at ]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
|
|
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:
[Quoted Text] > 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[ at ]gmail.com> wrote in message
> news:1158088263.759420.45110[ at ]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
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_03D9_01C6D671.2D2F7050
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Yep, should have caught that :)
Karen Hagerman
Faculty
University of Phoenix
kahager[ at ]email.uophx.edu=20
khagerman[ at ]email.wintu.edu
206-309-0438 (Leave a Message)
"Jay Freedman" <jay.freedman[ at ]verizon.net> wrote in message =
news:%23Y4vTFq1GHA.2176[ at ]TK2MSFTNGP04.phx.gbl...
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.
--=20
Regards,
Jay Freedman
Microsoft Word MVP FAQ: http://word.mvps.org
Email cannot be acknowledged; please post all follow-ups to the =
newsgroup so=20
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 =3D 0
> j =3D 0
> For i =3D 1 To 5
> if Val(ActiveDocument.FormFields("Qt0" & i).Result) <> "" Then
> aa =3D aa + Val(ActiveDocument.FormFields("Qt0" & i).Result)
> j =3D j + 1
> end if
> Next i
> aa =3D aa / j
>
> ActiveDocument.FormFields("QTotal").Result =3D _
> Format(aa, "#.##")
>
> End Sub
>
>
> Karen
>
> "TomorrowsMan" <tomorrowsman[ at ]gmail.com> wrote in message
> news:1158088263.759420.45110[ at ]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 =3D 0
> For i =3D 1 To 5
> aa =3D aa + Val(ActiveDocument.FormFields("Qt0" & i).Result)
> Next i
> aa =3D aa / 5
>
> ActiveDocument.FormFields("QTotal").Result =3D _
> Format(aa, "#.##")
>
> End Sub
>
> Thank you,
>
> Chris=20
------=_NextPart_000_03D9_01C6D671.2D2F7050
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DVerdana>Yep, should have caught that :)</FONT></DIV>
<DIV> </DIV>
<DIV>
<DIV>
<DIV><STRONG><SPAN style=3D"FONT-WEIGHT: normal; FONT-FAMILY: Verdana">
<DIV>
<DIV class=3DSection1>
<P style=3D"MARGIN: 0in 0in 0pt"><FONT color=3D#008000>Karen =
Hagerman</FONT></P>
<P style=3D"MARGIN: 0in 0in 0pt"><FONT =
color=3D#008000>Faculty</FONT></P>
<P style=3D"MARGIN: 0in 0in 0pt"><FONT color=3D#008000>University of=20
Phoenix</FONT></P>
<P style=3D"MARGIN: 0in 0in 0pt"><A=20
href=3D"mailto:kahager[ at ]email.uophx.edu">kahager[ at ]email.uophx.edu</A> </P>
<P style=3D"MARGIN: 0in 0in 0pt"><U><FONT color=3D#0000ff><A=20
href=3D"mailto:khagerman[ at ]email.wintu.edu">khagerman[ at ]email.wintu.edu</A></=
FONT></U></P>
<P style=3D"MARGIN: 0in 0in 0pt">206-309-0438 (Leave a=20
Message)</P></DIV></DIV></SPAN></STRONG></DIV></DIV></DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Jay Freedman" <<A=20
=
href=3D"mailto:jay.freedman[ at ]verizon.net">jay.freedman[ at ]verizon.net</A>>=
wrote=20
in message <A=20
=
href=3D"news:%23Y4vTFq1GHA.2176[ at ]TK2MSFTNGP04.phx.gbl">news:%23Y4vTFq1GHA.=
2176[ at ]TK2MSFTNGP04.phx.gbl</A>...</DIV>Just=20
be careful of data types... the If statement should =
be<BR><BR> =20
if Val(ActiveDocument.FormFields("Qt0" & i).Result) <> =
0=20
Then<BR><BR>because Val returns a number, not a string.<BR><BR>--=20
<BR>Regards,<BR>Jay Freedman<BR>Microsoft Word=20
MVP FAQ: <A=20
href=3D"http://word.mvps.org">http://word.mvps.org</A><BR>Email cannot =
be=20
acknowledged; please post all follow-ups to the newsgroup so <BR>all =
may=20
benefit.<BR><BR>Karen wrote:<BR>> Hiya =
TomorrowsMan,<BR>><BR>> You=20
could check each field first to see if it is empty or using<BR>> =
another=20
variable (j), test for an empty field, increment 'j' and use<BR>> =
it as=20
your divisor. With your current code you could try =
this:<BR>><BR>>=20
Sub QTotal()<BR>><BR>> Dim aa As Single, i As Single, j As=20
Single<BR>><BR>> aa =3D 0<BR>> j =3D 0<BR>> For i =3D 1 To =
5<BR>> if =
Val(ActiveDocument.FormFields("Qt0" &=20
i).Result) <> "" =
Then<BR>> =20
aa =3D aa + Val(ActiveDocument.FormFields("Qt0" &=20
i).Result)<BR>> j =3D j + =
1<BR>> end if<BR>> Next i<BR>> aa =3D aa /=20
j<BR>><BR>> ActiveDocument.FormFields("QTotal").Result =3D =
_<BR>>=20
Format(aa, "#.##")<BR>><BR>> End Sub<BR>><BR>><BR>>=20
Karen<BR>><BR>> "TomorrowsMan" <<A=20
href=3D"mailto:tomorrowsman[ at ]gmail.com">tomorrowsman[ at ]gmail.com</A>> =
wrote in=20
message<BR>> <A=20
=
href=3D"news:1158088263.759420.45110[ at ]i42g2000cwa.googlegroups.com">news:1=
158088263.759420.45110[ at ]i42g2000cwa.googlegroups.com</A>...<BR>>=20
- I have a table in Word 2000, 6 rows, 2 columns.<BR>> - In the =
first=20
column, rows 1-5 are for review criteria, and in the<BR>> second =
column is=20
a dropdown box in each cell with rating values from<BR>> =
0-4.<BR>> - The=20
user selects a rating from the dropdown box, then in the last<BR>> =
cell of=20
the table (row 5, col 2), a formula calculate the =
average.<BR>><BR>> The=20
problem is, not all of the fields are mandatory, so the number =
of<BR>>=20
criteria can range from 1 to 5. Also, it is possible to get a=20
"0.0"<BR>> rating.<BR>><BR>> I had been using this; =
obviously, my=20
question involves how to make the<BR>> value of i dynamic based on =
the=20
number of criteria used:<BR>><BR>> Sub QTotal()<BR>><BR>> =
Dim aa=20
As Single, i As Single<BR>><BR>> aa =3D 0<BR>> For i =3D 1 To =
5<BR>>=20
aa =3D aa + Val(ActiveDocument.FormFields("Qt0" & =
i).Result)<BR>> Next=20
i<BR>> aa =3D aa / 5<BR>><BR>>=20
ActiveDocument.FormFields("QTotal").Result =3D _<BR>> Format(aa,=20
"#.##")<BR>><BR>> End Sub<BR>><BR>> Thank =
you,<BR>><BR>>=20
Chris <BR><BR></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_03D9_01C6D671.2D2F7050--
|
|
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[ at ]email.uophx.edu
khagerman[ at ]email.wintu.edu
206-309-0438 (Leave a Message)
"Karen" <wonderlover[ at ]functiy.com> wrote in message
news:OjSjGCq1GHA.1304[ at ]TK2MSFTNGP05.phx.gbl...
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[ at ]gmail.com> wrote in message
news:1158088263.759420.45110[ at ]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
|
|
|
|
|