Replacing a cell's value by a search in a different cell

[Carlos]

Hello everyone,


I have a list where I need to replace the values from one cell based on a search of a different cell, let me explain.


I have my data like:


Column B

(B1) Bill

(B2) Dave

(B3) Bob


Column C would have different data, like a date.


Column D

(D1) In

(D2) In

(D3) Out


So I want to know if there's a way, surely using a macro, where I can enter in cell A1 the name "Bob", match the position for "Bob" in Column B (B3) and replace the value in Column D next to Bob (D3) from "Out" to "In".


Initially, the idea would be to have only unique values in Column B so that the matching can always work and replace the contents in Column D and to always replace it with the same word.


Eventually, I would like to try if I can do the same but by matching more values from different cells, but if I can go with one for now it would be great!

 

[Luke M]

Right-click on the sheet tab, view code, paste this in:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
'First, we check to see if the cell we are interested
'in has been changed
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

'define our variables
Dim searchWord As String
Dim fCell As Range

searchWord = Range("A1").Value
'If A1 is blank, do nothing
If searchWord = "" Then Exit Sub

'Use the find method to locate the cell in B1 with our name
'note that this will work for partial matches as well
Set fCell = Range("B:B").Find(searchWord)

'If name not found, fCell will be empty
If fCell Is Nothing Then
MsgBox "Name not found", vbOKOnly, "Not found"
Exit Sub
Else
'Turn off events since we're about to change a cell, and
'we don't want to call this macro again
Application.EnableEvents = False
If UCase(fCell.Offset(0, 2).Value) = "IN" Then
fCell.Offset(0, 2).Value = "Out"
Else
fCell.Offset(0, 2).Value = "In"
End If
End If
'Very important to turn this back on!
Application.EnableEvents = True

End Sub

[/pre]

 

[Carlos]

Thank you Luke! I'm already trying it and it works wonderful! I really apreciate it! I'm checking it and trying to come up with different variables, it's excellent!

 

[Carlos]

Hey Luke, how can I change it so I can enter the value from a different sheet? is there a way to modify it so I can assign it to a button or something?