MS Excel (macro) question -- Find and replace (using a macro)?

Question

I have a column (column A) with 500,000 rows -- within this column, there are two words: LOW ; REG ... I need help with a Macro to find these two words and replace it with as followed:

LOW = 20% ; REG = 40% ...

If someone helps me with a macro for replacing this, could you also show me how to edit it so if I had to had another word to find and replace, then I could do it.

Thank you so much!

Answer 1

You can use the following macro to do as you ask. It will query the user for 'find what' and then 'replace with'. Then, it will evaluate column A and perform the replacement.



Copy the following macro to the clipboard:



Sub Spl_Find_and_Replace()

Dim i, LastRow

LastRow = Range("A" & Rows.Count).End(xlUp).Row

findWhat = InputBox("Enter value to locate.", "Find What")

If findWhat = "" Then

Exit Sub

End If

If Application.CountIf(Range("A:A"), findWhat) = 0 Then

MsgBox UCase(findWhat) & " does not appear in the worksheet.", _

vbOKOnly, "Not Found"

Exit Sub

End If

replaceWith = InputBox("Enter replacement value.", "Replace With")

If replaceWith = "" Then

Exit Sub

End If

For i = 1 To LastRow

If UCase(findWhat) = UCase(Cells(i, "A").Value) Then

Cells(i, "A").Value = replaceWith

End If

Next

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the editing area to the right.



Close the VBE and return to the worksheet.



Press ALT + F8



When the Macros window appears, highlight the macro and click 'Options..'



Enter a letter to be used as a keyboard shortcut and click 'OK'.



Close the Macros window.



Press CTRL + your shortcut letter to call the macro.



Note: enter your percentages as '20%', '40%', etc and Excel will automatically format the 'replaced' cells as Percentage. The percentages do not have to be whole numbers, of course.

Answer 2

To create your macro:



1. Start recording your macro. Navigation to do this depends on what version of Excel you're using. For 2003 its Tools -> Macro -> Record new macro.

2. Highlight the entire column you wish to perform the replace on.

3. Click Cntrl + F to open the find/replace dialogue. Click on the replace tab and enter your Find what and Replace with values.

4. Stop the macro from recording. For 2003 its Tools -> Macro -> Stop Recording. Your macro is now complete.



To edit your macro:

1. For 2003, navigate to Tools -> Macro -> Macros. Select the macro you created and click edit. It opens your macro code.

2. Find where it says Selection.Replace What:= and replace it the quoted text with the new text you wish to search. Do the same thing with the Replacement:= section.

3. Save your changes and your macros is updated.

Answer 3

B1=IF(A1="Low","Low = 20%",IF(A1="Reg","Reg = 40%",A1)) copy down list. If required to be a permanent list copy column then Paste special>Values over the top of original data or in a spare column.

Answer 4

Your question is rather difficult in that i'm uncertain what you attempt to do. You point out 'in Excel fill' -- are you filling the 'countif' formulation down a column, for this reason you have distinctive formulation to handle? Do you have the formulation in purely one cellular? do you like the formulation to continuously function on the same variety, i.e. A1:E40? Or, do you like the form to be dynamic and alter to incorporate new cells further?