Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is this comparison correct (2003)

    It would help if you told us

    a) What the purpose of your code is (what do you want to accomplish)
    [img]/forums/images/smilies/cool.gif[/img] What the variables that are not defined/set in this code fragment mean and what their value is (aVar, bVar)

  2. #2
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Is this comparison correct (2003)

    Here is what I Have:

    <pre>Dim I As Long
    Dim C As Long
    Dim aVar, bVar, srng As String
    Dim rng As Long
    aVar = "ChgOrd"
    bVar = "PavBid"
    Range("E255").Select
    For I = 1 to Worksheets.Count
    rng = 255
    For C = 1 to 31
    srng = "EZ" & rng
    If (bVar & I & Chr(33) & srng) = (aVar & Chr(33) & srng) Then
    ActiveCell = 0
    ActiveCell.Offset(1, 0).Activate
    rng = rng + 1
    ElseIf bVar & I & Chr(33) & srng) <> (aVar & I & Chr(33) & srng) Then
    ActiveCell.Formula = Chr(61) & bVar & I & Chr(33) & srng & Chr(45) & _
    aVar & Chr(33) & srng
    ActiveCell.Offset(1, 0).Activate
    rng = rng + 1
    End If
    Next C
    ....
    </pre>


    What I thought I was doing with this code was comparing 2 values, which should read as follows:
    If ChrOrd1!EZ255 = PavBid!EX255 Then...

    I am needing to know if this way of coding will work to accomplish my goal?

    thanks,
    jackal

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is this comparison correct (2003)

    i have fixed the orginal post, i hope

    jackal

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is this comparison correct (2003)

    I runs from 1 to the number of worksheets. So what are ChOrd1, ChOrd2 etc., and PavBid1, PavBid2 etc.? Worksheets in two different workbooks? If so, what is the name of the workbooks?

    (I'm asking because you will have to refer to cells in a different way)

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is this comparison correct (2003)

    Right now there are 7 worksheets (ChrOrd1, CO Cost1 Diff, PavBid, Org Cost, WORKSHEET, Days Per Month, Sheet1)
    I am on sheet "CO Cost1 Diff", trying to compare ChrOrd!EZ255 with PavBid!EZ255. If these cells are equal then do something, if not do something else. Hope this helps.
    thanks,
    jackal

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is this comparison correct (2003)

    What, then, is the role of the I in your code? I'm getting more and more confused.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is this comparison correct (2003)

    Insofar as I can understand your code, I wonder why you can't simply enter the formula

    =PavBid!EZ255-ChrOrd!EZ255

    in E255 on the CO Cost1 Diff sheet, and fill down to E285. Or, if you prefer to use code, it can be simplified to

    Dim r As Long
    For r = 255 To 285
    Range("E" & r).Formula = "=PavBid!EZ" & r & "-ChOrd!EZ" & r
    Next r

    But perhaps I completely missed the mark.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is this comparison correct (2003)

    let me take a look at what you have provided and i will get back with you.
    thanks,
    jackal

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is this comparison correct (2003)

    Sorry for the confusion, I have attached a txt file with the entire procedure. My ultimate goal is to test the value of ChgOrd!EZ255 with PavBid!EZ255, based on whether or not they are equal. If so then do this, if not do that. The I that you are confused about is used to determine if there has been a change-order on the current job or not, if there has then it will name the new change-order accordingly. Hope this help to clarify things.

    thanks,
    jackal

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is this comparison correct (2003)

    I am totally baffled by

    For I = 1 To Worksheets.Count
    ...
    Set oWS = Worksheets("CO Cost" & I & " Diff")
    ...
    Worksheets("ChgOrd" & I).Select

    This makes no sense at all. Let's say your workbook has 12 worksheets, I would run from 1 to 12. So, you are referring to worksheers "Co Cost1 Diff" through "Co Cost12 Diff" and "ChOrd1" through "ChOrd12". That makes at least 24 worksheets!

    And what is the purpose of the For C = 1 To 31?

    I really think you should try to give a CLEAR explanation of what you want to do.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is this comparison correct (2003)

    Ok say i have a workbook with worksheets Sheet1, Sheet2 & Sheet3. In Sheet1!A1 the cell value is 10, Sheet2!A1 value is 7. In my macro I need to evaluate the values for Sheet1!A1 with Sheet2!A1, if they are equal then in Sheet3!D5 value needs to be 0, otherwise paste the formula Sheet!A1-Sheet2!A1 or the value of this difference.

    thanks,
    jackal

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is this comparison correct (2003)

    Why not always use the difference formula? If the cells are equal, the difference will be 0. See <post:=536,380>post 536,380</post:>.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is this comparison correct (2003)

    That will work about 50% of the time. The formula of the referenced cell (Sheet2!A1) will give a "#N/A" when not used. So my thinking was that I would compare the 2 values before any formula or other value was placed in Sheet3, but i may be going about this in the wrong way?

    jackal

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Is this comparison correct (2003)

    What do you mean by "when not used"?

  15. #15
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Is this comparison correct (2003)

    These values are subcontractor info ( CostCodes, Categeory, Desc, UoM, Est Qty, etc.). The user is limited to 30 possible Subs, so they are not all used. The #N/A is coming from a vlookup with the lookup range = false (i need an exact match).

    jackal

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •