-1

As I could not find anyone posting something like this, I make this question.

I have some data that is "dirty" and I need to clean it. In the picture in the link you can see a list of cars Column A is correct but the rest is wrong

I would need a code that could search Column A for information and then input the correct information into column F, G, H, I in similar way as shown in the picture

The dataset would be over 10000 lines

If the code could be customizeable also so that we could add more criterias that would be very helpfull

Thank you so much for replies.

Regards

Table in question

0m3r
  • 12,286
  • 15
  • 35
  • 71
Eirik Farstad
  • 1

1 Answers1

0

If I understand your problem correctly, you're trying to pull the "Petrol" or "Diesel" and put those in the second column, "LCV" or "Passenger Car" for the third column, and "Manual" and "Automatic" in the third column. If that is the case, then you could loop until the last value to go through everything, and then you could use the InStr value to see if the value is in the string, like they did here.

Sub CheckEntries()

Dim i As Integer
Dim iend As Integer
Dim rng As Range
Dim str As String
Set rng = 'Put the range you are trying to do test
iend = rng.Rows.Count

' This loops until it makes it to all values
For i = 1 To iend
    str = rng.Cells(i, 1).Value

    If InStr(0, str, "Petrol") > 0 Then
        rng.Cells(i, 2).Value = "Petrol"
    ElseIf InStr(0, str, "Diesel") > 0 Then
        rng.Cells(i, 2).Value = "Diesel"
    Else
        rng.Cells(i, 2).Value = ""
    End If

    If InStr(0, str, "LCV") > 0 Then
        rng.Cells(i, 2).Value = "LCV"
    ElseIf InStr(0, str, "Passenger car") > 0 Then
        rng.Cells(i, 2).Value = "Passenger car"
    Else
        rng.Cells(i, 2).Value = ""
    End If

    If InStr(0, str, "Manual") > 0 Then
        rng.Cells(i, 2).Value = "Manual"
    ElseIf InStr(0, str, "Automatic") > 0 Then
        rng.Cells(i, 2).Value = "Automatic"
    Else
        rng.Cells(i, 2).Value = ""
    End If
Next i
End Sub

This code will turn out a blank if the value doesn't include the value in the string. This should help with debugging if there is something wrong with the text in column 1.

Parker.R
  • 88
  • 8