VBA Code to find/search a value and replace value in another cell same row

[hussainsaif]

Hi,

I wanted help if you can provide me i will be very thankful.
My problem is that, i want to search a value in a sheet and replace the value of a cell in the same sheet where that value is find but another columns lets say that:

sheet1
column A
hussain
farhan
simon
adam

column c
900
1000
8000
800

now i want to search e.g adam and replace 800 by 950, wherever the sheet has adam in the sheet.
Is it possible to have 2 separate cells for entry (e.g cell no. y900 for search value and z900 for value that will replace value in column c same row) fix for the value to be enter and search and the value that will replace and have a button to execute the vba code.
please help me.

 

[CMSREPORTS]

Fluff: I am using your code for a similar exercise, but i am getting a "Method 'Range' of object'_Global' failed message, at the "For i="

The only difference from what i see is that I am searching column AQ and modifying column A. (Office 2013 64b)

Sub SearchReplace()
Dim i As Long
Dim Fnd As Range

Set Fnd = Range("AQ1")
For i = 1 To Application.CountIf(Range("AQ:AQ"), Range("Cancelled").Value)
Set Fnd = Range("AQ:AQ").Find(Range("Cancelled").Value, Fnd, , xlWhole, , , False, , False)
Fnd.Offset(, -42).Value = Range("To-Be-Deleted").Value
Next i
End Sub

Thank you in advance

 

[Fluff]

Do you have a named range on the active sheet called Cancelled?

 

[CMSREPORTS]

Do you have a named range on the active sheet called Cancelled?

Thank you for the quick reply.
No, I do not have any named ranges.

I have column AQ with the values "Active" and "Cancelled"
and I have Column A with a Document #.

What I am looking to do is: When a cell in Column AQ = "Cancelled" I want to modify the Data in the corresponding cell in Column A and change the value to "To-Be-Deleted"
Otherwise, do nothing.

so if AQ300="Cancelled" then cell A300 should be changed to "To-Be-Deleted"

 

[Fluff]

In that case try

Sub SearchReplace()
Dim i As Long
Dim Fnd As Range

Set Fnd = Range("AQ1")
For i = 1 To Application.CountIf(Range("AQ:AQ"), "Cancelled")
Set Fnd = Range("AQ:AQ").Find(Range("Cancelled").Value, Fnd, , xlWhole, , , False, , False)
Fnd.Offset(, -42).Value = "To-Be-Deleted"
Next i
End Sub

 

[CMSREPORTS]

In that case try

Sub SearchReplace()
Dim i As Long
Dim Fnd As Range

Set Fnd = Range("AQ1")
For i = 1 To Application.CountIf(Range("AQ:AQ"), "Cancelled")
Set Fnd = Range("AQ:AQ").Find(Range("Cancelled").Value, Fnd, , xlWhole, , , False, , False)
Fnd.Offset(, -42).Value = "To-Be-Deleted"
Next i
End Sub

Strange. I see what you are doing, and it makes sense, but i get the same error.

 

[Fluff]

Forgot to change one bit

Sub SearchReplace()
Dim i As Long
Dim Fnd As Range

Set Fnd = Range("AQ1")
For i = 1 To Application.CountIf(Range("AQ:AQ"), "Cancelled")
Set Fnd = Range("AQ:AQ").Find("Cancelled", Fnd, , xlWhole, , , False, , False)
Fnd.Offset(, -42).Value = "To-Be-Deleted"
Next i
End Sub

 

[CMSREPORTS]

Forgot to change one bit

Sub SearchReplace()
Dim i As Long
Dim Fnd As Range

Set Fnd = Range("AQ1")
For i = 1 To Application.CountIf(Range("AQ:AQ"), "Cancelled")
Set Fnd = Range("AQ:AQ").Find("Cancelled", Fnd, , xlWhole, , , False, , False)
Fnd.Offset(, -42).Value = "To-Be-Deleted"
Next i
End Sub

wow that was super fast. 1800 records in <1 second.

I appreciate the help.

 

[Fluff]

You're welcome & thanks for the feedback.