So i am trying to obtain code that will allow me to find a value in Column A and replace it with the value in column H using the list in columns G:H. In other words I would like it to loop through the spreadsheet and replace a particular word in Column A with the word in column H every time it finds it in the spreadsheet.sample find and replace worksheet.xlsx
Your sample is a bit confusing.
I assume;
Column A has the original list to be updated.
Column G has all the BEFORE names. The names to be changed(Before the change).
Column H has all the AFTER names. The new names(After the change).
Is this correct?
Hi jgelbach
Welcome to the Forum!!
It appears to me Column A and Column G are the same in all respects. If so, what am I missing...simply copy Column H to Column A.
John
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
@ jaslake,
Hello John,
Exactly what I thought as well, but what if a row in g just happend to be different?, then it should not copy the Cell H of that row.
I am looking into comparing values in Column A with that in Column G, and only if it is in fact the same, then Copy Column H to Column A. But I am having a bit of trouble getting some code to work along this assumption.
Care to help?
@Winon
Probably something like thisSorry that would be
Please Login or Register to view this content.Please Login or Register to view this content.
Last edited by jaslake; 03-23-2012 at 03:19 PM.
@ jazlake,
Very well done John. The WorkBook now looks like this and your code works like magic!
@ jaslake,
Hello John,
I found that if there are empty rows between Data entered your Code stops at the first empty row.
So I came up with this, to take care of empty rows as well:
Enjoy your weekend!Please Login or Register to view this content.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
@Winon
I don't know what scenario you've developed to cause thisWhatever the scenario, it needs to be dealt with. This line of code does not deal with it, it prematurely ends the sub.your Code stops at the first empty rowPlease Login or Register to view this content.
@ jaslake,
Hello John,
Whatever the scenario, it needs to be dealt with. This line of code does not deal with it, it prematurely ends the sub.Please Login or Register to view this content.
TheDoes NOT end anything prematurely, it simply implicates that if certain conditions are not met, THEN exit or whatever!Else:
Am am not saying that the original Code you gave is wrong, in fact it might hold true within certain requirements, but I merely suggested an alternative Code if there was an empty row within the range your code was looking at, whilst the requirement was to cover the whole range, empty rows despite.
With the Code you offered as, "Probably something like", I inserted a blank row or more, and found that "Probably something like" stopped at the first empty row, and did not cover the whole range, empty row/s despite.
So, in short, just to clarify and not fight, I was only trying to explain to you the different ways of looking at this specific situation.
@Winon
Not "fighting"...simply made an observation. Show me the scenario that causes thisIn my copy of the file I inserted three blank rows at Rows 2, 3 and 4...the code passed right by them and went on to Row 5. So, I don't know how you modified the file such that it gives you cause for concern. Show me so I understand.I inserted a blank row or more, and found that "Probably something like" stopped at the first empty row
@ jaslake,
Hello John,
Thank you for your response and not "Fighting"
Here you go, check out the WorkBook attached with your "Probably something like" Code and empty rows.
@ Winon
Again, not arguing (or fighting) but my observation stands...the procedure does end prematurely and should be dealt with. Here's the code I'd suggest to deal with it.I should point out, this code is not appropriate when one has blank rowsPlease Login or Register to view this content.Please Login or Register to view this content.
Last edited by jaslake; 03-25-2012 at 02:33 PM.
@ jaslake,
Hello John,
Tried and tested the NEW CODE:
Works like a charm however, I cannot find any posts in this thread, offering this solution prior to my alert!Please Login or Register to view this content.
Neither do I. So, put this in you're toolbox...if you anticipate empty cells in a column and you're looking for the last row in that column, don't use this approachOriginally Posted by Winon
@ jaslake,
I cannot find any posts in this thread, offering this solution prior to my alert!as .End(xlDown) ends the blank cell.Please Login or Register to view this content.
@ jaslake
LOL, thanks John,
I got to realise that, and all I can say is, Ditto!
Thank you so much for your input, looking forward to "fighting" with you again!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks