Replace Cells in Range based on Multiple Criteria

[huskrfreak88]

Good afternoon! I've been a long time reader of this forum but first time poster. I hope I am able to contribute to others as much as some of you have contributed to me!

------------------------------------------------
I have a problem I can solve, but I am looking for a more practical/efficient method.

I have a sheet that has, for all intents and purposes, two columns: Column 1: Username and Column 2: Location (Yes, one user can be in multiple locations)

[TABLE="width: 500"]
<tbody>[TR]
[TD]username[/TD]
[TD]location[/TD]
[/TR]
[TR]
[TD]bobj[/TD]
[TD]iowa[/TD]
[/TR]
[TR]
[TD]bobj[/TD]
[TD]kansas[/TD]
[/TR]
[TR]
[TD]samp[/TD]
[TD]nebraska[/TD]
[/TR]
[TR]
[TD]samp[/TD]
[TD]nebraska[/TD]
[/TR]
[TR]
[TD]bobj[/TD]
[TD]iowa[/TD]
[/TR]
</tbody>[/TABLE]

I have VBA that starts in row 1 and sets user = cells(i, "A") and user_location = cells(i, "B")

Once that happens I count all instances of that combination and set temp_count = COUNTIF(user, Column A, user_location, Column B) and perform several calculations based on the count.

Next is where I need help - I don't want to have to loop through every row if I have already counted that combination, so I want to do a find/replace that will replace cells in Column A (username) with "" as long as the value in Column A = user and the value in Column B = user_location (same user/location combination as the row I am currently on)

Once I do the replacement, my code will travel down until the next non-blank username and repeat the process. This should save a lot of time from having to perform calculations on EACH line if the calculations were already done for the user/location combination

I know I could loop through the spreadsheet and do "if (cell 1 = user) and (cell 2 = user_location) then (cell 1 = "") but I am wondering if there is a way to do this WITHOUT having to loop, as I have several thousand lines, and several hundred files to do this on.

There is a lot more going on in my macro, but I think I've provided the relevant information to look for a solution. Please let me know if you need more information or more clarity.

 

[davesexcel]

You can create a collection first then loop through the collection and loop through column A, when the condition is met, then do something.

This example just combines column A and Column B into Column C, you will change that to do whatever you want to happen.

Sub Button1_Click()
    Dim cUnique As Collection
    Dim Rng As Range, c As Range
    Dim Cell As Range
    Dim sh As Worksheet
    Dim vNum As Variant

    Set sh = ThisWorkbook.Sheets("Sheet1")
    Set Rng = sh.Range("A2", sh.Range("A2").End(xlDown))
    Set cUnique = New Collection

    On Error Resume Next
    For Each Cell In Rng.Cells
        cUnique.Add Cell.Value, CStr(Cell.Value)
    Next Cell
    On Error GoTo 0

    For Each vNum In cUnique
        For Each c In Rng.Cells
            If c = vNum Then
                '---Your code goes here
                c.Offset(, 2) = c & "-" & c.Offset(, 1)    'whatever you want to do with it.
            End If
        Next c
    Next vNum

End Sub

 

[huskrfreak88]

Excellent!!! Good solution, thank you!

I finally came up with a similar solution this morning. I am using a FindAll function from FindAll VBA Function to create a range of cells where the name matches, then looks at each one of the cells in the range to identify if the Column B value in the same row is a match - if so, execute code - if not, move on!

Thank you again, I really appreciate your response.