CODEWITHSUNDEEP
vbaexcelfindandmodify
Josie
Josie

Reputation: 71

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.

Upvotes: 0

Views: 34

Answers (2)

SJR
SJR

Reputation: 23081

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

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

You can simply use the Find method.

Also, no need to use .Activate

 Private Sub CommandButton8_Click()

 Dim Findtext As String
 Dim Replacetext As String

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

    Dim myCel As Range

    With ThisWorkbook.Sheets("Sheet 2").Cells
        Set myCel = .Find(What:=Findtext, LookAt:=xlWhole)
        myCel.Value = Replacetext
    End With

 End Sub

Upvotes: 0

Related Questions