# Thread: Is this comparison correct (2003)

1. ## 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. ## 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. ## Re: Is this comparison correct (2003)

i have fixed the orginal post, i hope

jackal

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Is this comparison correct (2003)

What do you mean by "when not used"?

15. ## 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 Last

#### Posting Permissions

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