comparing values that don't equal but do equal?

R

RussTheBear

comparing values that don't equal but do equal?

i built a macro that is supposed to check values of one cell versus another
cell

a message is supposed to pop up when the values equal and not when they
don't equal

however, a message pops up regardless of whether they equal or not

there are two conditional areas where the values are tested, which are
called ACTIVE and EDITED

my code is long...the issue is towards the bottom under EDITED, which is the
same as under ACTIVE, but the error only occurs with EDITED


Sub QQQQ()


'---------------------This must stay here-----------------------
'First I have to select Column D to measure the amount of rows
'in order to know when to stop

Range("D10").Select
x = ActiveCell.Row
y = ActiveCell.Column
z = 0
Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop
'--------------------------------------------

'--------------------CHECK EDITS--------------

'Calculate the number of rows in the book based of Event Code column
Range("D10").Activate
x = ActiveCell.Row
y = ActiveCell.Column
z = 0

Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop

'z is still the height of the book
Range("C10").Select
Selection.End(xlToRight).Select
y = ActiveCell.Column
'make y constant as the last column in the book
y = y + 0
'-----------------------------------

'start checking values in row 11
x = 11
'make w equal the column before the last edit column
w = y - 7

'----------------------------------------------------------------------------------------------------------
'---------------------Check edits for missing confirmation numbers or
missing prices-----------------------
'only performed on active or edited postings

Do
Cells(x, w).Select
If IsError(Cells(x, 1).Value) Then
'then do nothing and go to the next row
x = x + 1
ElseIf Cells(x, 1).Value <> "active" And Cells(x, 1).Value <>
"edited" Then
'then do nothing and go to the next row
x = x + 1
'ACTIVE-----------------------------
ElseIf Cells(x, 1).Value = "active" Then
Do Until w < 29
Cells(x, w).Select
'---------edit columns later than the first edit
'NO EDIT
If Cells(x, w).Value = "" _
And Cells(x, w + 2).Value = "" _
And Cells(x, w + 3).Value = "" _
Then
'EDIT WITH CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value <> "" _
And Cells(x, w + 3).Value <> "" _
Then
'MISSING CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value = "" _
Or Cells(x, w + 3).Value = "" _
Then
Cells(x, w).Select
MsgBox "Missing confirmation numbers. Record this row
and column and error type, and press OK to coninue the macro."
'don't end sub
'MISSING PRICE
ElseIf Cells(x, w).Value = "" _
And Cells(x, w + 2).Value <> "" _
Or Cells(x, w + 3).Value <> "" _
Then
Cells(x, w).Select
MsgBox "Missing price. Record this row and column and
error type, and press OK to coninue the macro."
'don't end sub
End If
w = w - 4
Loop
x = x + 1
w = y - 7
'EDITED-----------------------------
ElseIf Cells(x, 1).Value = "edited" Then
Do Until w < 29
Cells(x, w).Select
'---------edit columns later than the first edit
'NO EDIT
If Cells(x, w).Value = "" _
And Cells(x, w + 2).Value = "" _
And Cells(x, w + 3).Value = "" _
Then
'EDIT WITH CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value <> "" _
And Cells(x, w + 3).Value <> "" _
Then
'MISSING CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value = "" _
Or Cells(x, w + 3).Value = "" _
Then
Cells(x, w).Select
MsgBox "Missing confirmation numbers. Record this row
and column and error type, and press OK to coninue the macro."
'don't end sub
'MISSING PRICE
ElseIf Cells(x, w).Value = "" _
And Cells(x, w + 2).Value <> "" _
Or Cells(x, w + 3).Value <> "" _
Then
Cells(x, w).Select
MsgBox "Missing price. Record this row and column and
error type, and press OK to coninue the macro."
'don't end sub
End If
w = w - 4
Loop
x = x + 1
w = y - 7
End If
Loop Until x = z + 10

'----------------------------------------------------------------------------------------------------------
'---------------------Check edits for MATCHING prices-----------------------
'only performed on active or edited postings

'start checking values in row 11
x = 11
'make w equal the last edit column
w = y - 3
'make v equal the next edit column before w
v = w - (4 * a)
'make a a multiple
a = 1

