[SOLVED] Finding cell and replacing with different value

Finding cell and replacing with different value

I am writing a spreadsheet to record queries, once the query has been dealt with want to change the status of the entry to closed.

Currently, on Sheet 1 the user can type the code of the query in and select to close it. I then want to search through Sheet 2 which stores all the records and change the query code to closed by adding a C at the end.

 Private Sub CommandButton8_Click()
 Dim Findtext As String
 Dim Replacetext As String

 ThisWorkbook.Sheets("Sheet 1").Activate

 Findtext = Sheets("Sheet 1").Range("C25").Value
 Replacetext = Sheets("Sheet 1").Range("E25").Value


 ThisWorkbook.Sheets("Sheet 2").Activate

 Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
 ReplaceFormat:=False     
 End Sub

C25 is the code typed by the user and E2 is currently CONCATENATE(C25,"C") At the moment, the changes are being made on sheet 1 and not sheet 2, I have limit experience with VBA so assume I must be missing something out but not sure what it is.

Solution

Try this. You don't need to activate the sheets, just add sheet references as below.

Private Sub CommandButton8_Click()

Dim Findtext As String
Dim Replacetext As String

With Sheets("Sheet 1")
    Findtext = .Range("C25").Value
    Replacetext = .Range("E25").Value
End With

Sheets("Sheet 2").Cells.Replace What:=Findtext, Replacement:=Replacetext, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

End Sub