Find the value of A1 anywhere in Column C and replace with the value of B1

[jakepenner]

Hello! I'm just trying to find the value of my A1 cell within the range of Column C. And if that value is in Column C, I want to replace that value with the value in the adjacent cell B1.

Any help would be amazing. Thanks!

 

[alansidman]

Option Explicit

Sub FindA()
    Dim i As Long, lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    Dim crit As Variant
    crit = Range("A1")
    Application.ScreenUpdating = False
    For i = 1 To lr
        If Range("C" & i) = crit Then
            Range("C" & i) = Range("B1")
        End If
    Next i
    Application.ScreenUpdating = True

End Sub

 

[Rick Rothstein]

Here is another macro that should work for you...

Sub FindAndReplace()
  [C:C].Replace [A1], [B1], xlWhole, , , False, False, False
End Sub

 

[DanteAmor]

Hi Rick, If the Within "Workbook" option is selected, your macro replaces in all the columns of the sheet and in all the sheets.

Find and Replace remembers the "Within" option, which there is no (optional) parameter for in Range.Find or Range.Replace.

If you want to absolutely guarantee that the replace is only on the Sheet in question, the trick then to reset the "Within" option to "Sheet" from "Workbook" is to do a Range.Find first, before attempting to Range.Replace.

Continuing with your example:

Sub FindAndReplace()
  Dim ws As Worksheet, rng As Range
  Set ws = ActiveSheet
  Set rng = ws.Cells.Find("*")
  ws.Range("C:C").Replace [A1], [B1], xlWhole, , , False, False, False
End Sub

 

[DanteAmor]

Another way

Sub FindReplace()
  With Range("C1", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},IF(" & .Address & "<>"""",IF(" & .Address & "=A1,B1," & .Address & "),""""))")
  End With
End Sub

 

[Rick Rothstein]

Hi Rick, If the Within "Workbook" option is selected, your macro replaces in all the columns of the sheet and in all the sheets.

I was not aware of this! Thanks for noting it for me.

Find and Replace remembers the "Within" option, which there is no (optional) parameter for in Range.Find or Range.Replace.

I hate the Find and Replace dialog boxes for two specific reasons. First, I hate the fact that the dialog box and the VB Find and VB Replace functions remember several setting from use-to-use no matter where they were set. The VB code should start from a fresh position each time and not remember things that were done in the dialog box and vice versa. The second thing I hate is that there is an "Options>>" button that toggles such a small amount of additional options... the dialog box with options displayed is so small that it should be always what is displayed and there should not be an option button at all.

Continuing with your example:

Sub FindAndReplace()
  Dim ws As Worksheet, rng As Range
  Set ws = ActiveSheet
  Set rng = ws.Cells.Find("*")
  ws.Range("C:C").Replace [A1], [B1], xlWhole, , , False, False, False
End Sub

A quick test show that you do not have to vector through a Range variable... simply referencing ActiveSheet directly appears to work...

Sub FindAndReplace()
  ActiveSheet.Cells.Find ("*")
  ActiveSheet.Columns("C").Replace [A1], [B1], xlWhole, , , False, False, False
End Sub