Do
Cells(x, w).Activate
Cells(x, v).Activate
'#N/A-----------------------------
If IsError(Cells(x, 1).Value) Then
Do Until w < 29
Cells(x, w).Select
'ALL BLANK IS GOOD
If Cells(x, w).Value = "" _
And Cells(x, w + 2).Value = "" _
And Cells(x, w + 3).Value = "" _
Then
'ANY VALUES IS BAD
ElseIf Cells(x, w).Value <> "" _
Or Cells(x, w + 2).Value <> "" _
Or Cells(x, w + 3).Value <> "" _
Then
Cells(x, w).Select
MsgBox "This ticket has never been posted. There should
not be any information here. Record this row and column and error type, and
press OK to coninue the macro."
'don't end sub
End If
w = w - 4
Loop

ElseIf Cells(x, 1).Value <> "active" And Cells(x, 1).Value <>
"edited" Then
'then do nothing and go to the next row

'ACTIVE-----------------------------
ElseIf Cells(x, 1).Value = "active" Then
Do Until w = 29 'stop at the second edit column
Cells(x, w).Activate
Cells(x, v).Activate

'NO EDIT FOUND
If Cells(x, w).Value = "" Then
w = w - 4
a = 1

'EDIT FOUND AND NOT EQUAL
ElseIf Cells(x, w).Value <> "" _
And (Cells(x, w).Value <> Cells(x, v).Value) Then
'don't end sub
'but go to the next row by ending the loop by making w =
29
w = 29

'EDIT FOUND AND EQUAL
ElseIf Cells(x, w).Value <> "" _
And (Cells(x, w).Value = Cells(x, v).Value) Then
Cells(x, w).Select
MsgBox "New edit prices cannot match old previous
prices. Record this row and column and error type, and press OK to coninue
the macro."
'don't end sub
'but go to the next row by ending the loop by making w =
29
w = 29

'EDIT FOUND BUT NOT SEQUENTIAL
ElseIf Cells(x, w).Value <> "" _
And Cells(x, v).Value = "" Then
Do
a = a + 1
Loop Until v = 29

End If
Loop


'EDITED-----------------------------
ElseIf Cells(x, 1).Value = "edited" Then
Do Until w = 29 'stop at the second edit column
Cells(x, w).Activate
Cells(x, v).Activate

'NO EDIT FOUND
If Cells(x, w).Value = "" Then
w = w - 4
a = 1

'EDIT FOUND AND NOT EQUAL
ElseIf Cells(x, w).Value <> Cells(x, v).Value Then
'don't end sub
'but go to the next row by ending the loop by making w =
29
w = 29

'***********************************************
'The error occurs below here
'***********************************************

'EDIT FOUND AND EQUAL
ElseIf Cells(x, w).Value = Cells(x, v).Value Then
Cells(x, w).Select
MsgBox "New edit prices cannot match old previous
prices. Record this row and column and error type, and press OK to coninue
the macro."
'don't end sub
'but go to the next row by ending the loop by making w =
29
w = 29

'EDIT FOUND BUT NOT SEQUENTIAL
ElseIf Cells(x, w).Value <> "" _
And Cells(x, v).Value = "" Then
Do
a = a + 1
Loop Until v = 29

End If
Loop

End If

'go to the next row and reset variables
x = x + 1
w = y - 3
v = w - (4 * a)
a = 1

Loop Until x = z + 10

End Sub
 
I

ilia

Looking at a line like this:

ElseIf Cells(x, w).Value = Cells(x, v).Value
Looking at a line like this:

ElseIf Cells(x, w).Value = Cells(x, v).Value

This will evaluate to false for a number of reasons. Two leading
causes: case may not match, and/or there might be leading or trailing
spaces.

A better way to code this line is:

ElseIf LCase$(Trim$(Cells(x, w).Value)) = LCase$(Trim$(Cells(x,
v).Value))

This will compare the two cells in all lower-case, and ignore leading
and trailing spaces.

Another issue I've encountered is user entering additional data, in
other words they'll put something like "edited (but not too much)" and
then you have another cell with text "edited" you won't get a match.
Using a specific number of characters from the left can solve this
problem, or throwing out everything after the first space if a space
exists when the length from left varies. You could code this
exception-testing logic like so:

Dim i as integer
i = InStr(1, Trim$(Cells(x, w).Value)), " ")

If i <> 0 Then
' you have a space, and it's not at the beginning or end of the cell
value
' edit the cell value to match format, or whatever you want to do
Cells(x, w).Value = Left$(Trim$(Cells(x, w).Value), i - 1)
End If

' do the same thing for Cells(x, v).Value

If LCase$(Cells(x, w).Value) = LCase$(Cells(x, v).Value) Then
Call MsgBox("Those two cells are equal.")
End If


Hope that helps.
 
