Error when matching records

Question

Problem :

Code returns 0 matches.

Code :

Sub searchNames()
Dim loc As String

Call location(loc)

Dim loadWb As Workbook
Dim loadWs As Worksheet

' ~~ Load file location
Set loadWb = Workbooks.Open(loc)
Set loadWs = loadWb.Sheets("Sheet1")

' ~~ Init rows in loaded excel
Dim lrow As Long
With loadWs
    ' ~~ Set range for lookup value
    lrow = .Range("G" & .rows.Count).End(xlUp).Row
End With

' ~~ Loop to remove trailing spaces
Dim TrimCounter As String
Dim NewString As String
For ind = 2 To lrow
    ' ~~ Set rows for trim
    TrimCounter = loadWs.Range("G" & ind).Value
    NewString = Trim(TrimCounter)
    ' ~ Write trimmed values
    loadWs.Range("G" & ind).Value = NewString
Next ind

' ~~ Set output worksheet
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("ALL BRANDS")

Dim lrowWs As Long
With ws
    lrowWs = .Range("D" & .rows.Count).End(xlUp).Row
End With


Dim counter As Long
Dim rows As Long
Dim nameCounter As String
counter = 0
' ~~ Get controlPointNumber in ALL BRANDS
For ind = 2 To lrowWs
    ' ~~ Set controlPointNumber
    nameCounter = ws.Range("D" & ind).Value
    ' ~~ Start with row 2 in loaded Excel to omit header
    For ind2 = 2 To lrow
        ' ~~ Check if the name matches in ALL BRANDS
        If loadWs.Range("G" & ind2).Value = nameCounter Then
            counter = counter + 1
        End If
    Next ind2
    ' ~~ Write the value in Worksheet 'ALL BRANDS' equal to the results
    ws.Range("L" & ind).Value = counter
    ' ~~ Init counter to 0 and check other controlPointNumber
    counter = 0
    rows = rows + 1
Next ind
' ~~ Close workbook ~ Byeee
loadWb.Close False
MsgBox "Scan finished! Scanned " & rows & " rows"
End Sub

Screenshots :

Am I missing something? Any ideas?

EDIT:

Problem located. There are spaces in the values in COLUMN G

Answer 1

I always worked with the .find Method. For me it's easier and if you combined it with a dictionary you can do the whole range and can be sure that no Value will be missing. The code will take the range with values from column A and will count how often the value appears in the range. Hope the code can help you.

    Sub Makro1()
'Excel objects.
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rngLockin As Range
    Dim rngFind As Range

    Dim idx As Integer
    Dim idxRow As Integer
    idxRow = 2
    Dim strAddress As String

    'Initialize the Excel objects.
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Tabelle1")


    Set dicSearch = CreateObject("Scripting.Dictionary")
    LastRow = ws.UsedRange.Rows.Count
    Set rngLockin = ws.Range("A2:A22").SpecialCells(xlCellTypeConstants)

    For Each rngcell In rngLockin



        'I Value is not in dic, insert it and start counting

        If Not dicSearch.Exists(rngcell.Value) Then
            dicSearch.Add rngcell.Value, ""

            'Search the four columns for any constants.


            'Retrieve all columns that contain X. If there is at least one, begin the DO/WHILE loop.
            idx = 0
            With rngLockin
                Set rngFind = .Find(What:=rngcell.Value, LookIn:=xlValues)
                If Not rngFind Is Nothing Then
                    strAddress = rngFind.Address
                    idx = idx + 1
                    rngFind.Select
                    'Unhide the column, and then find the next X.
                    Do
                        rngFind.EntireColumn.Hidden = False
                        Set rngFind = .FindNext(rngFind)
                        rngFind.Select
                        If Not rngFind Is Nothing And rngFind.Address <> strAddress Then idx = idx + 1

                    Loop While Not rngFind Is Nothing And rngFind.Address <> strAddress
                End If
            End With
            Cells(idxRow, 3) = rngcell.Value
            Cells(idxRow, 4).Value = idx
            idxRow = idxRow + 1

        End If
    Next
End Sub

Fell free to ask if you have a question.

Answer 2

Change the part of the code like this:

For ind = 2 To lrowWs
    Debug.Print lrowWs

    nameCounter = ws.Range("D" & ind).value
    Debug.Print nameCounter

    For ind2 = 2 To lrow
        If loadWs.Range("G" & ind2).value = nameCounter Then
            Debug.Print loadWs.Range("G" & ind2).value
            counter = counter + 1

        End If

    Next ind2

    ws.Range("L" & ind).value = counter
    Stop
    counter = 0
    rows = rows + 1

Next ind

Then, when you reach the stop, you should have 3 different values in the immediate window. Take a good look at them, analyze them and repair the whole code correspondingly.

---

Edit: Probably the error comes from the idea, that you can use something like this:

Dim rows As Long

Thus, VBA does not know what you mean, when you say rows.Count. Long story short, change the Dim rows as Long to Dim lngRows as long and fix correspondingly everywhere.