VBA Index and Match Macro Button Help

[dhardman1]

Situation:

I have a master database with 5 sheets that needs to be updated from 5 different sources of informations. All the accounts have unique IDs and I am looking to populate the columns in the masterdatabase from the columns in the raw data files with the matching unique IDs. The raw data is updated on a monthly basis so this really needs to be a button that when I put the new raw data into the 'November Data' folder (for example) the Master database grabs the new november data. The november master database can then be saved down.


I'm currenly using a hardcoded vlookup function but this really isnt practical as it doesn't look up the column header based on the title, only the number which sometimes isn't always the same...

There are about 8 column headings that I need to match up from the raw daata to the master database.

Any help with this would be much appreciated.

 

[dhardman1]

Little more clarification - apologies for earlier post.

Master database

Facility Number Group Name <tbody> </tbody> Asset Data Currency Limit (Base Currency) Drawn Exposure (Base Currency) 0012 Blah USD 0013 Blah USD 0014 Blah USD 0015 Blah CAD 0016 Blah CAD 0017 Blah USD 0018 Blah USD 0019 Blah USD 0021 Blah USD 0043 Blah USD <tbody> </tbody>
Raw data file [TABLE="width: 352"] <tbody>[TR] [TD]D	Z
Facility Number	Limit Amount	Drawn Exposure
0012	1.00	123,489.00
0015	2.00	123,489.00
0067	3.00	123,489.00
0045	4.00	123,489.00
0034	5.00	123,489.00
0078	6.00	123,489.00
0034	7.00	123,489.00

<tbody>
</tbody>

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
I'm currently using the following formula:

=IF($B4=0,,INDEX('R:\ Database\[All Deals Listing.xls]Facility Listing'!$Z$2:$Z$483,MATCH($B4,'R:\Database\[All Deals Listing.xls]Facility Listing'!$D$2:$D$483,0)))

Which works but if I want to update this on a monthly basis I'll have to change all the index and match formulas at the time.

Also I want it so that if there isnt a facility number it doesn't change the cell (something an IF function can't do).

Then there are a total of 5 sheets on the MD that each need updating from different Raw Files.

Any assitance would be great.

I also much prefer the way VBA dumps the numbers into the cells (rather than having formulas in them).


[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]