How to Find and Replace a Range of Data

[Chad Bateman]

Hello All,
I was hoping someone could help me figure out how to find and replace a range of values. I've looked online, and searched the forum with no sucess.

I have a list of cells in a column labled "Check #1", "Check #2", "Check #3" ect. I'm trying to build a Macro that would help me replace any value from "Check #1" to "Check #50,000" with "Outgoing Check". I know this can be done manually with relative ease, but it will need to be done very very very often, so I'd like to automatic it as much as possible.

Any tips on how this can be done? Ideally I'd like to put the steps into a Macro or Formula that could be re-produced with ease.

Thanks in advance for your help.

[sktneer]

Welcome to the Forum.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Chad Bateman]

I've attached an example. Also, if there's any way to set it so that any field starting with "Check #" could be changed to "Outgoing check" that would be great too (I only said 1-50,000 as an example).

Thanks in advance for everyone's help, I really really appreciate it.

[sktneer]

Though this can be achieved with a formula, you will need to populate an extra column to hold the replaced values. But if you want to replace the text in the cell itself, you may try this code to achieve this...

Please Login or Register  to view this content.

The red part in the code can be replaced as per your requirement if the Check # column is other than col. B

Press Alt+F11 to open VBA editor --> Insert --> Module --> Paste the above code in the code window --> Close VBA editor --> Save your workbook as Excel Macro-Enabled Workbook.
To run the code again, either insert a shape (like what I did in the Test Sheet), right click on it and assign this macro to the shape OR press Alt+F8 --> select the macro ReplaceText from the list --> Click Run

Does this help?

[Chad Bateman]

That was awesome, thanks so much for your help. One more question. I'm trying to do the same thing for fields that look like "Returned CK #"- I want them to become "Returned Check". I tried taking your code and changing the two fields like this:
Sub ReplaceText()
Dim rng, cell As Range
Dim lr As Long
lr = Cells(Rows.Count, "B").End(xlUp).Row
Application.ScreenUpdating = False
Set rng = Range("B2:B" & lr)
For Each cell In rng
If Left(cell, 7) = "Returned ck #199" Then
cell = "Returned Check"
End If
Next cell
Application.ScreenUpdating = True
End Sub

I can't it get it to make the change though. Is there anything else I need to change? The field is still in Column B.

Thanks again, you've been incredibly helpful.

[Chad Bateman]

Ha! I figured it out! It's the If Left(cell, 7) part. I changed that the (cell, 12) and it works perfectly.

Thanks again.

[sktneer]

Glad you figured out yourself and your issue is resolved.
If that takes care of your question, please mark your thread as Solved by selecting Thread Tools (just above your first post) -- Mark thread as solved.
Moreover you may also click on * (star) to Add Reputation to those who have put their time and efforts to help you in this forum. This is another way to say thanks to them.