Help with a Macro

P

Poli

Please Help me I am writing a Macro using Word 2007 and Windows XP so that it
looks at differnt FormFields and gives me a Mininum and Maximum value into
another field. It give me error though. Here it is:

Sub GetMinMaxValueA()

Dim EntryFlag As Boolean
EntryFlag = False

varMAX_A = 0
varMIN_A = 9999.99

If Application.ActiveDocument.Fields("FHZ_A").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_A").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_A").Data
End If
If Application.ActiveDocument.Fields("FHZ_A").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_A").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_E").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_E").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_E").Data
End If
If Application.ActiveDocument.Fields("FHZ_E").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_E").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_I").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_I").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_I").Data
End If
If Application.ActiveDocument.Fields("FHZ_I").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_I").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_M").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_M").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_M").Data
End If
If Application.ActiveDocument.Fields("FHZ_M").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_M").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_Q").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_Q").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_Q").Data
End If
If Application.ActiveDocument.Fields("FHZ_Q").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_Q").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_U").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_U").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_U").Data
End If
If Application.ActiveDocument.Fields("FHZ_U").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_U").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_Y").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_Y").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_Y").Data
End If
If Application.ActiveDocument.Fields("FHZ_Y").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_Y").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_AC").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_AC").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_AC").Data
End If
If Application.ActiveDocument.Fields("FHZ_AC").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_AC").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_AG").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_AG").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_AG").Data
End If
If Application.ActiveDocument.Fields("FHZ_AG").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_AG").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_AK").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_AK").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_AK").Data
End If
If Application.ActiveDocument.Fields("FHZ_AK").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_AK").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_AO").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_AO").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_AO").Data
End If
If Application.ActiveDocument.Fields("FHZ_AO").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_AO").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_AS").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_AS").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_AS").Data
End If
If Application.ActiveDocument.Fields("FHZ_AS").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_AS").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_AW").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_AW").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_AW").Data
End If
If Application.ActiveDocument.Fields("FHZ_AW").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_AW").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_BA").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_BA").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_BA").Data
End If
If Application.ActiveDocument.Fields("FHZ_BA").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_BA").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_BE").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_BE").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_BE").Data
End If
If Application.ActiveDocument.Fields("FHZ_BE").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_BE").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_BI").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_BI").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_BI").Data
End If
If Application.ActiveDocument.Fields("FHZ_BI").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_BI").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_BM").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_BM").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_BM").Data
End If
If Application.ActiveDocument.Fields("FHZ_BM").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_BM").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_BQ").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_BQ").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_BQ").Data
End If
If Application.ActiveDocument.Fields("FHZ_BQ").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_BQ").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_BU").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_BU").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_BU").Data
End If
If Application.ActiveDocument.Fields("FHZ_BU").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_BU").Data
End If
EntryFlag = True
End If


If Application.ActiveDocument.Fields("FHZ_BY").Data <> 0 Then
If Application.ActiveDocument.Fields("FHZ_BY").Data > varMAX_A Then
varMAX_A = Application.ActiveDocument.Fields("FHZ_BY").Data
End If
If Application.ActiveDocument.Fields("FHZ_BY").Data < varMIN_A Then
varMIN_A = Application.ActiveDocument.Fields("FHZ_BY").Data
End If
EntryFlag = True
End If

If EntryFlag = False Then
Application.ActiveDocument.Fields("TOTMAX_A").Data = ""
Else
Application.ActiveDocument.Fields("TOTMAX_A").Data = varMAX_A
End If


If EntryFlag = False Then
Application.ActiveDocument.Fields("TOTMIN_A").Data = ""
Else
Application.ActiveDocument.Fields("TOTMIN_A").Data = varMIN_A
End If


EntryFlag = False

End Sub
 
G

Graham Mayor

If you are working with form fields your syntax is wrong. Try the following.
Note for your first line to work the form field must be a number type with a
default of 0. If it is an emptry text form field you would use
If ActiveDocument.FormFields("FHZ_A").Result <> "" Then



