You should be able to do this without using Access.
Which version of Excel are you using?
Does the data in Book2 change?
Just so that I understand what you are currently doing:
you have been opening book1, inserting a column next to the "country" and entering "location" for the header. You are then by hand opening book2 and then, by hand, looking up the information from book2 as a cross reference and by hand entering that value, yes?
For this first part, the built in macro recorder can build your base code. As such would be normally available to you, the basic code will be:
- Sub InsertColumnForLocation()
-
'
-
Range("A1").Select
-
Columns("D:D").Select
-
Selection.Insert Shift:=xlToRight
-
Range("D1").FormulaR1C1 = "Location"
-
Range("A1").Select
-
End Sub
Note that I am working on the concept that your headers are in row1. So the first thing I do in this code is send you to Home on the worksheet - not strictly needed; however, bad habit.
Then We slect the entire D column (same as clicking on the column-button) and then insert the column (there are other ways to do this; however, I've sometimes had deep cells not shift properly useing them whereas this method doesn't seem to fail) and the header text.
This is very plain, no formating etc...
The next step will be how to handle the data.
That may depend somewhat on the version of excel you are using.
What I would do is bring in your book2 data, sort it on the country field, assign it a range name, then use VBA to enter a VLOOKUP() formula into your location column.
-
Sorry, no, still not opening the attachments, still at a work PC.
Also as we progress thru this project, please keep in mind that Bytes is neither a code writing nor homework service.
6
