CODEWITHSUNDEEP
stringexcelvbacompare
Austin
Austin

Reputation: 1

How to compare two strings in different workbooks (Excel VBA)

To keep it simple: I need to match a userID number from one Excel Workbook to another. The idea is to see if the larger of the two documents HAS this userID number from the smaller document. If the larger document has the userID number, other data needs to be copied over to the smaller document (I know how to do all this)

My problem is that when I compare each field, my function keeps displaying my searchString (the string in the larger document) as blank. It is not picking up on the array that I have created like it picks up on the one I created in the smaller document. The code will explain better than me. I am not a programmer nor do I really know VBA, so I already know that my code is sub-par.

Whenever I test my code, I have the MsgBox function show me the strings it is comparing, for some reason, the "searchString" always shows up as blank so it is comparing my "findString" which has the data it needs, to a blank string for whatever reason. I need the MsgBox Function to show the array data from the other document, not just a blank box.

'NID Comparisson Script

Sub getUID()

'Select Appropriate cell
Range("C2").Select

'Count the number of rows that contain data in the SCCM document.
Dim rows As Integer
rows = ActiveSheet.UsedRange.rows.count

'Create Array
With Sheets("SMSReportResults(2)")
    arrData = .Range(.Cells(2, 3), .Cells(rows, 3)).Value
End With

'Declare a variable for comparing UID/NID strings
Dim findString As String

'Loop through the document and grab the UID numbers as "searchString"
For i = 1 To rows - 1
    findString = arrData(i, 1)
    Windows("NIDs.xlsx").Activate
    Dim rows2 As Integer
    rows2 = ActiveSheet.UsedRange.rows.count

    'Create second array in the NIDs Workbook
    With Sheets("Sheet1")
        arrData2 = .Range(.Cells(2, 1), .Cells(rows, 1)).Value
    End With

    'Create searchString for NIDs workbook
    Dim searchString As String

    For j = 1 To rows2 - 1
        searchString = arrData2(j, 1)

        Dim compare As Integer
        compare = StrComp(searchString, findString)
        MsgBox (seachString)
        MsgBox (findString)
        MsgBox (compare)
        ActiveCell.Offset(1, 0).Select

    Next
Next

End Sub

Upvotes: 0

Views: 4301

Answers (2)

Steven Marciano
Steven Marciano

Reputation: 120

There is probably a more efficient way of doing this, what with the nested loops, but this is basically what I believe you need to do:

Dim rows        As Integer
Dim arrData     As Variant
Dim arrData2    As Variant

rows = ThisWorkbook.ActiveSheet.UsedRange.rows.Count

With Sheets("SMSReportResults(2)")
    arrData = .Range(.Cells(2, 3), .Cells(rows, 3)).Value
End With

On Error Resume Next
    Windows("NIDs.xlsx").Activate
On Error GoTo 0

With Workbooks("NIDS.xlsx").Sheets("Sheet1")
    arrData2 = .Range(.Cells(2, 1), .Cells(rows, 1)).Value
End With

For R1 = 1 To UBound(arrData, 1)
    For C1 = 1 To UBound(arrData, 2)
        For R2 = 1 To UBound(arrData2, 1)
            For C2 = 1 To UBound(arrData2, 2)
                If arrData(R1, C1) = arrData2(R2, C2) Then

                    'Returns the value from the next column in NIDS
                    MsgBox Workbooks("NIDS.xlsx").Sheets("Sheet1").Cells(R2, C2 + 1)

                End If
            Next C2
        Next R2
    Next C1
Next R1

Upvotes: 0

dmaruca
dmaruca

Reputation: 525

Go to the top of your module and type "Option Explicit". You misspelled searchString inside "MsgBox (seachString)" and therefore created a new variable.

Upvotes: 1

Related Questions