How to search in one column, and replace in the other column if fo

[Adnan]

I have multiple data in Column A, these data have a value in column B, I am
looking at a way (via VBA) to go from A1:A100 looking for curtain names (say
“Adnanâ€, “John Smithâ€,â€John Doeâ€) and change their value in column B to sy
for Adnan 10, for John Smith 25, and for John Doe = 65 wherever they are
(they exist in more then two places).

These values could be text, numbers, date or currency.

Thank you,
Adnan

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in and run it

Sub stantial()
Dim response As String
Dim MyRange As Range
Set MyRange = Range("A1:A100")
response = UCase(InputBox("Enter name to search for", "Name search"))
newval = InputBox("Enter Replace value", "Name search")
For Each c In MyRange
If UCase(c.Value) = response Then
c.Offset(, 1).Value = newval
End If
Next
End Sub

Mike

 

[Adnan]

Mike,

Thank you for your response. I see I am being prompted, I was wondering if I
can set criteria in code and then finish it without prompting.

Would something like this work, (just tryied it and am getting a Roun-time
'13' error)?




Sub stantial()

Dim Row As Long

For Row = 1 To 100
If Cells(Row, 1).Value = "Adnan" Then
Cells(Rows, "B").Value = 10
ElseIf Cells(Row, 1).Value = "John Smith" Then
Cells(Row, "B").Value = 65
Else
End If
Next Row

End Sub