Cleaning up records

[jwburritt]

Hello: I have part of a macro that needs to clean up cells and replace the bad data with the good data. I'm using a loop nested if else, but is there a more efficient way? This looks cumbersome. Any help would be appreciated . Thank you!

Sub cleandata()    

    Dim i As Long
    Dim DataValue As Variant
    Dim LastRow As Long
    Dim StartRow As Long

    StartRow = 7
    LastRow = Cells(StartRow, 6).End(xlDown).Row

    For i = StartRow To LastRow

    DataValue = Cells(i, 6).Value

        If InStr(1, Cells(i, 6), "Apples Today") = 1 Then
            Cells(i, 6) = "Apple"
            Else
            If InStr(1, Cells(i, 6), "Oranges Not Good") = 1 Then
                Cells(i, 6) = "Oranges"
                Else
                If InStr(1, Cells(i, 6), "Blue") = 1 Then
                    Cells(i, 6) = "Blueberries"
                    Else
                    If InStr(1, Cells(i, 6), "Apples Tomorrow") = 1 Then
                        Cells(i, 6) = "Apples plus 2"
                        Else
                        If InStr(1, Cells(i, 6), "Fruit") = 1 Then
                            Cells(i, 6) = "Fruit Stock"
                            Else
                            If InStr(1, Cells(i, 6), "Bananas") = 1 Then
                                Cells(i, 6) = "Ban"
                                Else
                            End If
                        End If
                    End If
                End If
            End If
        End If
    Next i

End Sub

 

[svendiamond]

Make a separate sheet that has all the ones you want to translate. Like column A would have all the bad data like "Apples Today" and "Oranges Not Good" etc etc. Then column B would have the "corrected" versions. Then just loop through your sheet and lookup each value in your new Column A of bad values. And if it matches then change it to what's in column B. Does that make sense?

 

[jwburritt]

Thank you. I thought of that earlier, but the sheet I'm manipulating the raw data in is only temporary. Once the data is cleaned and formulated properly, I'll copy all the data to the target sheet and close the raw data file without saving. But that's also when my exception list was one or two. Now, it's five or so, and may get bigger. Plus, I didn't want to maintain another sheet (ugh). But, it looks like your idea is probably the best -- and unavoidable.

Thanks again!

 

[Fluff]

Another option would be

Sub jwburrit()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Apples Today*", "Apple", "Oranges Not Good*", "Oranges", "Blue*", "Blueberries")
   For i = 0 To UBound(Ary) Step 2
      Range("A:A").Replace Ary(i), Ary(i + 1), xlWhole, , False, , False, False
   Next i
End Sub

Just add the rest of the values to the array. Although if the values are likely to change, I would use a list on a sheet somewhere as suggested by svendiamond.

 

[jwburritt]

Thank you!

 

[Fluff]

You're welcome