PDA

View Full Version : [SOLVED] Search And Replace - Values into Next Worksheet Offset Columns



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

mdmackillop
05-29-2017, 10:47 AM
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
05-29-2017, 11:15 AM
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 :grinhalo:

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
05-29-2017, 11:35 AM
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
05-29-2017, 11:56 AM
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 :doh:

all is well now with this search and replace table

Thank you very much and great evening to you