I have been trying to find a specific instance of what I am looking for out here and just cannot find it. I have a spread sheet with 17 different Tabs. I have to put a Temporary ID into the spread sheet and those Temporary IDs can be found on all tabs. Once New IDs are created I have to replace all the Temporary IDs with the newly Created IDs. If there are not too many, I manually Find and Replace All across the workbook. My other option is to do a Vlookup 17 plus times because the newly created IDs are sometimes in different columns of each spread sheet as well.
The way my spread sheet is set up the Temporary ID is on the first sheet on column B of the 17 tab workbook and once the update is completed a workbook is created with the newly created IDs listed in the first sheet in column C. But that is the only sheet that holds the newly created IDs. I need to find and replace the temporary IDs on my 17tab sheet with the new ids created from the newly created workbook that holds the new IDs.
I have tried a bunch of different things. This was the only thing I found that sort of did what I wanted it only would update the data in the First Sheet of the newly created spread sheet. I like the code below because There may be 400 rows of data but only 100 of them are Temporary, if a new ID was created then there will be an ID in Column C. So this code checks for that and only replaces the rows that have data in both columns.
So lets say I have 17tabsheet and NewIDsheet.
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") ' This is the first sheet in the NewIDsheet
' 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
So Like I said this works to replace the Temp IDs on the first sheet of the NewIDsheet but thats it. I was thinking if I could figure out how to get it to do all the sheets then I could finagle it to use the NewIDsheet as my source and my 17Tabsheet as the current workbook.
Any help would be appreciated.
Bwomble is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.