Macro Troubleshooting---Multiple Ifs

[Wildcats23]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tests
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]410--Troubleshoot Test
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]410
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Troubleshoot Test
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]873---Water Pressure Test
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Water Pressure Test
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]873
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hello,
The problem I'm having is that I have a column (Column B) of about 30 different tests that are supposed to only be categorized as numbers...... however there has been a lot of mislabeling in my data sample. I provided a small example of what I mean in the table above. What I would like the Macro to do is filter the column so if it sees (For Example) "water pressure test" or "873---Water Test" It will replace it with just "873". I have a lot of different tests so a find and replace would take too long and I would have to continually redo it every time I update the data (weekly basis). I would like to write one big macro so I never have to do that again. THANK YOU!!!!

 

[Yongle]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

​

[/td][td]

Tests​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

​

[/td][td]

410--Troubleshoot Test​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

​

[/td][td]

410​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

​

[/td][td]

Troubleshoot Test​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

​

[/td][td]

873---Water Pressure Test​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

​

[/td][td]

Water Pressure Test​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

​

[/td][td]

873​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Data[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]

Look For​

[/td][td]

Replacement​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

410​

[/td][td]

410​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

410--Troubleshoot Test​

[/td][td]

410​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

Troubleshoot Test​

[/td][td]

410​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

873​

[/td][td]

873​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

873---Water Pressure Test​

[/td][td]

873​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

Water Pressure Test​

[/td][td]

873​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: LookUp[/td][/tr][/table]



Add worksheet with replacement lookup values
- include all values you expect to see in the column

Paste code into standard module and amend sheet names

Option Explicit

Sub ReplaceValues()
 Application.ScreenUpdating = False
    
    Dim Valu, Orig As Range, cel As Range, LookUpRng As Range, Msg As String, Ws As Worksheet
    Set LookUpRng = Sheets("Lookup").Range("A:B")
        
    With Sheets("[COLOR=#ff0000]Data[/COLOR]")
        Set Orig = .Range("[COLOR=#ff0000]B2[/COLOR]", .Range("[COLOR=#ff0000]B[/COLOR]" & Rows.Count).End(xlUp))
            For Each cel In Orig
            On Error Resume Next
                Valu = WorksheetFunction.VLookup(cel, LookUpRng, 2, 0)
                    If Err.Number > 0 Then
                        Msg = Msg & vbCr & cel
                    Else
                        cel = Valu
                    End If
                On Error GoTo 0
            Next cel
    End With
 Application.ScreenUpdating = True
 If Msg <> "" Then MsgBox Msg, vbInformation, "NOT FOUND"
End Sub

 

[Wildcats23]

That worked great, Thank you very much!!!

 

[Yongle]

thanks for the feedback