Return entries with specific criteria met in VBA-CodePudding

I'm trying to grab certain values based on whether or not they meet a criteria in a different column. Normally if this was in the cell-space, it would be an index, but I'm not sure how this translates so easily.

Essentially, I want the if loop to scan a range that contains names of accounts, and if that account name is found, to grab its associated values (deposits, withdrawals, etc) and display them in a list.

Here is what I had so far (names changed of course):

If AccountomboBox1.Value = "Anderson" Then
    Dim x As Range
    Dim xx As Range
    
    With Sheets("MISC")
        For Each xx In .Range("I3", .Range("I" & Rows.Count).End(xlUp))
            If xx.Value = "Anderson" Then
                For Each x In .Range("J3", .Range("J" & Rows.Count).End(xlUp))
                    If x.Value <> "" Then
                        ActivityComboBox1.AddItem x.Value
                    End If
                Next x
            End If
        Next xx
    End With End If

Where I3 is the list of the account names and J3 is its balances.

From what I can gather, the code currently is only validating that the Anderson value does exist before grabbing all of the balances for all accounts. But I want it to only grab the Anderson values.

Any help would be greatly appreciated.

CodePudding user response：

The primary issue in your code is that you are starting a second loop that doesn't care about the previous loop's IF condition, so it's just grabbing everything. You can fix this by using a row counter to loop (instead of a For Each), removing the second loop, and then combining your IF statements:

Dim i as Long

ActivityComboBox1.Clear 'Clear current list
With Sheets("MISC")
    For i = 3 to .Range("I" & Rows.Count).End(xlUp).Row
        If .Cells(i, "I").Value = AccountComboBox1.Value And .Cells(i, "J").Value <> "" Then
            ActivityComboBox1.AddItem .Cells(i, "J").Value
        End If
    Next i
End With