Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace - Limit search to a specific column | Excel Worksheet Functions | |||
Search a column for values, return a value from adj column | Excel Worksheet Functions | |||
LookUp Function with Two Column Search Returning One Column Value | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Replace all non blanks in a column with column header | Excel Discussion (Misc queries) |