I have an excel spreadsheet that I am trying to remove a substring from the cell and insert into an adjacent cell - mimicking the "Insert Copied Cells" feature in Excel. Problem is that when I try to utilize Replace it gives me "Expected =" as an error. I realize with the current code I would only be able to remove the substring, but I can try to figure out the cut and insert method myself first. Any tips or help would be greatly appreciated.
Code:
Sub fixSheet()
Dim lRow As Integer, rng As Integer, char As Integer, str As String, totstr As String
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Worksheets(1).Range("A1:A" & lRow).Select
word = "KB-"
For Each cell In Selection
char = InStr(cell.Value, word)
If char <> 0 Then
str = Mid(cell.Value, char)
totstr = ", " & str
Replace(cell.value,totstr,"")
char = 0
str = ""
End If
Next cell
End Sub