On the worksheet, I need to find wherever the cell value begins '2 X 4 ...' in column L, and then replace the cell value in the same row, in column K, with '113010'. In my attempts, my code just changes the first occurrence, not all occurrences.
I want to use VBA to do this.
Any suggestions or help would be greatly appreciated!
Here's my code so far:
Code
Sub Update_Product_IDs()
Application.ScreenUpdating = False
Dim i As Integer, iCount As Integer
Dim numberOfPMDs As Integer
Dim PMD_Name As Integer
Dim worksheetName As String
Dim worksheets_in_file As Integer
worksheets_in_file = Worksheets.Count
worksheetName = Worksheets(2).Name
numberOfPMDs = worksheets_in_file - 1
'For each *.PMD imported, Find and Replace the dimensional ProdID with the TJID that will import into Stellar
For PMD_Name = 2 To worksheets_in_file
Worksheets(PMD_Name).Select
With Worksheets(PMD_Name).Range("L:L")
'Find the cell reference, go to the preceding column, and input 113010 for 2X4
i = WorksheetFunction.CountIf(Range("L:L"), "2 X 4*")
Do Until iCount = i
Columns("L:L").Select
Selection.Find(What:="2 X 4*", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Offset(0, -1).Activate
Selection.Replace What:=ActiveCell.Value, Replacement:="113010", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False
iCount = iCount + 1
Loop
End With
Cells.EntireColumn.AutoFit
Range("A1").Select
Next PMD_Name
Application.ScreenUpdating = True
End Sub