VBA Delete part of value if it contains VIN#

[LORDMARKS]

Hi All

I have cobbled together the code below to try and clean up some data. The code seems to identify the correct values but returns very strange and inconsistent results.

What I am trying to do, is look down Col W and any cell that contains "VIN#" delete the "VIN#" and 9 characters to the right, leaving the rest of the value intact. So far it locates the VIN but then seems to delete varying numbers of characters, sometimes all of them?

Sub RemoveVin()
Dim Cell As Range
Dim Rng As Range
Dim RngEnd As Range
Dim Wks As Worksheet
PreCheckConditions
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
    Set Wks = Worksheets("IEOutput")
    Set Rng = Wks.Range("W2")
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, Wks.Range(Rng, RngEnd))
For Each Cell In Rng
If (InStr(Cell.Value, "VIN#") >= 1) Then
    Cell.Value = Replace(Cell.Value, Mid(Cell.Value, InStr(Cell.Value, "VIN#"), 9), "")
End If
Next Cell
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox "All VIN# Numbers have been removed", vbInformation, "PMS Data Filter"
End Sub

I do not really understand the InStr Function as I was sent this code by a friend, so I am now at a loss. Thanks in advance as always

 

[tlowry]

Here is my take on what I think you want:

Put this code in a module of the test data you have and run FixVins().

Post the results

Sub FixVins()
    Dim cCell
    With Worksheets("IEOutput")
        For Each cCell In .Range("W1:W" & .Cells(Rows.Count, "W").End(xlUp).Row).Cells
            cCell.Value = RemoveVin(cCell.Value)
        Next cCell
    End With
End Sub
Function RemoveVin(sIn)
    On Local Error Resume Next
    RemoveVin = sIn
    RemoveVin = Trim((Replace(sIn, Mid(sIn, InStr(UCase(sIn), "VIN#"), 13), "")))
End Function

 

[LORDMARKS]

Sorry for the late reply, I though I had already posted this. It worked peerfectly thankyou.