locked
Form Filed Calculation RRS feed

  • Question

  • Hi Everyone,

    I would like to create a Macro Code that can calculate 2 numbers then divided by 3 then multiply it by 2. Then if the result is higher than a MAX then have word show the MAX, if lower than MIN then to show MIN. Now, the MAX and MIN changes with years, so, the calculation result must not exceed the MAX or MIN of the year of you calculation. Everything is based on a table provided by US Gov. I have created a spread sheet that looks as follow:

     

    PERIOD

    NAWW

    MAX

    MIN

    PERCENT INCREASE

    10/01/2009 - 09/30/2010

    $612.33

    $1,224.66

    $306.17

    2.00%

    10/01/2008 - 09/30/2009

    $600.31

    $1,200.62

    $300.16

    3.47%

    10/01/2007 - 09/30/2008

    $580.18

    $1,160.36

    $290.09

    4.12%

    10/01/2006 - 09/30/2007

    $557.22

    $1,114.44

    $278.61

    3.80%

    10/01/2005 - 09/30/2006

    $536.82

    $1,073.64

    $268.41

    2.53%

    10/01/2004 - 09/30/2005

    $523.58

    $1,047.16

    $261.79

    1.59%

    10/01/2003 - 09/30/2004

    $515.39

    $1,030.78

    $257.70

    3.44%

    I have a Text Form Field that will show the result of a calculation (Text58).

    I have a Text Form with a Date (Text2).

     

    The calculation should be as follow:

     

    =(Text56 – Text43) / 3 * 2

     

    If result is higher than MAX I would like word to show the MAX.

    If result is higher than MIN would like word to show the MIN.

     

    Please let me know if this is possible.

     

    Any Help Will Be Gladly Appreciated!

     

    Friday, July 16, 2010 8:04 PM

Answers

  • Try this version:

    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim myarray As Variant
    Dim i As Long
    Dim PeriodStart As Date
    Dim PeriodEnd As Date
    Dim dDate As Date
    Dim vmax As Single
    Dim vmin As Single
    Dim val56 As Single
    Dim val43 As Single
    Dim sresult As Single
    dDate = ActiveDocument.FormFields("Text2").Result
    On Error Resume Next
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
        bstartApp = True
        Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlbook = xlapp.Workbooks.Open("C:\Users\Doug\Documents\NAWW.xlsx") 'modify as required
    Set xlsheet = xlbook.Worksheets(1)
    myarray = xlsheet.Range("A1").CurrentRegion.Value
    xlbook.Close
    If bstartApp = True Then
        xlapp.Quit
    End If
    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlsheet = Nothing
    For i = 2 To UBound(myarray, 1)
        PeriodEnd = Right(myarray(i, 1), 10)
        PeriodStart = Left(myarray(i, 1), 10)
        If DateDiff("d", dDate, PeriodEnd) >= 0 And DateDiff("d", dDate, PeriodStart) <= 0 Then
            vmax = Val(myarray(i, 3))
            vmin = Val(myarray(i, 4))
            Exit For
        End If
    Next i
    With ActiveDocument
        val56 = CSng(.FormFields("Text56").Result)
        val43 = CSng(.FormFields("Text43").Result)
        sresult = (val56 - val43) / 3 * 2
        If sresult > vmax Then
            sresult = vmax
        ElseIf sresult < vmin Then
            sresult = vmin
        Else
            sresult = sresult
        End If
        .FormFields("text58").Result = sresult
    End With


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "scorpdevil" wrote in message news:c98e3086-7880-4d1f-bdc6-661f08519111@communitybridge.codeplex.com...

    Doug,

    What do you think of separating the Start Date from End Date? i keep getting error "0"My table goes from 1972 to 2010


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by scorpdevil Sunday, July 18, 2010 4:07 PM
    Sunday, July 18, 2010 4:46 AM
  • In the code

    With ActiveDocument
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            .FormFields("Text184").Result = "MAX"
        ElseIf sresult < vmin Then
            .FormFields("Text184").Result = "MIN"
        End If
        .FormFields("Text184").Result = sresult
    End With

    The final command before the End With is going to overwrite whatever Result is set by the If/End If code.  Also, you are not dealing with the case where sresult = vmax

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "scorpdevil" wrote in message news:8180c9ac-a79d-4a0d-90d7-fe48bb959e6d@communitybridge.codeplex.com...

    Doug,

    I came out with the following code; and works great Thanks again for your time.

    With ActiveDocument
    If .FormFields("Check27").CheckBox.Value = Falce Then
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            sresult = vmax
        ElseIf sresult < vmin Then
            sresult = vmin
        Else
            sresult = sresult
        End If
        .FormFields("Text26").Result = sresult
    ElseIf .FormFields("Check27").CheckBox.Value = True Then
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        End If
        .FormFields("Text26").Result = sresult
    End With

    But i can't incorporate my MAX and MIN identifier. I tried to add the following but is not allowing me:  *

    *With ActiveDocument
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            .FormFields("Text184").Result = "MAX"
        ElseIf sresult < vmin Then
            .FormFields("Text184").Result = "MIN"
        End If
        .FormFields("Text184").Result = sresult
    *End With *

    Please help.

    Thanks

    -- Thanks Rewar


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by scorpdevil Wednesday, December 29, 2010 4:35 PM
    Tuesday, December 28, 2010 8:40 PM

