CODEWITHSUNDEEP
vbaexcelstring-comparison
HelloWorld
HelloWorld

Reputation: 111

How to modify this script to account for different case?

I wrote a VBA script which searches for a string in one column and returns the match in another column. The point is match an item number to it's product image by SKU. The problem is that it fails if the case of the item number and the image have different case. The script is below:

Sub Test()
    Dim NA As Long, NC As Long, v As String, I As Long, J As Long
    Dim v2 As String
    NA = Cells(Rows.Count, "A").End(xlUp).Row
    NC = Cells(Rows.Count, "C").End(xlUp).Row
    For I = 2 To NA
        v = Cells(I, "A").Value
        v2 = ""
        For J = 2 To NC
            If InStr(Cells(J, "C").Value, v) > 0 Then
                v2 = v2 & ";" & Cells(J, "C").Value
            End If
        Next J
        Cells(I, "A").Offset(0, 1).Value = Mid(v2,2)
    Next I
End Sub

How can I account for different case? Is there a method for this?

Upvotes: 1

Views: 63

Answers (2)

RubberDuck
RubberDuck

Reputation: 12728

Just use the vbTextCompare option of the Instr function.

If InStr(1, Cells(J, "C").Value, v, vbTextCompare) > 0 Then
    v2 = v2 & ";" & Cells(J, "C").Value
End If

[vbTextCompare] Performs a text comparison, based on a case-insensitive text sort order determined by your application's current culture information.

Upvotes: 1

EngJon
EngJon

Reputation: 987

Pretty simple, just cast it to lower case:

Sub Test()
    Dim NA As Long, NC As Long, v As String, I As Long, J As Long
    Dim v2 As String
    NA = Cells(Rows.Count, "A").End(xlUp).Row
    NC = Cells(Rows.Count, "C").End(xlUp).Row
    For I = 2 To NA
        v = LCase(Cells(I, "A").Value)
        v2 = ""
        For J = 2 To NC
            If InStr(LCase(Cells(J, "C").Value), v) > 0 Then
                v2 = v2 & ";" & Cells(J, "C").Value
            End If
        Next J
        Cells(I, "A").Offset(0, 1).Value = Mid(v2,2)
    Next I
End Sub

I just took your code sample and put casts to lower case on v and Cells(J, "C").Value.

I didn't test this, so I can't guarantee flawless functionality.

You can also alter your module (on top) with

Option Compare Text

That will shut down case sensitivity completely (so you wouldn't need LCase(..))

Upvotes: 1

Related Questions