On one sheet, column 5 is the customer number and column 6 is the new
customer number. I do a little formatting to ensure both columns are text
and the old customer numbers are 7 characters in length. If they are not 7
characters long, I add a space at the end of the old customer number. Next I
sort these 2 columns based on the old customer number. Finally, I load these
items into a 2 dimensional array. So far, so good, as all of the old
customers numbers are sorted in descending order.

Next, I move to the data sheet and and do the same same formatting and
sorting to the old customer number. Again, so far, so good as all of the old
customers numbers are sorted in descending order.

Next I run a little loop to compare the first item on the data sheet to the
first item in the array.....else

If the customer number is less than the first item in the array, I move to
the next customer number.....else

If the customer number equals the first item in the array, I add the new
customer number to the data sheet for that row and move to the next row.

If the customer number is greater than the first item in the array, I
increase the array index by 1 adn re-check the old customer number. (I'm
comparing the same record with the next item in the array.)

What's driving me crazy is the data is sorted like I expect but when it
compares the customer number to the item in the array, the lower item is
greater than the array item, so it is increasing the array index, instead of
moving to the next customer number.

For example, when I compare customer number "@AMXRWD" to the array item
"10D5671", I expect it to move to the next customer number since "10D5671"
follows "@AMXRWD" when the data is sorted.

Instead, it appears that "@AMXRWD" is greater and is increasing the array
index; which throws off the rest of the items.

Any suggestions or help on this string comparison would be greatly
appreciasted. I have include the code for the loop below.

Thanks for the help.

' cells reference is the customer number
' code (1,y) is the old customer number in the array
' code (2,y) is the new customer number in the array

r = 1
y = 1

Do Until Len(Trim(Cells(r, 1))) = 0

If Cells(r, 2) < Code20(1, y) Then
r = r + 1
Else
If Cells(r, 2) = Code20(1, y) Then
Cells(r, 3).NumberFormat = "@"
Cells(r, 3) = Code20(2, y)
r = r + 1
Else
If Cells(r, 2) Code20(1, y) Then
y = y + 1
End If
End If
End If

Loop

Hi Shaves,

You can control the string comparison
mode.

At the head of your module, before any
procedures, try inserting the statement:

Option Compare Text


For further information, see the 'Option Compare
Statement' in VBA help.



---
Regards.
Norman


"Shaves" wrote in message
...
On one sheet, column 5 is the customer number and column 6 is the new
customer number. I do a little formatting to ensure both columns are text
and the old customer numbers are 7 characters in length. If they are not
7
characters long, I add a space at the end of the old customer number.
Next I
sort these 2 columns based on the old customer number. Finally, I load
these
items into a 2 dimensional array. So far, so good, as all of the old
customers numbers are sorted in descending order.

Next, I move to the data sheet and and do the same same formatting and
sorting to the old customer number. Again, so far, so good as all of the
old
customers numbers are sorted in descending order.

Next I run a little loop to compare the first item on the data sheet to
the
first item in the array.....else

If the customer number is less than the first item in the array, I move to
the next customer number.....else

If the customer number equals the first item in the array, I add the new
customer number to the data sheet for that row and move to the next row.

If the customer number is greater than the first item in the array, I
increase the array index by 1 adn re-check the old customer number. (I'm
comparing the same record with the next item in the array.)

What's driving me crazy is the data is sorted like I expect but when it
compares the customer number to the item in the array, the lower item is
greater than the array item, so it is increasing the array index, instead
of
moving to the next customer number.

For example, when I compare customer number "@AMXRWD" to the array item
"10D5671", I expect it to move to the next customer number since "10D5671"
follows "@AMXRWD" when the data is sorted.

Instead, it appears that "@AMXRWD" is greater and is increasing the array
index; which throws off the rest of the items.

Any suggestions or help on this string comparison would be greatly
appreciasted. I have include the code for the loop below.

Thanks for the help.

' cells reference is the customer number
' code (1,y) is the old customer number in the array
' code (2,y) is the new customer number in the array

r = 1
y = 1

Do Until Len(Trim(Cells(r, 1))) = 0

If Cells(r, 2) < Code20(1, y) Then
r = r + 1
Else
If Cells(r, 2) = Code20(1, y) Then
Cells(r, 3).NumberFormat = "@"
Cells(r, 3) = Code20(2, y)
r = r + 1
Else
If Cells(r, 2) Code20(1, y) Then
y = y + 1
End If
End If
End If

Loop