VBA - Using criteria list select and clear or select an replace values

Question

Alright, I'm back. This time I'm trying to quickly select all of the values in a range which match values in a separate list, my first iteration will be to clear the contents of voided IDs, my second iteration will be to select those values and then replace them with corresponding new values.

I asked another question about VBA and was pointed in mentioning that I've tried to teach myself and find resources to work through these issues before but people seem to get pissed that I'm asking, if you could at least direct me to somewhere that I can learn about these matters (or even a place I can learn basic logic and have a list of usable functions without having to go through all the "How to make your first Excel VBA for some problem that nobody cares about" I would appreciate it)

Anyway I tried to watch a few videos and then hack together something but it seems pretty clear that the function they were using cannot be adapted for other uses. This is what I have at the moment:

Sub FilterElim()
    finalRow = Range("g2").End(xlDown).Row
    Range("A1").ClearContents _
        Action:= xlClearContents, _
        CriteriaRange: Range("Sheet4!B1:B10"), _
        Unique:= False
End Sub

So based on some helpful questions I am making an edit to include an example and desired end

Example set:

Desired end result:

I presume I may need to perform a selection of some sort based on the Criteria before the ClearContents but I wasn't finding anything helpful on how to go about that. PLEASE and thank you.

| Contractor   ID | Cont Name         | Proj 1 | Proj 2 | Proj 3 |   |   | Old ID | Reconciliation |
|-----------------|-------------------|--------|--------|--------|---|---|--------|----------------|
| C1001           | Boba Fet          | P1120  |        |        |   |   | P1001  | Void           |
| C1003           | Jules Winnfield   | P1031  | P1045  |        |   |   | P1002  | P1010          |
| C1002           | Dom Cobb          | P1001  |        |        |   |   | P1005  | Void           |
| C1010           | Patrick Verona    | P1020  | P1224  | P1251  |   |   | P1020  | Void           |
| C1007           | Matt Damon        | P1008  | P1005  | P1300  |   |   | P1045  | P1100          |
| C1004           | Ned Plimpton      | P1002  |        |        |   |   | P1224  | P1300          |
| C1020           | Derek Zoolander   | P1020  | P1290  |        |   |   |        |                |
| C1009           | Charles Marlow    | P1002  | P0090  |        |   |   |        |                |
| C1011           | Robert Jordan     | P1119  |        |        |   |   |        |                |
| C1015           | Perrin Aybara     | P1200  | P1224  |        |   |   |        |                |
| C1005           | Fuzzy Dunlop      | P1005  |        |        |   |   |        |                |
| C1008           | Thomas A Anderson | P1001  | P1000  |        |   |   |        |                |
|                 |                   |        |        |        |   |   |        |                |

user_10277540 · Answer

What makes you go for a VBA solution ? Hard to do much without a glance of you data and expected result.

Non VBA option:

=IFERROR(INDEX($G$2:$G$15,MATCH(A32,$F$2:$F$15,0)),B32)

For a VBA option, you can try:

Option Explicit

Sub update_id()

Dim D1 As Object: Set D1 = CreateObject("scripting.dictionary")
Dim R1 As Range: Set R1 = Range("A2:A32")
Dim R2 As Range: Set R2 = Range("E2:E15")
Dim Rtmp As Range

For Each Rtmp In R2
    D1(Rtmp.Value) = Rtmp.Offset(0, 1).Value
Next Rtmp

For Each Rtmp In R1
    If D1.exists(Rtmp.Value) Then Rtmp.Offset(0, 1) = D1(Rtmp.Value)
Next Rtmp

End Sub

Working on the following set up :

Again, without a better understanding of your data and your issue, its hard to be more precise.

VBA - Using criteria list select and clear or select an replace values

1 Answers