P

Per Jessen

Hi

I think i found the error. The values v and w are equal, so you are
comparing the value against it self.

Make a breakpoint at the line with the msgbox, to check the values.

BTW: In stead of setting w=29 to end the Do loop you can use "Exit Do".

Regards,

Per
 
R

RussTheBear

No, the values are generated and will thus always be correct. I need to check
the values of prices in other columns, which may or may not equal. When they
do, then i need an error message. The problem is that the changes to the
prices occur across a long set of columns and the location is not consistent.
This inconsistency must occur this way for tracking purposes. So I have
developed a formula to calculate various variables in order to point to the
values of certains cells within that sequence.

Pay close attention to the definition of variables.

Sub QQQQ()
'
' Q Macro
' Macro recorded 1/28/2008 by Russell Bernstein
'
' Keyboard Shortcut: Ctrl+Shift+Q
'


'---------------------This must stay here-----------------------
'First I have to select Column D to measure the amount of rows
'in order to know when to stop

Range("D10").Select
x = ActiveCell.Row
y = ActiveCell.Column
z = 0
Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop
'--------------------------------------------

'-------------------------------------------------------------------------------
'--------------------CHECK EDITS--------------

'Calculate the number of rows in the book based of Event Code column
Range("D10").Activate
x = ActiveCell.Row
y = ActiveCell.Column
z = 0

Do While Cells(x, y).Value <> ""
x = x + 1
z = z + 1
Loop

'z is still the height of the book
Range("C10").Select
Selection.End(xlToRight).Select
y = ActiveCell.Column
'make y constant as the last column in the book
y = y + 0
'-----------------------------------

'start checking values in row 11
x = 11
'make w equal the column before the last edit column
w = y - 7

'----------------------------------------------------------------------------------------------------------
'---------------------Check edits for missing confirmation numbers or
missing prices-----------------------
'only performed on active or edited postings

Do
Cells(x, w).Select
If IsError(Cells(x, 1).Value) Then
'then do nothing and go to the next row
x = x + 1
ElseIf Cells(x, 1).Value <> "active" And Cells(x, 1).Value <>
"edited" Then
'then do nothing and go to the next row
x = x + 1
'ACTIVE-----------------------------
ElseIf Cells(x, 1).Value = "active" Then
Do Until w < 29
Cells(x, w).Select
'---------edit columns later than the first edit
'NO EDIT
If Cells(x, w).Value = "" _
And Cells(x, w + 2).Value = "" _
And Cells(x, w + 3).Value = "" _
Then
'EDIT WITH CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value <> "" _
And Cells(x, w + 3).Value <> "" _
Then
'MISSING CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value = "" _
Or Cells(x, w + 3).Value = "" _
Then
Cells(x, w).Select
MsgBox "Missing confirmation numbers. Record this row
and column and error type, and press OK to coninue the macro."
'don't end sub
'MISSING PRICE
ElseIf Cells(x, w).Value = "" _
And Cells(x, w + 2).Value <> "" _
Or Cells(x, w + 3).Value <> "" _
Then
Cells(x, w).Select
MsgBox "Missing price. Record this row and column and
error type, and press OK to coninue the macro."
'don't end sub
End If
w = w - 4
Loop
x = x + 1
w = y - 7
'EDITED-----------------------------
ElseIf Cells(x, 1).Value = "edited" Then
Do Until w < 29
Cells(x, w).Select
'---------edit columns later than the first edit
'NO EDIT
If Cells(x, w).Value = "" _
And Cells(x, w + 2).Value = "" _
And Cells(x, w + 3).Value = "" _
Then
'EDIT WITH CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value <> "" _
And Cells(x, w + 3).Value <> "" _
Then
'MISSING CONFIRMATION NUMBERS
ElseIf Cells(x, w).Value <> "" _
And Cells(x, w + 2).Value = "" _
Or Cells(x, w + 3).Value = "" _
Then
Cells(x, w).Select
MsgBox "Missing confirmation numbers. Record this row
and column and error type, and press OK to coninue the macro."
'don't end sub
'MISSING PRICE
ElseIf Cells(x, w).Value = "" _
And Cells(x, w + 2).Value <> "" _
Or Cells(x, w + 3).Value <> "" _
Then
Cells(x, w).Select
MsgBox "Missing price. Record this row and column and
error type, and press OK to coninue the macro."
'don't end sub
End If
w = w - 4
Loop
x = x + 1
w = y - 7
End If
Loop Until x = z + 10

