dj44
05-29-2017, 10:13 AM
folks,
good day.
I am trying to make a search and replace table with an offset
well after wrestling all morning i cant seem to fix it any more
Sub Search_Replace()
Dim oData As Range, oCell As Range
Dim i As Long, j As Long
Dim ws As Worksheet
Set oData = ActiveSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
i = 1
Set ws = Worksheets("Table")
For i = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For Each oCell In ws.Cells(i, "B").Value
If InStr(1, oCell, ws.Cells(i, "B").Value) > 0 Then 'String to find
oCell.Offset(0, 2).Copy Worksheets("Replace").Range("C" & i)
i = i + 1
' . . . and then stuck
End If
Next i
End Sub
i lost my bearing and the logic a few hours ago - it doesnt make sense any more.
In the worksheet it makes sense - attached
Can some one please take a look a this and see if this is possible? I am not sure if i did it the right way now
good day.
I am trying to make a search and replace table with an offset
well after wrestling all morning i cant seem to fix it any more
Sub Search_Replace()
Dim oData As Range, oCell As Range
Dim i As Long, j As Long
Dim ws As Worksheet
Set oData = ActiveSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
i = 1
Set ws = Worksheets("Table")
For i = 2 To ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
For Each oCell In ws.Cells(i, "B").Value
If InStr(1, oCell, ws.Cells(i, "B").Value) > 0 Then 'String to find
oCell.Offset(0, 2).Copy Worksheets("Replace").Range("C" & i)
i = i + 1
' . . . and then stuck
End If
Next i
End Sub
i lost my bearing and the logic a few hours ago - it doesnt make sense any more.
In the worksheet it makes sense - attached
Can some one please take a look a this and see if this is possible? I am not sure if i did it the right way now