CODEWITHSUNDEEP
vbaexcel
Rookie2145
Rookie2145

Reputation: 37

Excel vba Comparing 2 Alphanumeric Strings

I've written some code to compare two strings in the same worksheet. The code should check to see if the string in column b appears anywhere in column a. If the string is present in column a then a value of 'yes' should be returned. If the string isn't present then 'no' should be returned. An example of the strings would be 'SR03SN56' The SR and SN will always be present, only the numerical values will change. The code I have written is always returning 'Yes' or 'No' irrespective of whether the string is present in column a or not. I can't work out why although I have a feeling it's my use of the strcmp function. Any help is much appreciated.

Public Sub NameLater()
Dim colA As String, colB As String
Dim LastRowA As Integer, LastRowB As Integer, i As Integer, j As Integer
Dim Comparison As Integer
Dim Result As String

With ActiveSheet
        LastRowA = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
With ActiveSheet
        LastRowB = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With


For i = 1 To LastRowA
    For j = 2 To LastRowB



            colA = Range("A" & i).Value
            colB = Range("B" & j).Value

            Comparison = StrComp(colA, colB, 1)

            If Comparison = 1 Then Result = "Yes"
            If Comparison = 0 Then Result = "No"
            If Comparison = -1 Then Result = "No"

        Sheet2.Range("H" & j).Value = Result

        Next j

    Next i

End Sub

Upvotes: 0

Views: 608

Answers (1)

nicolò grando
nicolò grando

Reputation: 407

I suggest you to use vlookup instead of VBA:

=IF(IFERROR(VLOOKUP(B1,A:A,1,false),0)=0,"No","Yes")

For every cell of column b you can find if exist in column a. is much easier.

Upvotes: 1

Related Questions