'----------------------------------------------------------------------------------------------------------
'---------------------Check edits for MATCHING prices-----------------------
'only performed on active or edited postings

'start checking values in row 11
x = 11
'make w equal the last edit column
w = y - 3
'make v equal the next edit column before w
v = w - (4 * a)
'make a a multiple
a = 1


Do
Cells(x, w).Activate
Cells(x, v).Activate
'#N/A-----------------------------
If IsError(Cells(x, 1).Value) Then
Do Until w < 29
Cells(x, w).Select
'ALL BLANK IS GOOD
If Cells(x, w).Value = "" _
And Cells(x, w + 2).Value = "" _
And Cells(x, w + 3).Value = "" _
Then
'ANY VALUES IS BAD
ElseIf Cells(x, w).Value <> "" _
Or Cells(x, w + 2).Value <> "" _
Or Cells(x, w + 3).Value <> "" _
Then
Cells(x, w).Select
MsgBox "This ticket has never been posted. There should
not be any information here. Record this row and column and error type, and
press OK to coninue the macro."
'don't end sub
End If
w = w - 4
Loop

ElseIf Cells(x, 1).Value <> "active" And Cells(x, 1).Value <>
"edited" Then
'then do nothing and go to the next row

'ACTIVE-----------------------------
ElseIf Cells(x, 1).Value = "active" Then
Do Until w = 29 'stop at the second edit column
Cells(x, w).Activate
Cells(x, v).Activate

'NO EDIT FOUND
If Cells(x, w).Value = "" Then
Cells(x, w).Select
Cells(x, v).Select
w = w - 4
a = 1
v = w - (4 * a)

'EDIT FOUND AND NOT EQUAL
ElseIf Cells(x, w).Value <> "" _
And (Cells(x, w).Value <> Cells(x, v).Value) Then
'don't end sub
'but go to the next row
Exit Do

'EDIT FOUND AND EQUAL
ElseIf Cells(x, w).Value <> "" _
And (Cells(x, w).Value = Cells(x, v).Value) Then
Cells(x, w).Select
MsgBox "New edit prices cannot match old previous
prices. Record this row and column and error type, and press OK to coninue
the macro."
'don't end sub
'but go to the next row
Exit Do

'EDIT FOUND BUT NOT SEQUENTIAL
ElseIf Cells(x, w).Value <> "" _
And Cells(x, v).Value = "" Then
Do
Cells(x, w).Select
Cells(x, v).Select
a = a + 1
w = w
v = w - (4 * a)
Loop Until Cells(x, v).Value <> "" Or v = 29

End If
Loop


'EDITED-----------------------------
ElseIf Cells(x, 1).Value = "edited" Then
Do Until w = 29 'stop at the second edit column
Cells(x, w).Activate
Cells(x, v).Activate

'NO EDIT FOUND
If Cells(x, w).Value = "" Then
Cells(x, w).Select
Cells(x, v).Select
w = w - 4
a = 1
v = w - (4 * a)

'EDIT FOUND AND NOT EQUAL
ElseIf Cells(x, w).Value <> "" _
And (Cells(x, w).Value <> Cells(x, v).Value) Then
'don't end sub
'but go to the next row
Exit Do

'EDIT FOUND AND EQUAL
ElseIf Cells(x, w).Value <> "" _
And (Cells(x, w).Value = Cells(x, v).Value) Then
Cells(x, w).Select
MsgBox "New edit prices cannot match old previous
prices. Record this row and column and error type, and press OK to coninue
the macro."
'don't end sub
'but go to the next row
Exit Do

'EDIT FOUND BUT NOT SEQUENTIAL
ElseIf Cells(x, w).Value <> "" _
And Cells(x, v).Value = "" Then
Do
Cells(x, w).Select
Cells(x, v).Select
a = a + 1
w = w
v = w - (4 * a)
Loop Until Cells(x, v).Value <> "" Or v = 29

End If
Loop

End If

'go to the next row and reset variables
x = x + 1
w = y - 3
v = w - (4 * a)
a = 1

Loop Until x = z + 10

End Sub
 
P

paul.robinson

Hi
Havn't gone through your code, but it is often difficult to check if
two values are, or are not, equal. Excel will check all the digits in
the calculation, not just the ones you can see. e.g. You might see
1.12 and 1.12 in the cells but the actual values may be 1.1200001 and
1.1200002, which are not equal.
You could try
If Abs(Value1 - Value2)<0.005 then

