how to check the values in each cell across workbooks?

[htoh2]

Hi experts,

i'm a novice in vba and hope someone out there can assist me, thanks.

I would like to know how can i compare the values in the cells between 2 workbooks.

For eg.

in Enduser.xls, i have the following 2 columns :

Country code Business Positions
HH1 Manager
HH2 P Manager

In Lookup.xls, i have 2 columns of data:

Country code Company Code
HH1 CC1
HH1 CC2
HH1 CC3
HH2 CD1
HH3 CE3

So when i run the macro, it will check that IF country code in Enduser.xls & Country Code in Lookup.xls = "HH1" , then copy or store the values (CC1,CC2,CC3) in an array so later i can retrieve them.

Pls help

Thanks

Regards,
htoh2

 

[littleiitin]

Use in Enduser workbook

I have assumed your both workbook is placd in same folder

Sub check()
Dim varArray(100)
Dim cell As Range
Dim i As Long
Dim wbkA    As Workbook
Dim wbkNew  As Workbook
Set wbkA = ThisWorkbook
 
i = 0
Set wbkNew = Workbooks.Open(ThisWorkbook.Path & "\lookup.xls")
With wbkNew.Worksheets("sheet1")
    For Each cell In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
If cell.Value = "HH1" Then
varArray(i) = cell.Offset(, 1).Value
i = i + 1
End If
Next
End With
End Sub

 

[htoh2]

Hi, thank you for your reply.

May i check with you if your codes are storing the values into the array?

However,

i've edited to these codes:

Sub check()
Dim varArray(100)
Dim cell As Range
Dim i As Long
Dim wbkA As Workbook
Dim wbkNew As Workbook
Set wbkA = ThisWorkbook

i = 0
Set wbkNew = Workbooks.Open(ThisWorkbook.Path & "\lookup.xls")
With wbkNew.Worksheets("sheet1")
For Each cell In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
If cell.Value = "HH1" Then
varArray(i) = cell.Offset(, 1).Value
i = i + 1
End If
Next
With wbkNew.Worksheets("destinationFile")
ActiveCell.Value = varArray
End With
End With
End Sub

but i could not paste to my destination.

Could you kindly guide me?
Thanks

Regards,
htoh2