Forum Discussion

JamesPhImp's avatar
JamesPhImp
Copper Contributor
Jul 27, 2023

VBA code to find and replace in each row based on cell value

Hi I need a VBA code that will cycle through a sheet and do the following.

highlight the row and do a find and replace for that row, find the value in column B and replace with the value in column C for that row. It will then cycle through the sheet (as long as there is a value in column A).
For example:

 

it will take row 651 find and replace 230710 with 230711 for that row, it will then go to row 652 and replace 230711 with 230712 for that row and so on until no value in column A..

 

  • JamesPhImp's avatar
    JamesPhImp
    Copper Contributor
    Hi thank you for the suggestion but neither work. I am not looking for it to replace the value in column B, it need to search the row for the values in column b and replace with the value from column c. I have formulas in other cells in the row that need updating with the data
    • HansVogelaar's avatar
      HansVogelaar
      MVP

      JamesPhImp 

      I don't understand. In the first post, you wrote "find the value in column B and replace with the value in column C for that row". I took that literally, but apparently you want something else. Please explain in detail and very clearly what you do want instead.

      • JamesPhImp's avatar
        JamesPhImp
        Copper Contributor

        HansVogelaar effectively need it to do this:

        and so on for each row..

         

        there are formulas in rows e onwards that contain the text so need it updated.

  • JamesPhImp 

    No need to loop through the rows. You can do it in one go:

    Sub C2B()
        Dim LastRow As Long
        Application.ScreenUpdating = False
        LastRow = Range("A" & Rows.Count).End(xlUp).Row
        Range("B2:B" & LastRow).Value = Range("C2:C" & LastRow).Value
        Application.ScreenUpdating = True
    End Sub
  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor

    Hi JamesPhImp,

    You can try this script, let me explain what the script should do.

    The script is designed to cycle through a specific worksheet in the Excel workbook and perform a find and replace operation in each row based on the values in columns B and C.

    It will look for non-empty cells in both columns B and C, and if found, it will highlight the entire row, replace the value in column B with the value in column C for that row, and then move on to the next row.
    The script will continue this process until there is no value in column A, indicating the end of the data.

     

    Sub FindAndReplaceInEachRow()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet to work with
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Replace "Sheet1" with your sheet name
    
    ' Find the last row with data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each row and perform find and replace
    For i = 1 To lastRow
    ' Check if there is a value in both column B and C for the current row
    If Not IsEmpty(ws.Cells(i, "B").Value) And Not IsEmpty(ws.Cells(i, "C").Value) Then
    ' Highlight the entire row to indicate that the operation is being performed
    ws.Rows(i).Interior.Color = RGB(255, 255, 0) ' Yellow color, change as needed
    
    ' Perform the find and replace for column B and C of the current row
    ws.Cells(i, "B").Value = ws.Cells(i, "C").Value
    End If
    Next i
    
    ' Clear the highlighting after the process is done
    ws.Cells.Interior.ColorIndex = xlNone
    End Sub



    You can use the script by following these steps in Excel:
    1. Open your Excel workbook.
    2. Press "ALT + F11" to open the Visual Basic for Applications (VBA) editor.
    3. Go to "Insert" > "Module" to add a new module.
    4. Paste the code into the module.
    5. Close the VBA editor.

    6. Press "ALT + F8" to open the "Macro" dialog box.
    7. Select "FindAndReplaceInEachRow" and click "Run."

    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic

Resources