All replies

  • Use:

    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim myarray As Variant
    Dim i As Long
    Dim PeriodStart As Date
    Dim PeriodEnd As Date
    Dim dDate As Date
    Dim vmax As Single
    Dim vmin As Single
    Dim val56 As Single
    Dim val43 As Single
    Dim sresult As Single
    dDate = ActiveDocument.FormFields("Text2").Result
    On Error Resume Next
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
        bstartApp = True
        Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlbook = xlapp.Workbooks.Open("C:\Users\Doug\Documents\NAWW.xlsx") 'modify as required
    Set xlsheet = xlbook.Worksheets(1)
    myarray = xlsheet.Range("A1").CurrentRegion.Value
    If bstartApp = True Then
        xlapp.Quit
    End If
    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlsheet = Nothing
    For i = 2 To UBound(myarray, 1)
        PeriodEnd = Right(myarray(i, 1), 10)
        PeriodStart = Left(myarray(i, 1), 10)
        If DateDiff("d", dDate, PeriodEnd) >= 0 And DateDiff("d", dDate, PeriodStart) <= 0 Then
            vmax = Val(myarray(i, 3))
            vmin = Val(myarray(i, 4))
        End If
        Exit For
    Next i
    MsgBox vmax
    MsgBox vmin
    With ActiveDocument
        val56 = Val(.FormFields("Text56").Result)
        val43 = Val(.FormFields("Text43").Result)
        sresult = (val56 - val43) / 3 * 2
        If sresult > vmax Then
            sresult = vmax
        ElseIf sresult < vmin Then
            sresult = vmin
        Else
            sresult = sresult
        End If
        .FormFields("text58").Result = sresult
    End With


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "scorpdevil" wrote in message news:eaa64703-1fe3-4a62-a902-c9ad946ecc62@communitybridge.codeplex.com...

    Hi Everyone,

    I would like to create a Macro Code that can calculate 2 numbers then divided by 3 then multiply it by 2. Then if the result is higher than a MAX then have word show the MAX, if lower than MIN then to show MIN. Now, the MAX and MIN changes with years, so, the calculation result must not exceed the MAX or MIN of the year of you calculation. Everything is based on a table provided by US Gov. I have created a spread sheet that looks as follow:

    PERIOD

    NAWW

    MAX

    MIN

    PERCENT INCREASE

    10/01/2009 - 09/30/2010

    $612.33

    $1,224.66

    $306.17

    2.00%

    10/01/2008 - 09/30/2009

    $600.31

    $1,200.62

    $300.16

    3.47%

    10/01/2007 - 09/30/2008

    $580.18

    $1,160.36

    $290.09

    4.12%

    10/01/2006 - 09/30/2007

    $557.22

    $1,114.44

    $278.61

    3.80%

    10/01/2005 - 09/30/2006

    $536.82

    $1,073.64

    $268.41

    2.53%

    10/01/2004 - 09/30/2005

    $523.58

    $1,047.16

    $261.79

    1.59%

    10/01/2003 - 09/30/2004

    $515.39

    $1,030.78

    $257.70

    3.44%

    I have a Text Form Field that will show the result of a calculation (Text58).

    I have a Text Form with a Date (Text2).

    The calculation should be as follow:

    =(Text56 – Text43) / 3 * 2

    If result is higher than MAX I would like word to show the MAX.

    If result is higher than MIN would like word to show the MIN.

    Please let me know if this is possible.

    Any Help Will Be Gladly Appreciated!


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Saturday, July 17, 2010 7:37 AM
  • From the information you have provided, what you ask is not possible. For a start, in order to test whether a date falls within a range of dates you will have to separate the start and end dates into separate entities then check whether the date falls after the start date and before the end date.

    Your date ranges do not complete the years in question i.e. what about the last three months of each year?

    What is the relevance of the NAWW and Percent Increase columns?

    What do you want to show if the values are between the max and min ranges?

    Reading data from an Excel worksheet to evaluate the calculation has performance implications that are unnecessary. I would create the lookup table in Word.

    If you add another column and split the start/end dates across two columns (1 and 2) and save as a Word document - here data.docx - then the following while not very pretty, should set you in the right direction.

    Dim oDoc As Document
    Dim oData As Document
    Dim sDate As Date
    Dim sText As String
    Dim iHigh As Long
    Dim iLow As Long
    Dim iValue As Long
    Dim oTable As Table
    Dim oLoDate As Date
    Dim oHiDate As Date
    Dim oLoVal As Range
    Dim oHiVal As Range
    Dim oRng As Range
    Dim i As Long
    Set oDoc = ActiveDocument
    Set oData = Documents.Open("d:\My Documents\Test\Data.docx")
    sDate = oDoc.FormFields("Text2").Result
    iHigh = oDoc.FormFields("Text56").Result
    iLow = oDoc.FormFields("Text43").Result
    iValue = (iHigh - iLow) / 3 * 2
    sText = ""
    Set oTable = oData.Tables(1)
    For i = 2 To oTable.Range.Rows.Count
        Set oRng = oTable.Cell(i, 1).Range
        oRng.End = oRng.End - 1
        oLoDate = oRng.Text
        Set oRng = oTable.Cell(i, 2).Range
        oRng.End = oRng.End - 1
        oHiDate = oRng.Text
        If sDate >= oLoDate And _
        sDate <= oHiDate Then
            Set oLoVal = oTable.Cell(i, 5).Range
            oLoVal.End = oLoVal.End - 1
            Set oHiVal = oTable.Cell(i, 4).Range
            oHiVal.End = oHiVal.End - 1
            sText = iValue
            If iValue > oHiVal Then sText = "MAX"
            If iValue < oLoVal Then sText = "MIN"
            Exit For
        End If
    Next i
    If sText = "" Then
        sText = "Out of range"
    End If
    oDoc.FormFields("Text58").Result = sText

    <scorpdevil> wrote in message news:eaa64703-1fe3-4a62-a902-c9ad946ecc62@communitybridge.codeplex.com...

    Hi Everyone,

    I would like to create a Macro Code that can calculate 2 numbers then divided by 3 then multiply it by 2. Then if the result is higher than a MAX then have word show the MAX, if lower than MIN then to show MIN. Now, the MAX and MIN changes with years, so, the calculation result must not exceed the MAX or MIN of the year of you calculation. Everything is based on a table provided by US Gov. I have created a spread sheet that looks as follow:



         PERIOD
        NAWW
        MAX
        MIN
        PERCENT INCREASE

         10/01/2009 - 09/30/2010
        $612.33
        $1,224.66
        $306.17
        2.00%

         10/01/2008 - 09/30/2009
        $600.31
        $1,200.62
        $300.16
        3.47%

         10/01/2007 - 09/30/2008
        $580.18
        $1,160.36
        $290.09
        4.12%

         10/01/2006 - 09/30/2007
        $557.22
        $1,114.44
        $278.61
        3.80%

         10/01/2005 - 09/30/2006
        $536.82
        $1,073.64
        $268.41
        2.53%

         10/01/2004 - 09/30/2005
        $523.58
        $1,047.16
        $261.79
        1.59%

         10/01/2003 - 09/30/2004
        $515.39
        $1,030.78
        $257.70
        3.44%




    I have a Text Form Field that will show the result of a calculation (Text58).

    I have a Text Form with a Date (Text2).



    The calculation should be as follow:



    =(Text56 - Text43) / 3 * 2



    If result is higher than MAX I would like word to show the MAX.

    If result is higher than MIN would like word to show the MIN.



    Please let me know if this is possible.



    Any Help Will Be Gladly Appreciated!


    Graham Mayor - Word MVP
    www.gmayor.com
    Posted via the Communities Bridge
    http://communitybridge.codeplex.com/
    Saturday, July 17, 2010 8:15 AM
  • Ah! I see we crossed in the mail - thanks to the bridge not automatically updating. :(

    Between the two approaches (and I guess not splitting the date range into two columns would work) our friend should get somewhere. There is still the issue of the missing dates however?


    Graham Mayor - Word MVP
    www.gmayor.com
    Posted via the Communities Bridge
    http://communitybridge.codeplex.com/
    Saturday, July 17, 2010 8:23 AM
  • Hi Graham,

    The date ranges are from October 1 thru September 30 of the following year - i.e. a full 12 months.

    Even using Late Binding, accessing the data in Excel is (to all intents and purposes) instantaneous.

    The myarray = xlsheet.Range("A1").CurrentRegion.Value method is really nifty for grabbing and undefined range of values from Excel.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "Graham Mayor" wrote in message news:126ba739-a5ce-4d1a-bb29-ba7cacd7a48d@communitybridge.codeplex.com...

    Ah! I see we crossed in the mail - thanks to the bridge not automatically updating. :(

    Between the two approaches (and I guess not splitting the date range into two columns would work) our friend should get somewhere. There is still the issue of the missing dates however?

    -- Graham Mayor - Word MVP
    http://www.gmayor.com
    Posted via the Communities Bridge
    http://communitybridge.codeplex.com/


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Saturday, July 17, 2010 10:08 AM
  • When you don't normally use, but are presented with US date formats - it is all too easy to read it the wrong way round.:(

    <Doug Robbins [MVP]> wrote in message news:832ac927-a3d3-4feb-8df4-5e944cf00a6c@communitybridge.codeplex.com...

    Hi Graham,

    The date ranges are from October 1 thru September 30 of the following year - i.e. a full 12 months.


    Graham Mayor - Word MVP
    www.gmayor.com
    Posted via the Communities Bridge
    http://communitybridge.codeplex.com/
    Saturday, July 17, 2010 10:50 AM
  • Gentlemen:

    I did run the Macro Code on Exit from Text58 and I'm getting an error.

     

    0

     

    that's all it says.

    I'm using

    Text56 = $1222.48

    Text43 = $340.00

    Text2 = 09/11/1984

     

    thanks

    Saturday, July 17, 2010 9:02 PM
  • 09/11/1984 is way before the periods for which you have the data.

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "scorpdevil" wrote in message news:a5a832f4-80f5-488c-acd7-d109031bbfc6@communitybridge.codeplex.com...

    Gentlemen:

    I did run the Macro Code on Exit from Text58 and I'm getting an error.

    0

    that's all it says.

    I'm using

    Text56 = $1222.48

    Text43 = $340.00

    Text2 = 09/11/1984

    thanks


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Saturday, July 17, 2010 11:10 PM
  • Doug,

    What do you think of separating the Start Date from End Date? i keep getting error "0"

    My table goes from 1972 to 2010
    Sunday, July 18, 2010 12:12 AM
  • Try this version:

    Dim xlapp As Object
    Dim xlbook As Object
    Dim xlsheet As Object
    Dim myarray As Variant
    Dim i As Long
    Dim PeriodStart As Date
    Dim PeriodEnd As Date
    Dim dDate As Date
    Dim vmax As Single
    Dim vmin As Single
    Dim val56 As Single
    Dim val43 As Single
    Dim sresult As Single
    dDate = ActiveDocument.FormFields("Text2").Result
    On Error Resume Next
    Set xlapp = GetObject(, "Excel.Application")
    If Err Then
        bstartApp = True
        Set xlapp = CreateObject("Excel.Application")
    End If
    On Error GoTo 0
    Set xlbook = xlapp.Workbooks.Open("C:\Users\Doug\Documents\NAWW.xlsx") 'modify as required
    Set xlsheet = xlbook.Worksheets(1)
    myarray = xlsheet.Range("A1").CurrentRegion.Value
    xlbook.Close
    If bstartApp = True Then
        xlapp.Quit
    End If
    Set xlapp = Nothing
    Set xlbook = Nothing
    Set xlsheet = Nothing
    For i = 2 To UBound(myarray, 1)
        PeriodEnd = Right(myarray(i, 1), 10)
        PeriodStart = Left(myarray(i, 1), 10)
        If DateDiff("d", dDate, PeriodEnd) >= 0 And DateDiff("d", dDate, PeriodStart) <= 0 Then
            vmax = Val(myarray(i, 3))
            vmin = Val(myarray(i, 4))
            Exit For
        End If
    Next i
    With ActiveDocument
        val56 = CSng(.FormFields("Text56").Result)
        val43 = CSng(.FormFields("Text43").Result)
        sresult = (val56 - val43) / 3 * 2
        If sresult > vmax Then
            sresult = vmax
        ElseIf sresult < vmin Then
            sresult = vmin
        Else
            sresult = sresult
        End If
        .FormFields("text58").Result = sresult
    End With


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "scorpdevil" wrote in message news:c98e3086-7880-4d1f-bdc6-661f08519111@communitybridge.codeplex.com...

    Doug,

    What do you think of separating the Start Date from End Date? i keep getting error "0"My table goes from 1972 to 2010


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by scorpdevil Sunday, July 18, 2010 4:07 PM
    Sunday, July 18, 2010 4:46 AM
  • Personally I would make Text58 non-editable and run the macro on exit from the previous contributing field - but see Doug's comments re the date.

    <scorpdevil> wrote in message news:a5a832f4-80f5-488c-acd7-d109031bbfc6@communitybridge.codeplex.com...

    Gentlemen:

    I did run the Macro Code on Exit from Text58 and I'm getting an error.



    0



    that's all it says.

    I'm using

    Text56 = $1222.48

    Text43 = $340.00

    Text2 = 09/11/1984



    thanks


    Graham Mayor - Word MVP
    www.gmayor.com
    Posted via the Communities Bridge
    http://communitybridge.codeplex.com/
    Sunday, July 18, 2010 5:27 AM
  • Doug,

    You are a GENIUS. There is no limit for you!

    Work like a charm

    Sunday, July 18, 2010 4:10 PM
  • Doug,

    i need to add some new changes to your calculation.

    1. I would like to add a TextForm Field that will alert me when I'm using the max or min. For example:

    TextForm 1 + TextForm 2 = higher than max to show "MAX" in TextForm 3.

    TextForm 1 + TextForm 2 = lower than min to show "MIN" in TextForm 3.

    2. I need to "round off" the calculation result. Example:

    If result is 506.6666666 i would like to create a code for that. If the second digit [ . 66 ] is higher that 5 to automatically calculate 506.66 + 0.01 to = 506.67, but result is not higher than 5 to keep the same result.

    3. Then i need to create a "checkbox" to work as a switch. example

    when i check the box: i want the calculation to NOT to show the max or the min.

    when the checkbox is checked i want to show the max and the min.

    thanks

    and happy new year.

     

    Monday, December 27, 2010 7:56 PM
  • For 1 and 3

    With ActiveDocument
        If FormFields("chkshowmaxmin").CheckBox.Value = True Then
            If .FormFields("TextForm1").Result + .FormFields("TextForm2).Result  > [somevalue] AND then
                ,FormFields("TextForm3").Result = "MAX"
            ElseIf .FormFields("TextForm1").Result + .FormFields("TextForm2).Result < [somevalue] then
                ,FormFields("TextForm3").Result = "MIN"
            Else
                .FormFields("TextForm3").Result = "EQUAL"
            End If
        Else
            .FormFields("TextForm3").Result = ""
        End if
    End With

    For 2, use the Round() function

    Round(566.666, 2) will return 566.67

    Round(566.665, 2) returns 566.66

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "scorpdevil" wrote in message news:bf1d562f-db14-4dbb-8c6a-b08b346978fc@communitybridge.codeplex.com...

    Doug,

    i need to add some new changes to your calculation.

    1. I would like to add a TextForm Field that will alert me when I'm using the max or min. For example:

    TextForm 1 + TextForm 2 = higher than max to show "MAX" in TextForm 3.

    TextForm 1 + TextForm 2 = lower than min to show "MIN" in TextForm 3.

    2. I need to "round off" the calculation result. Example:

    If result is 506.6666666 i would like to create a code for that. If the second digit [ . 66 ] is higher that 5 to automatically calculate 506.66 + 0.01 to = 506.67, but result is not higher than 5 to keep the same result.

    3. Then i need to create a "checkbox" to work as a switch. example

    when i check the box: i want the calculation to NOT to show the max or the min.

    when the checkbox is checked i want to show the max and the min.

    thanks

    and happy new year.


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Monday, December 27, 2010 9:58 PM
  • Doug,

    I came out with the following code; and works great Thanks again for your time.

    With ActiveDocument
    If .FormFields("Check27").CheckBox.Value = Falce Then
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            sresult = vmax
        ElseIf sresult < vmin Then
            sresult = vmin
        Else
            sresult = sresult
        End If
        .FormFields("Text26").Result = sresult
    ElseIf .FormFields("Check27").CheckBox.Value = True Then
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        End If
        .FormFields("Text26").Result = sresult
    End With

    But i can't incorporate my MAX and MIN identifier. I tried to add the following but is not allowing me:

    With ActiveDocument
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            .FormFields("Text184").Result = "MAX"
        ElseIf sresult < vmin Then
            .FormFields("Text184").Result = "MIN"
        End If
        .FormFields("Text184").Result = sresult
    End With

    Please help.

    Thanks


    Thanks Rewar
    Tuesday, December 28, 2010 4:35 PM
  • In the code

    With ActiveDocument
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            .FormFields("Text184").Result = "MAX"
        ElseIf sresult < vmin Then
            .FormFields("Text184").Result = "MIN"
        End If
        .FormFields("Text184").Result = sresult
    End With

    The final command before the End With is going to overwrite whatever Result is set by the If/End If code.  Also, you are not dealing with the case where sresult = vmax

    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "scorpdevil" wrote in message news:8180c9ac-a79d-4a0d-90d7-fe48bb959e6d@communitybridge.codeplex.com...

    Doug,

    I came out with the following code; and works great Thanks again for your time.

    With ActiveDocument
    If .FormFields("Check27").CheckBox.Value = Falce Then
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            sresult = vmax
        ElseIf sresult < vmin Then
            sresult = vmin
        Else
            sresult = sresult
        End If
        .FormFields("Text26").Result = sresult
    ElseIf .FormFields("Check27").CheckBox.Value = True Then
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        End If
        .FormFields("Text26").Result = sresult
    End With

    But i can't incorporate my MAX and MIN identifier. I tried to add the following but is not allowing me:  *

    *With ActiveDocument
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            .FormFields("Text184").Result = "MAX"
        ElseIf sresult < vmin Then
            .FormFields("Text184").Result = "MIN"
        End If
        .FormFields("Text184").Result = sresult
    *End With *

    Please help.

    Thanks

    -- Thanks Rewar


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    • Marked as answer by scorpdevil Wednesday, December 29, 2010 4:35 PM
    Tuesday, December 28, 2010 8:40 PM
  • Doug,

    I came with the following code:

    With ActiveDocument
        val25 = CSng(.FormFields("Text25").Result)
        sresult = Round(((val25) * 2 / 3), 2)
        If sresult > vmax Then
            .FormFields("Text184").Result = "(max)"
        ElseIf sresult < vmin Then
            .FormFields("Text184").Result = "(min)"
        ElseIf sresult < vmax Then
            .FormFields("Text184").Result = ""
        ElseIf sresult > vmin Then
            .FormFields("Text184").Result = ""
        Else
            sresult = sresult
        End If
    End With

    Excellent! - Thanks for your time


    Thanks Rewar
    Wednesday, December 29, 2010 4:29 PM