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

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

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

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" wrote:

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

How to search in one column, and replace in the other column i
 

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



"Mike H" wrote:

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" wrote:

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