to check if two numbers are the same to two decimal places, or use the
Excel round function in each cell replacing
=formula
in the cells with
=round(formula, 2)
so that numbers are rounded to two decimals before your code compares
them.

regards
Paul
 
R

RussTheBear

No, the values will always be integers. The numbers are generated manually.

Basically the entire script checks values like this

1. select Column D to measure the amount of rows in order to know when to
stop because Column D is the only column where there will always be data from
top to bottom

2. make y constant as the last column in the book because we need it to
determine the location of previous columns

3. make w equal the column before the last edit column which is 3 columns
before the last column

4. make v equal the next edit column before w (4 columns before), but there
might be blank edit columns in between so we need to create a variable that
represents a multiple of the distance between each edit column (4), a
distance which remains constant (at 4)

5. look at the value of the first cell in the first column in the first row
to see the status
a. If this cell displays #N/A then do nothing and go to the next row
b. If the cell displays something besides "active" or "edited" then do
nothing and go to the next row
c. If the cell displays "active" then select the first cell in the last
edit column in the same row
i. but if this cell is blank, then select the cell in the same row in
columns prior to this (from right to left) until you find a value and if you
don't find a value before a certain point (column 29), then go to the next
row and start over
ii. if you find a value, then check this value against the value in
one more previous (4 to the left) unless 4 to the left is less than 29
- but if this is blank too, then go one more (4 columns) until you
find a value to check it against, but if there are none by the time you reach
column 29, then stop checking and go to the next row
- if you find another, then check the values versus each
other...they should not equal

6. repeat this for "edited"


Here are the areas where I believe I am having problems

'------------the definition of variables---------------
'start checking values in row 11
x = 11
'make w equal the last edit column
w = y - 3
'make v equal the next edit column before w
v = w - (4 * a)
'make a a multiple
a = 1

'------------the problem area---------------
'EDIT FOUND BUT NOT SEQUENTIAL
ElseIf Cells(x, w).Value <> "" _
And Cells(x, v).Value = "" Then
Do
Cells(x, w).Select
Cells(x, v).Select
a = a + 1
w = w
v = w - (4 * a)
Loop Until Cells(x, v).Value <> "" Or v = 29
 
R

RussTheBear

No, the values will always be integers. The numbers are generated manually.

Basically the entire script checks values like this

1. select Column D to measure the amount of rows in order to know when to
stop because Column D is the only column where there will always be data from
top to bottom

2. make y constant as the last column in the book because we need it to
determine the location of previous columns

3. make w equal the column before the last edit column which is 3 columns
before the last column

4. make v equal the next edit column before w (4 columns before), but there
might be blank edit columns in between so we need to create a variable that
represents a multiple of the distance between each edit column (4), a
distance which remains constant (at 4)

5. look at the value of the first cell in the first column in the first row
to see the status
a. If this cell displays #N/A then do nothing and go to the next row
b. If the cell displays something besides "active" or "edited" then do
nothing and go to the next row
c. If the cell displays "active" then select the first cell in the last
edit column in the same row
i. but if this cell is blank, then select the cell in the same row in
columns prior to this (from right to left) until you find a value and if you
don't find a value before a certain point (column 29), then go to the next
row and start over
ii. if you find a value, then check this value against the value in
one more previous (4 to the left) unless 4 to the left is less than 29
- but if this is blank too, then go one more (4 columns) until you
find a value to check it against, but if there are none by the time you reach
column 29, then stop checking and go to the next row
- if you find another, then check the values versus each
other...they should not equal

6. repeat this for "edited"


Here are the areas where I believe I am having problems

'------------the definition of variables---------------
'start checking values in row 11
x = 11
'make w equal the last edit column
w = y - 3
'make v equal the next edit column before w
v = w - (4 * a)
'make a a multiple
a = 1

'------------the problem area---------------
'EDIT FOUND BUT NOT SEQUENTIAL
ElseIf Cells(x, w).Value <> "" _
And Cells(x, v).Value = "" Then
Do
Cells(x, w).Select
Cells(x, v).Select
a = a + 1
w = w
v = w - (4 * a)
Loop Until Cells(x, v).Value <> "" Or v = 29
 

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

Replacing a hardcoded date 3
Custom Fill in Listbox 2
Forcing xl2K7 Column Count back to xl2K3 size 3
Unmerge cells and fill duplicate data 5
For Eachcell in Range tranpose comma seperated values to a list in D 4
custom error messages 2
Loading pivotfields from an array 1
How to update a range of cells if cell value is equal to... 6

Top