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.

 

[Learn Excel in Tamil]

Hi

Please try with this coding, hope this will help

Sub Test()


Dim Temptext As String
Dim Tempvalue As String
Worksheets("Sheet2").Select
Range("A2").Select


Worksheets("Sheet1").Select
Range("A2").Select
Temptext = ActiveCell.Value
Tempvalue = ActiveCell.Offset(0, 2).Value
Worksheets("Sheet2").Select
Do While ActiveCell.Value <> ""
    Do While ActiveCell.Value <> ""
    If ActiveCell.Value = Temptext Then
    ActiveCell.Offset(0, 2).Value = Tempvalue
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    Range("A2").Select
Worksheets("Sheet1").Select
If ActiveCell.Offset(1, 0).Value = "" Then
Range("A1").Select
MsgBox "Macro Completed", vbExclamation
Exit Sub
Else
ActiveCell.Offset(1, 0).Select
End If
Temptext = ActiveCell.Value
Tempvalue = ActiveCell.Offset(0, 2).Value
Worksheets("Sheet2").Select
Loop


End Sub

 

[hussainsaif]

Hello,
Your code is for 2 worksheets, i asked for one worksheet and finding a value and replacing the value of a cell in the same row but different column.

Regards
Hussain

Hi

Please try with this coding, hope this will help

Sub Test()


Dim Temptext As String
Dim Tempvalue As String
Worksheets("Sheet2").Select
Range("A2").Select


Worksheets("Sheet1").Select
Range("A2").Select
Temptext = ActiveCell.Value
Tempvalue = ActiveCell.Offset(0, 2).Value
Worksheets("Sheet2").Select
Do While ActiveCell.Value <> ""
    Do While ActiveCell.Value <> ""
    If ActiveCell.Value = Temptext Then
    ActiveCell.Offset(0, 2).Value = Tempvalue
    End If
    ActiveCell.Offset(1, 0).Select
    Loop
    Range("A2").Select
Worksheets("Sheet1").Select
If ActiveCell.Offset(1, 0).Value = "" Then
Range("A1").Select
MsgBox "Macro Completed", vbExclamation
Exit Sub
Else
ActiveCell.Offset(1, 0).Select
End If
Temptext = ActiveCell.Value
Tempvalue = ActiveCell.Offset(0, 2).Value
Worksheets("Sheet2").Select
Loop


End Sub

 

[Fluff]

Another option

Sub SearchReplace()
   Dim i As Long
   Dim Fnd As Range
      
   Set Fnd = Range("A1")
   For i = 1 To Application.CountIf(Range("A:A"), Range("Y900").Value)
     Set Fnd = Range("A:A").Find(Range("Y1").Value, Fnd, , xlWhole, , , False, , False)
     Fnd.Offset(, 2).Value = Range("Z900").Value
   Next i
End Sub

 

[hussainsaif]

Fluff,
Where shall i enter the value to be find and the value to be replaced, i mean in your where i shall enter the value that is to be find in column A and where to enter the value that is to be replaced in column C

Another option

Sub SearchReplace()
   Dim i As Long
   Dim Fnd As Range
      
   Set Fnd = Range("A1")
   For i = 1 To Application.CountIf(Range("A:A"), Range("Y900").Value)
     Set Fnd = Range("A:A").Find(Range("Y1").Value, Fnd, , xlWhole, , , False, , False)
     Fnd.Offset(, 2).Value = Range("Z900").Value
   Next i
End Sub

 

[Fluff]

In your op you said

y900 for search value and z900 for value that will replace value in column c

So that is what I've done.
If you enter the search value in cell Y900 & the replace value in cell Z900.
That can easily be changed in needed.

 

[hussainsaif]

Thanks alot Fluff, you have provided the exact code i required, just a minor modification, in your code you have mentioned a reference of cell "Y1", which i think is not required, therefore i changed your code to this:

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

Set Fnd = Range("A1")
For i = 1 To Application.CountIf(Range("A:A"), Range("Y900").Value)
Set Fnd = Range("A:A").Find(Range("Y900").Value, Fnd, , xlWhole, , , False, , False)
Fnd.Offset(, 2).Value = Range("Z900").Value
Next i
End Sub

Please check Fluff is it alright now.

And one last thing, how do i put a shortcut or a button in the same excel sheet beside the search cells to run this code from there directly.

Thank you once again!

Regards
Hussain

In your op you saidSo that is what I've done.
If you enter the search value in cell Y900 & the replace value in cell Z900.
That can easily be changed in needed.

 

[Fluff]

The change you've made is fine, when testing I used Y1 & Z1 for ease & forgot to change one of them back.
Have a look here for details on how to use a button to run a macro
http://www.contextures.com/excel-vba-worksheet-macro-buttons.html

 

[hussainsaif]

Thanks Fluff. You've made my work heaven

Best Regards
Hussain

The change you've made is fine, when testing I used Y1 & Z1 for ease & forgot to change one of them back.
Have a look here for details on how to use a button to run a macro
http://www.contextures.com/excel-vba-worksheet-macro-buttons.html

 

[Fluff]

Glad to help & thanks for the feedback