Select and Replace help needed

[igrock]

Hi everyone

I need help with the below macro. The first part of the macro selects and copies to clipboard 15 cells in one worksheet then pastes them into another worksheet, finds the value "888" and replaces it with the value "999". Then the second part part of the macro repeats the process from pasting 15 cells starting from row 16.
I don't have a problem with the first value "888" because it is static and does not change but the second value "999" has to be pulled from another worksheet each time from the next cell down -- A1, A2, A3, etc. And this needs to be on a loop until there is no more values in the A column.

Does anyone know how to do this? Thanks for your help!




Sheets("Sheet1").Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Paste
Selection.Replace What:="888", Replacement:="999", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ActiveCell.Offset(15, 0).Range("A1").Select
ActiveSheet.Paste
Selection.Replace What:="888", Replacement:="999", LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

 

[Andrew Poulsom]

Welcome to MrExcel.

Assuming that Sheet3 is the name of the worksheet that contains the replacement try the untested:

    Sheets("Sheet1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Selection.Replace What:="888", Replacement:="999", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Dim LastRow As Long
    Dim r As Long
    With Worksheets("Sheet3")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For r = 2 To LastRow
            ActiveCell.Offset(15, 0).Range("A1").Select
            ActiveSheet.Paste
            Selection.Replace What:="888", Replacement:=.Range("A" & r).Value, LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next r
    End With

 

[Phanmore]

duplicate

 

[Andrew Poulsom]

duplicate

What does that mean? igrock has only one post to date.

 

[Phanmore]

sorry, i edited my post as I answered the same question but didn't see you post until after. I should have been more clear :D

 

[igrock]

Thank you, Andrew!!! you are a genius. Deleted the first unnecessary part, edited the names of the sheet and it works like a dream. Thank you!

Welcome to MrExcel.

Assuming that Sheet3 is the name of the worksheet that contains the replacement try the untested:

    Sheets("Sheet1").Select
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Selection.Replace What:="888", Replacement:="999", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Dim LastRow As Long
    Dim r As Long
    With Worksheets("Sheet3")
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        For r = 2 To LastRow
            ActiveCell.Offset(15, 0).Range("A1").Select
            ActiveSheet.Paste
            Selection.Replace What:="888", Replacement:=.Range("A" & r).Value, LookAt:=xlPart, _
                SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next r
    End With