Please help! How can i do this?

[htoh2]

Greetings to all experts,

I'm a novice in visual basic, hence i would like to seek some advices from the experts. I would appreciate if you can help me on this issue:

I have 2 excel workbooks, 1st will be EndUserFile.xls, and it have 3 columns of data as below:

Country Code Business Positions Company code
CC1 Manager
CC2 Admin Clerk
CC3 Officer
CC4 Manager

2nd workbook will be Lookup.xls file with the following data:

Country Code Company Code
CC1 ABC
CC1 CDE
CC1 FGH
CC1 JKL
CC2 KKK
CC2 LLL
CC3 001
CC3 002
CC3 003

And my requirement using macro is:

When we filter Country code = "CC1" in EndUserFile.xls , upon clicking a button, the values of company codes (e.g.ABC,CDE,FGH,JKL) for country code e.g. "CC1" from Lookup.xls is copied and these values are updated back to the company code column in EndUserFile.xls

Experts, pls kindly guide me.

Feel free to clear any doubts with me.

Thanks a millions

Best Regards,
htoh2

 

[htoh2]

Hi experts,

I'm trying to use array and i've found some codes regarding storing the variables.
So i'm not sure if i could use it to perform my requirement.

These are some codes, in case you may need to go through it :

Sub TestDynamicArray()
' stores all names in the workbook in the array variable MyNames()
Dim MyNames() As String ' declares a dynamic array variable
Dim iCount As Integer
Dim Max As Integer
Max = ThisWorkbook.Names.Count ' finds the maximum array size
ReDim MyNames(1 to Max) ' declares the array variable with the necessary size
For iCount = 1 To Max
MyNames(iCount) = ThisWorkbook.Names(iCount)
Next iCount
Erase MyNames() ' deletes the varible contents, free some memory
End Sub

From the source : http://www.exceltip.com/st/Array_variables_using_VBA_in_Microsoft_Excel/509.html


i've tried to edit it according to my needs, but to no avail.

Pls kindly help me out, thank you so much

Regards,
htoh2