Find & Replace unknown values

[MichaelRSnow]

Hi, can anyone help, i'm trying to get sheet 2 (Slave Fund Numbers column A to replace all numbers found from Sheet 1 column D with their replacing numbers Sheet 1 column E

e.g. D2 is 1, E2 is 3, Sheet 2 Column A has 30 values of 1 = replace those 30 1's with new number 3. The code below only changes the first one found and not all of them? Help!!!


Private Sub CommandButton2_Click()
Dim i As Long
Dim LastRow As Long
Dim sh As Worksheet
Dim cell As Range

Set sh = Worksheets("Slave Fund Numbers")

With ActiveSheet

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
For i = 2 To LastRow

Set cell = Nothing
On Error Resume Next
Set cell = sh.Columns("A").Find(.Cells(i, "D").Value)
On Error GoTo 0
If Not cell Is Nothing Then cell.Value = .Cells(i, "E").Value
Next i
End With

End Sub

 

[Domenic]

Try...

Private Sub CommandButton2_Click()

    Dim i As Long
    Dim LastRow As Long
    Dim sh As Worksheet
    
    Set sh = Worksheets("Slave Fund Numbers")
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        For i = 2 To LastRow
            sh.Columns("A").Replace what:=.Cells(i, "D"), replacement:=.Cells(i, "E"), _
                LookAt:=xlWhole, searchorder:=xlByColumns, MatchCase:=False
        Next i
    End With
    
End Sub

 

[MichaelRSnow]

Thanks Mate, works great. 1 small thing if Column E is blank, no value it replaces it finds D in column A and replaces this with blank.

Any way i can get this to ignore blank cells in column E?

 

[Domenic]

Thanks Mate, works great.

You're welcome!

Any way i can get this to ignore blank cells in column E?

Try...

Private Sub CommandButton2_Click()

    Dim i As Long
    Dim LastRow As Long
    Dim sh As Worksheet
    
    Set sh = Worksheets("Slave Fund Numbers")
    
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        For i = 2 To LastRow
            If .Cells(i, "E").Value <> "" Then
                sh.Columns("A").Replace what:=.Cells(i, "D"), replacement:=.Cells(i, "E"), _
                    LookAt:=xlWhole, searchorder:=xlByColumns, MatchCase:=False
            End If
        Next i
    End With
    
End Sub

 

[MichaelRSnow]

That has worked perfectly, one more question while your helping me out, slightly different to the last.

I have this code...that moves data from 1 sheet to another putting it in the next empty row!

Private Sub CommandButton1_Click()
Sheets("Scenario Options").Range("Z1:Z10").Copy Destination:=Sheets("Scenario Log").Range("A500").End(xlUp).Offset(1, 0)
End Sub

But I'm having a little trouble trying to get this to copy and paste values as some of the cells have fomulas in and this ends up as #REF being copied?

 

[Domenic]

Try...

Private Sub CommandButton1_Click()
    Sheets("Scenario Options").Range("Z1:Z10").Copy
    Sheets("Scenario Log").Range("A500").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
End Sub

 

[MichaelRSnow]

As per your last post, spot on. Cheers Dominic