Find-replace text with numbers, macro-question

[wintermaul]

Hi!
Ive have completed a survey in Forms wich asked for how satisfied you are with multiple things. I used words to describe answers instead of numbers when asking, and now im having trouble getting this into a good presentation.
So basically i need something to help me out to replace certain text with numbers in a marked row. The rows are not identical so i need to do this 1 row at a time.
I looked up and found a macro similar to what need here: How to create a macro code to achieve find and replace text in Excel?

Sub FindandReplaceText()
'Update by Extendoffice 2018/5/24
    Dim xFind As String
    Dim xRep As String
    Dim xRg As Range
    On Error Resume Next
    Set xRg =Cells
    xFind = Application.InputBox("word to search:", "Kutools for Excel", , , , , 2)
    xRep = Application.InputBox("word to replace:", "Kutools for Excel", , , , , 2)
    If xFind = "False" Or xRep = "False" Then Exit Sub
    xRg.Replace xFind, xRep, xlPart, xlByRows, False, False, False, False
End Sub

Because its a loooong time since ive been into this VBA and excel in general i was hoping for some help from you. How can i get this macro to do 1 row at a time and not the entire sheet?

As a sidenote, ive also looked at the Find and Replace-function that searched for text to replace but it also searched the whole workbook.

 

[jasonb75]

As a sidenote, ive also looked at the Find and Replace-function that searched for text to replace but it also searched the whole workbook.

That function has parameters that limit where it looks and what it looks at, it would likely be more practical than the code that you found.

If more than 1 cell is selected (or worked with in vba) when you use find and replace then only the selected cells are considered.

If it's a one off task then I would personally use find and replace (Ctrl h) with a range of cells selected where a specific change can be applied to all instances of a chosen word / number combination. If it is to be repeated regularly then perhaps it would be better to create a list of words to replace, what to replace them with and in which rows in a separate sheet so that the code can read from it rather than entering it into message boxes each time.

 

[wintermaul]

Thanks for your answer. I have been looking at the find and replace-function and found a tutorial: How to use find/replace
But i still cant get it to do just 1 row. It does the entire sheet when i try.
How can i limit this to just 1 row?

 

[jasonb75]

If it is doing the entire sheet then that means you have only selected a single cell.
Doing it manually, if you select the row (or a range of cells) then it will only look at what is selected. Doing it with vba (a macro) you need to tell it where to look.

 

[Alex Blakenburg]

How are you identifying which row to use ?
In theory all you need to do is change this line.
From:

Set xRg = Cells

To something like:

Set xRg = ActiveCell.EntireRow

 

[wintermaul]

If it is doing the entire sheet then that means you have only selected a single cell.
Doing it manually, if you select the row (or a range of cells) then it will only look at what is selected. Doing it with vba (a macro) you need to tell it where to look.

Aha! Got it, i found out that i had set the setting "Within" to Workbook,
It actually worked with marked row when i set the settings to...

So thanks alot this helps me out. Im gonna look into the macro a bit just for fun now that i got a good answer from Alex Blakenburg. But now i at least know do make it work.

 

[Alex Blakenburg]

Glad we could help. Thanks for letting us know.

 

[jasonb75]

In my opinion, the code that you found is far from practical which was why I was trying to divert you away from it. vba is useful for automating repetitive tasks when there is some consistency and it can complete the task with minimal user input, but for what you are doing it is likely to take longer than doing without.

Using the code, you have to repeatedly select the rows, run the macro, and enter data into 2 separate popup boxes. With Find and Replace, you can leave the box open and change the row selection behind it, no need to reopen it (or run a macro again) every time you change the row selection.