Checking values in a column to change multiple row values

Question

I have a spreadsheet with a good amount of data (more than 100,000 rows with columns a-h).

I know twenty or more distinct values in Column F are wrong and I know what they should be. For example, every occurrence of "pif" should be "pig" and "coe" should be "cow", etc.

(Note that there are multiple incorrect values (i.e. multiple "pif"s) for each.)

I'm currently building a macro to go through and fix these individually, and that part works:

Sub FixColumnF()

ActiveSheet.Columns("F").Replace What:= _
        "pif", _
        Replacement:="pig", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False, SearchFormat:=False, ReplaceFormat:=False
ActiveSheet.Columns("F").Replace What:= _
        "coe", _
        Replacement:="cow", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:= _
        False, SearchFormat:=False, ReplaceFormat:=False

...

End Sub

My problem is that column A is used to keep track of errors, one of which is an incorrect value in column F. How do I erase the value in column A to indicate there is no longer an error for each row where the value in column F is fixed?

I'm extremely new to vba, so any help would be very much appreciated!

Answer 1

There are two ways that I can think of.

Way 1

Use .Find/.FindNext to loop though all the cells which have say the word "pif". In that loop not only replace the word but also edit the value in Col A. For example (Untested)

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim SearchString As String, FoundAt As String

    On Error GoTo Whoa

    Set ws = Worksheets("Sheet1")
    Set oRange = ws.Columns(5) '<~~ Column 5

    SearchString = "pif"
    NewString = "pig"

    Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        Set bCell = aCell

        '~~> Change value in Cell F
        aCell.Value = Replace(aCell.Value, SearchString, NewString)
        '~~> Change value in Cell A
        aCell.Offset(, -5).Value = "Rectified"

        Do
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                aCell.Value = Replace(aCell.Value, SearchString, NewString)
                aCell.Offset(, -5).Value = "Rectified"
            Else
                Exit Do
            End If
        Loop
    End If

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Way 2

Use Autofilter to filter Col F on the word "pif". Loop though all the cells in Col A and add the relevant message. Remove Autofilter and then run your original code (mentioned in your question) to do the replace in Col F. see THIS link on how to use Autofilter.

Answer 2

Siddharth posted his suggestions while I was testing/typing mine. My first suggestion is a simple variation of his first suggestion and offers no advantage that I can see. However, my second suggestion is different from either of his suggestions and may be appropriate.

You need to loop for each occurrence of "pif" and "coe" if you want to do something extra with each faulty value found. The code below shows how to replace every occurrence of "pif" by "pig" and then do something with column "A". If you like this technique, you would need to duplicate this code for "coe" and "cow".

Option Explicit
Sub ReplaceFaultyA()

  Dim Rng As Range

  ' It is best to avoid "ActiveSheet" in case the use has called the
  ' macro with the wrong worksheet active. Probably this is not likely
  ' in this case but I like to follow good practice even if it not not
  ' necessary
  With Worksheets("Data")

    Do While True

      ' Are you sure all your Find parameters are correct? For example,
      ' "LookAt:=xlPart" means than "pif" in the middle of a word will
      ' be replaced by "pig". "LookAt:=xlWhole" may better match your
      ' requirement. I suggest you look up the specification of Find
      ' and consider the implications of each parameter.
      Set Rng = .Columns("F").Find(What:="pif")
      If Rng Is Nothing Then
        ' No [more] occurrences of "pif" in column F
        Exit Do
      Else
        ' Occurrences of "pif" found in column F
        .Cells(Rng.Row, "F") = "pig"
        ' Amend column ""A" of Rng.Row as necessary
      End If
   Loop

  End With

End Sub

Duplication my loop and replacing "pif" and "pig" by "coe" and "cow" in the duplicate is probably the simpliest solution if there are only two replacements. However, if there are many replacements, the technique below may be a better choice.

In this code, I place the faulty values and the matching good values in arrays. With this approach, one block of replacement code can handle an indefinite number of replacements providing the action for column A is the same for each replacement.

Sub ReplaceFaultyB()

  Dim InxValue As Long
  Dim Rng As Range
  Dim ValueFaulty() As Variant
  Dim ValueGood() As Variant

  ' The code below assumes there same number of entries in both arrays.
  ' You can add extra entries to the two arrays as necessary.
  ' This assumes the action for column "A" is the same for each faulty value
  ValueFaulty = Array("pif", "coe", "appme")
  ValueGood = Array("pig", "cow", "apple")

  With Worksheets("Data")

    For InxValue = LBound(ValueFaulty) To UBound(ValueFaulty)

      Do While True
        Set Rng = .Columns("F").Find(What:=ValueFaulty(InxValue))
        If Rng Is Nothing Then
          ' No [more] occurrences of this faulty value in column F
          Exit Do
        Else
          ' Occurrences of faulty value found in column F
          .Cells(Rng.Row, "F") = ValueGood(InxValue)
          ' Amend column ""A" of Rng.Row as necessary
        End If
     Loop

   Next InxValue

  End With

End Sub