If ActiveDocument.FormFields("FHZ_A").Result <> 0 Then
If Application.ActiveDocument.FormFields("FHZ_A").Result > varMAX_A Then
varMAX_A = ActiveDocument.FormFields("FHZ_A").Result
End If
If ActiveDocument.FormFields("FHZ_A").Result < varMIN_A Then
varMIN_A = ActiveDocument.FormFields("FHZ_A").Result
End If
EntryFlag = True
End If
'*************************
'etc
'**************************

If EntryFlag = False Then
ActiveDocument.FormFields("TOTMAX_A").Result = ""
Else
ActiveDocument.FormFields("TOTMAX_A").Result = varMAX_A
End If


If EntryFlag = False Then
ActiveDocument.FormFields("TOTMIN_A").Result = ""
Else
ActiveDocument.FormFields("TOTMIN_A").Result = varMIN_A
End If


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 
M

macropod

Or perhaps:

Sub GetMinMaxValue()
varMAX_A = 0
varMIN_A = 9999.99
Dim i as Integer
Dim strFlds As String
Dim FldVal As String
Dim Pwd As String
Pwd = "" ' Insert your form password here
strFlds = "FHZ_A,FHZ_E,FHZ_I,FHZ_M,FHZ_Q,FHZ_U,FHZ_Y,FHZ_AC,FHZ_AG,FHZ_AK,FHZ_AO,"
strFlds = strFlds & "FHZ_AS,FHZ_AW,FHZ_BA,FHZ_BE,FHZ_BI,FHZ_BM,FHZ_BQ,FHZ_BU,FHZ_BY"
With ActiveDocument
.Unprotect Password:=Pwd
For i = 1 To UBound(Split(strFlds, ","))
FldVal = .Fields(Split(strFlds, ",")(i)).Result
If FldVal <> 0 Then
If FldVal > varMAX_A Then varMAX_A = FldVal
If FldVal < varMIN_A Then varMIN_A = FldVal
End If
Next
If varMAX_A = 0 And varMIN_A = 9999.99 Then
.Fields("TOTMAX_A").Result = ""
.Fields("TOTMIN_A").Result = ""
Else
.Fields("TOTMAX_A").Result = varMAX_A
.Fields("TOTMIN_A").Result = varMIN_A
End If
.Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd
End With
End Sub
 
G

Graham Mayor

I think you'll find that with form fields this results in the same syntax
issues as the original. Nor should it be necessary to unlock the form to run
this bit of code.

Sub GetMinMaxValue()
varMAX_A = 0
varMIN_A = 9999.99
Dim i As Integer
Dim strFlds As String
Dim FldVal As String
strFlds =
"FHZ_A,FHZ_E,FHZ_I,FHZ_M,FHZ_Q,FHZ_U,FHZ_Y,FHZ_AC,FHZ_AG,FHZ_AK,FHZ_AO,"
strFlds = strFlds &
"FHZ_AS,FHZ_AW,FHZ_BA,FHZ_BE,FHZ_BI,FHZ_BM,FHZ_BQ,FHZ_BU,FHZ_BY"
With ActiveDocument
For i = 1 To UBound(Split(strFlds, ","))
FldVal = .FormFields(Split(strFlds, ",")(i)).Result
If FldVal <> 0 Then
If FldVal > varMAX_A Then varMAX_A = FldVal
If FldVal < varMIN_A Then varMIN_A = FldVal
End If
Next
If varMAX_A = 0 And varMIN_A = 9999.99 Then
.FormFields("TOTMAX_A").Result = ""
.FormFields("TOTMIN_A").Result = ""
Else
.FormFields("TOTMAX_A").Result = varMAX_A
.FormFields("TOTMIN_A").Result = varMIN_A
End If
End With
End Sub

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
 

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

Lookup Macro 0
VBA Macro - Copying specific data to different sheets in a workbookand linking the InputBox to a com 7
Saving a Word document as PDF from en Excel macro 2
Help with this macro 6
Macro change - help please 3
Help with Code 8
Creating Pivot Table with Macro using Multiple Sheets 0
help with restricting paste in cells with data validation 1

Top