Trygve Thayer
asked on
Excel Macro update each line based on another worksheet
I have a file that has a header record in A1 and a trailer record in it at the end. The lines between is the data I want to edit that start in Cell A2. So Header record in A1 then Data in A2 then A3 then A4 and so on until it gets to the end with the last record being the trailer record.
What I want the macro to do is look in the file (attached as Data) and go to the first record in A2 and get the number in positions 43 through 48. Then look in the Reference file and find that number in Column A and get the 2 digit code in column C. Then go back to the record and replace positions 74 and 75 with that information.
So if I look at cell A2 in the data file and count over to positions 43 - 48 I get 321482
Then if I look in the Reference file and find that number I see in Column C the information is 5A
Then knowing 5A is what I want to replace I go to the record in the data file and count over to position 74 and 75 I see the value in there is 48.
Then I replace the 48 with 5A
Then I need to move to the next record and do it. and the next record, and the next record etc.
Hope this is detailed enough and a macro is possible. Thanks in advance.
Data.xls
Reference.xls
What I want the macro to do is look in the file (attached as Data) and go to the first record in A2 and get the number in positions 43 through 48. Then look in the Reference file and find that number in Column A and get the 2 digit code in column C. Then go back to the record and replace positions 74 and 75 with that information.
So if I look at cell A2 in the data file and count over to positions 43 - 48 I get 321482
Then if I look in the Reference file and find that number I see in Column C the information is 5A
Then knowing 5A is what I want to replace I go to the record in the data file and count over to position 74 and 75 I see the value in there is 48.
Then I replace the 48 with 5A
Then I need to move to the next record and do it. and the next record, and the next record etc.
Hope this is detailed enough and a macro is possible. Thanks in advance.
Data.xls
Reference.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER