Search And Replace - Values into Next Worksheet Offset Columns

[dj44]

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

[mdmackillop]

Not recommending this code but does it do what you're after? If so, we can look at better solution.

  Sub Search_Replace()
    Dim oData As Range, oCell As Range
    Dim i  As Long, j As Long
    Dim wsT  As Worksheet
    Dim wsR  As Worksheet
    
    Set wsT = Worksheets("Table")
    Set wsR = Worksheets("Replace")
    
    Set oData = wsT.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)
    For i = 2 To wsT.Cells(Rows.Count, "B").End(xlUp).Row
        For Each oCell In Range(wsR.Cells(2, 1), wsR.Cells(Rows.Count, 1).End(xlUp)).Cells
            If InStr(1, oCell, wsT.Cells(i, "B").Value) > 0 Then    'String to find
                wsT.Range("D" & i).Copy oCell.Offset(0, 2)
                wsT.Range("E" & i).Copy oCell.Offset(0, 5)
                wsT.Range("F" & i).Copy oCell.Offset(0, 7)
            End If
        Next oCell
    Next i
End Sub

[dj44]

Hello M good day,

What a lightning speed code - I can't believe it just happened.

It was exactly what I was trying to do but you see I'm not very good with search and replace functions.

I broke the last search and replace macro i found - I still don't know what happened to it

And I'm not good with arrays

So I need to see it from One table to the next otherwise it becomes a bit messy as you can see from my coding style.


So long as I can carefully set all my columns in the right place

and it does what it says on the tin then I am as happy as Larry

thank you for the help

[mdmackillop]

Simpler and clearer

Option Explicit
  Sub Search_Replace()
    Dim oData As Range, oCell As Range, idData As Range, c As Range
    Dim wsT  As Worksheet
    Dim wsR  As Worksheet
    
    Set wsT = Worksheets("Table")
    Set wsR = Worksheets("Replace")
    
    Set oData = wsT.Range("B:B").Cells.SpecialCells(xlCellTypeConstants)
    Set idData = wsR.Range("A:A").Cells.SpecialCells(xlCellTypeConstants)


    For Each oCell In oData.Cells
        Set c = idData.Find(oCell.Value)
        If Not c Is Nothing Then
            c.Offset(, 2) = oCell.Offset(, 2)
            c.Offset(, 5) = oCell.Offset(, 3)
            c.Offset(, 7) = oCell.Offset(, 4)
        End If
    Next oCell
    
End Sub

[dj44]

Thank you M,

I can always do with another way to do a task.

Because I'm really accident prone to breaking it.

Many a time my Excel has gone into a loop that I couldn't break out of so I got to be careful and annotate all my stuff and prevent my Excel from crashing my worksheet.

Initially I started off with a search and replace table and I thought that I might do an offset on that but that was a disaster because it was searching for the string and replacing the string that I didn't want to replace

all is well now with this search and replace table

Thank you very much and great evening to you