Search columns with similar values

**vijay2482** · 02-01-2010, 03:21 PM

Hi all,
Thanks for all the previous help.
I have a issue which has to be solved.
If column 12 has value one(1) less than column 23 then
check the next rows, if column 22 in next row has the same value as column 23 in previous row then
clear the "X" in column 6 or in column 7 or in column 8 and write "?" instead of "X".
eg:
4296076M97 in column 12 and 4296076M98 in column 23 in a row then
if 4296076M98 in column 22 of the next row then
clear "X" in column 6 and write "?" in column 6.

In need of help badly.
Thanks in Advance.

**aron** · 02-01-2010, 04:20 PM

How will the numbers be entered. I see from your example that there for instance are: 5228001M1 and 4296076M98.

Will there always be either one or two digits after a letter in the end?
Will there always be a capital M there?
Could these values be of a different length than 9 or 10 characters?
If there only is one digit, is 9 one less than 0?

Would be a lot easier if some of these questions were answered.

Regards
-aron

**vijay2482** · 02-01-2010, 04:29 PM

Hi Aron,
Thanks for your reply.

The answers for your reply are as follows:
1.Will there always be either one or two digits after a letter in the end?
A.Yes

2.Will there always be a capital M there?
A. Yes

Could these values be of a different length than 9 or 10 characters?
A. No

If there only is one digit, is 9 one less than 0?
A. I dont understand this question.
There can be M1 to M8 ,M11 to M18, M91 to M98

Thanks in advance for a solution.

**aron** · 02-02-2010, 08:18 AM

By some reason the cells in column 12 that were blank were considered not empty by excel. Thats why there is a string lengthcheck in the beginning.

Ok, this is a solution to what I imagine to be the tricky part. To check if column 12 is one smaller than column 23.

Sub partSolution()
Dim str1 As String
Dim str2 As String
Dim int1 As Integer
Dim int2 As Integer

For i = 0 To 3

        str2 = Cells(5 + i, 23)
        str1 = Cells(5 + i, 12)
     If Len(str1) > 8 And Len(str2) = Len(str1) Then
        
        'gets two last charaters
        str2 = Right(str2, 2)
        str1 = Right(str1, 2)
        'replaces a possible M with a blank space
        str2 = Replace(str2, "M", " ")
        str1 = Replace(str1, "M", " ")
        'trims away blank spaces (if there was an M) and converts to a number
        int2 = Trim(str2)
        int1 = Trim(str1)

        If (int2 - int1) = 1 Then
            '
            ' Code for comparing next row and replacing X with ? and so on
            '
        End If
       
    End If
Next i

End Sub