VBA / Formula for Find and Replace operation required to replace data on main sheet from updated entries on secondary sheet
HI Experts
Hope you can help
I have a worksheet with two different sheets – the Main STK Sheet contains all data whereas the HTS Master File sheet contains the shipping codes that are replicated in Column H , I and K on the Main STK Sheet.
The shipping codes are assigned to each part description on the Main STK Sheet depending on the criteria. For example, a pair of trousers for a boy or girl will have the same code as the main criteria (trousers) is the same even though they will have two separate part numbers to distinguish the items apart. Therefore, a shipping code can be duplicated many times on the Main STK sheet depending on the number of parts.
Basically, the codes on the Main STK sheet are current however, if these change (as they do on a regular basis) I need to enter a NEW code against the old code on the HTS Master File sheet. When this new code is entered against an existing code on the HTS Master sheet, I need a find / replace operation to take place that replaces all the existing codes on Main STK sheet to these new codes.
Ive tried with various combinations of INDEX, MATCH, LOOKUPs etc. and tried recording a macro but unfortunately, this has not been successful.
I have attached the file currently in use.
Any help would be much appreciated
J
EE_Example_010317.xlsx
Hope you can help
I have a worksheet with two different sheets – the Main STK Sheet contains all data whereas the HTS Master File sheet contains the shipping codes that are replicated in Column H , I and K on the Main STK Sheet.
The shipping codes are assigned to each part description on the Main STK Sheet depending on the criteria. For example, a pair of trousers for a boy or girl will have the same code as the main criteria (trousers) is the same even though they will have two separate part numbers to distinguish the items apart. Therefore, a shipping code can be duplicated many times on the Main STK sheet depending on the number of parts.
Basically, the codes on the Main STK sheet are current however, if these change (as they do on a regular basis) I need to enter a NEW code against the old code on the HTS Master File sheet. When this new code is entered against an existing code on the HTS Master sheet, I need a find / replace operation to take place that replaces all the existing codes on Main STK sheet to these new codes.
Ive tried with various combinations of INDEX, MATCH, LOOKUPs etc. and tried recording a macro but unfortunately, this has not been successful.
I have attached the file currently in use.
Any help would be much appreciated
J
EE_Example_010317.xlsx
Jamie Garroch (MVP)
Are you looking for an automation mechanism, either by formulae or VBA macro or will a manual search and replace work:
ASKER
HI Jamie
Thank you for your swift response
Unfortunately, I need something automated as to promote user-friendliness for all types of capability (rules of the company im afraid!)
I tried a manual version with a few hidden columns, some data dragging and lookups but it just seemed to negate the process as the same amount of entry was still required.
Ideally some VBA or linked formulae to update the existing to the new codes is what I am looking for.
Appreciate your help
J
Thank you for your swift response
Unfortunately, I need something automated as to promote user-friendliness for all types of capability (rules of the company im afraid!)
I tried a manual version with a few hidden columns, some data dragging and lookups but it just seemed to negate the process as the same amount of entry was still required.
Ideally some VBA or linked formulae to update the existing to the new codes is what I am looking for.
Appreciate your help
J
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HI Jamie
I cant thank you enough for your help
It works perfect - Ive replicated it across three buttons for the UK USA and Canada codes - exactly what I needed
This has saved a lot of time as well as your code giving me some invaluable insight into creating a script of this nature.
J
I cant thank you enough for your help
It works perfect - Ive replicated it across three buttons for the UK USA and Canada codes - exactly what I needed
This has saved a lot of time as well as your code giving me some invaluable insight into creating a script of this nature.
J
Glad it helped! If you want to apply the same search and replace code to all three columns and go further by using the cell that's preselected to pre-populate the find field, it wouldn't be too much more work to write a single macro that calls this form that then runs the code above but with the UK/USA/Canada variations built in:
Have fun!
Have fun!
ASKER
HI Jamie
The above screen looks awesome
Can you give me some pointers as to how to convert the code to include this method instead of your original version?
J
The above screen looks awesome
Can you give me some pointers as to how to convert the code to include this method instead of your original version?
J
If you post a new question, referencing this one and how to use the code in a form, I'll post a new answer.
ASKER
HI Jamie
Many Thanks
I have reposted under the following link
https://www.experts-exchange.com/questions/29005947/Using-existing-code-in-a-form-Update-to-previous-question-VBA-Find-and-Replace.html
J
Many Thanks
I have reposted under the following link
https://www.experts-exchange.com/questions/29005947/Using-existing-code-in-a-form-Update-to-previous-question-VBA-Find-and-Replace.html
J