Multiple Find/Replace within Variable Range

[freybe06]

Hello Everyone,

What I'm trying to do - I have a list of dates in Sheet2, Column A. Using a loop code, I'd like my macro to go through each cell in the list of dates and search for them in Sheet1, from Z1 to the last filled cell. Now here is a little twist - If the macro finds the date in Sheet1, Column Z, I'd like for the date to be replaced with a new date from Sheet2, Column B.

For example; If the macro is searching through Sheet1 Column Z for a date listed in Sheet2 A3, and it finds the date, I would like for the macro to replace it with the date in Sheet2 B3.

Now I'm not sure if that is possible, but if it is, I would greatly appreciate the help.

Thanks a bunch!

[rylo]

Hi

How about

Please Login or Register  to view this content.

rylo

[freybe06]

That seems to work great! Thanks Rylo!

[freybe06]

Hello again,

I opened this post back up because I wanted to add on to the request/help a little bit. I'm hoping to take the code that Rylo so graciously provided and make it so that I can find the text in column A within a string in column Y.

So, to explain a little further, the search would be for column Y and Z. We'll say column Y says "Hello((123456" and column Z says "123456". Now cell A1 says "123456" and B1 says "654321". The macro would find the instance of 123456 in both column Y and column Z and replace it so that Y would say "Hello((654321" and Z would just say "654321".

I hope I explained that well enough. Thanks again for the help!

[rylo]

Hi

Can you please put up an example file, and show in that file exactly what result you want to achieve from which specific condition.

rylo

[freybe06]

ReplaceMacroTest.xlsm

Attached is an example file of what I'm doing. I tried to take out just this part of the total macro since this is for work. As you can see in the file, whenever the last filled cell in column D of the Data tab says "no" then just column B of Sheet1 should have replacements, whenever the last filled cell in column D of the data tab says "yes" then both column A and B of Sheet1 should have replacements. The replacements are on Sheet2. Using the rule given above, whenever the number in column A shows up, it is replaced with the number next to it in column B.

Let me know if you need more details.

Thanks!

[rylo]

Hi

How does the stuff in the data tab relate to the items in either of the other sheets? Is it supposed to be a position match (ie row 1 of data relates to row 1 of sheet1?

Can you modify the example file and put up a results sheet to show how things should be after processing, and explain why each of the changes was made.

rylo

[freybe06]

Here is an updated file. I made a "results" tab that shows what Sheet1 starts as, and should end up as in two different scenarios.

Let me know if you have any questions.

Thanks!ReplaceMacroTest.xlsm

[ajmihalic4]

Nice. I'm looking for something almost exactly the same as the last request here (I think). I want to search strings in multiple columns, match against words in a separate table and subsitute (replace) when I find a match. Specifically I'm trying to "Britishize" some US English text into it's British colloquial equivalent. I'll attach a sheet with my data. Tab1 has the strings to be evaluated, and Tab2 has the terms to match and replace.

Thanks for any help!

[rylo]

Hi

Try this. The only problem that I can see in the results from the example file, is that the processing converts the format of 064321 to be the value 64321 in column C.

If you want to display the leading 0, then either make the column text, or format the column so that it displays the leading zero.

Please Login or Register  to view this content.

rylo

[freybe06]

Thanks Rylo - the code is getting stuck at this line though:

Please Login or Register  to view this content.

I changed the 3 that you originally to a 9 because instead of "3rd", I actually have a 9 digit code.

Any thoughts?

Thanks!

[rylo]

Hi

Does that code really exist in the lookup range? Make sure that one isn't a numeric, and one an alpha.

Try putting the vlookup formula directly into the spreadsheet and see if it gives an error or a result. If an error, then try to find why that error is occurring, and when corrected, then try running the code again.

rylo

[freybe06]

Hi Rylo,

I got the vlookup to work (or at least not give me an error), but now it doesn't look like the code is substituting anything. I really don't understand how the vlookup could be the issue. Both cells are set to General format. The "arr" cell is "JABID(1,46352JAB7)" and the cell on the "Data" tab is "46352JAB7". What would cause these to not match up?

If the vlookup isn't the issue, I'm not sure why the substitution part would not work either. On the file that I provided for you earlier, it turns out that the code wasn't actually choosing between the "Yes" and "No" macro. It was just defaulting to the "No" macro every time for some reason.

Thanks again for your help!

[rylo]

Hi

Can you put up an example file that contains data that is giving the problem and I'll have a look.

Re the code not working on your earlier file, I took your file from #8, put my code from #10 into a general module, selected sheet 1, and ran the code. I got the same results as shown in results!E:F with the exception of 64321, and I mentioned this problem in #10.

What do you get when you follow those steps?

rylo