VBA Vlookup from diffrent workbooks

[Sotos13]

hello everyone

i have a table that has multiple columns which each cell has this formula. It starts from D2 all the way down to D500
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;4;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;2;0).

so it goes underneath VLOOKUP($C3 ...* VLOOKUP($A3...

In E2 i have the same formula but the only thing that changes is the column index fro both Vlookup
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;6;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;3;0).

and
F2 the same with difrent col.index
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;8;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;4;0).

So the first vlookup column index is changing in a step 2 manner and the second one adding one by one column.
this goes up to Q2
VLOOKUP($C2;'[Test1]Sheet1'!$A$2:$AI$137;30;0)*VLOOKUP($A2;'[Test2]Sheet1'!$A$3:$O$7927;15;0).

At the end in cell R2 i sum C2:Q2, R3 =sum C3:Q3..and so on.

What i want is a vba to calculate the above and just bring back the sum of this multiplication between those two vlookup.

Any ideas or any workarounds?
Thanks

 

[offthelip]

I would do the whole thing in vBA just by using variant arrays which should be much faster, ( and simpler to my mind):
this code should get you started. I wasn't quite sure of what equations you want where but this does one equation and the others are very similar.

Sub test()
Aname = ActiveWorkbook.Name


Workbooks("Test1").Activate
' note I load the array from A1 to make the indexing simpler
test1array = Worksheets("Sheet1").Range("$A$1:$AI$137")
Workbooks("Test2").Activate
' ditto
test2Array = Worksheets("Sheet1").Range("$A$1:$O$7927")
Workbooks(Aname).Activate
Searcharr = Range("A1:C500")
For i = 2 To 500
  For j = 2 To 137
   If Searcharr(i, 3) = test1array(j, 1) Then
    ' found the first index
      For k = 1 To 7927
           If Searcharr(i, 1) = test2Array(k, 1) Then
            ' found the second index
            ' so do the calculatiosn here
            ' this just does the first equation on your line but the others are very similar
            Cells(i, 4) = test1array(j, 4) * test2Array(k, 2)
           End If
      Next k
   End If
 Next j
Next i
End Sub

 

[Sotos13]

Hi Offfthelip,

thanks you for your reply...i will try it to see how it works...so your not using application.formula= Vlookup? it is slower?

 

[Sotos13]

Hi Offthelip,

i get a rut time error 9 " subscript out of range' . ihave three workbooks Test1, Test2, Test3 (Test3 is the one i want to have my answer from Test1 and Test2

Also, should we declare the arrays? i think there is where i get the error..

Sub test()
Test3 = ActiveWorkbook.Name


Workbooks("Test1").Activate
' note I load the array from A1 to make the indexing simpler
test1array = Worksheets("Sheet1").Range("$A$1:$AI$137")
Workbooks("Test2").Activate
' ditto
test2Array = Worksheets("Sheet1").Range("$A$1:$O$7927")
Workbooks("Test3").Activate
Searcharr = Range("A1:C500")
For i = 2 To 500
For j = 2 To 137
If Searcharr(i, 3) = test1array(j, 1) Then
' found the first index
For k = 1 To 7927
If Searcharr(i, 1) = test2Array(k, 1) Then
' found the second index
' so do the calculatiosn here
' this just does the first equation on your line but the others are very similar
Cells(i, 4) = test1array(j, 4) * test2Array(k, 2)
End If
Next k
End If
Next j
Next i
End Sub

 

[offthelip]

I presume it is this line:

[COLOR=#333333][FONT=Verdana]test1array = Worksheets("Sheet1").Range("$A$1:$AI$137"),
[/FONT][/COLOR]

Is the worksheet with the data on it named "Sheet1" because it looks like it isn't
Similar with the others


You don't need to declare the variables unless you have "option explicit" at the top. ( I never do!!)
Excel defaults to declaring anything as Variant. If you load a range from the worksheet into a variable it will automatically defualt to being a variant array
I never declare anything unless it is necessary. I think it makes my code much easier to read because it doesn't start with lines and lines of delcaration which tell you nothing. Spending the time saved writng comments is much more useful I think.
It also means if I have declared something it is for a specific reason so It stand out. e.g this number is double because I need the precision,

 

[Sotos13]

No, all the sheets for each workbook is named Sheet1.
i tried changing it with ActiveWorkbook.Sheets("Sheet1").Range...instead but again the same error

 

[offthelip]

I have just tested this code and it correctly picks up the data from my workbook and sheet number. if you are getting an error it is because you have spelt something incorrectly

Workbooks("BB_set list.xlsx").Activate
' note I load the array from A1 to make the indexing simpler
test1array = Worksheets("Sheet2").Range("$A$1:$AI$137")
MsgBox (test1array(1, 1))

 

[offthelip]

Another quick thought I presume you have got TEST1 and TEST2 open!!

 

[Sotos13]

hi ,
i'v tried it with open and with closed the workbooks but again i get the same error.. I changed my workbook names to First, Second, Third and also in the code.Let me show you what i have in each workbook.
In workbook "First" i have this

a	1	57	63	45	18	12	60	59	59	59	59	58	58	58
b	2	60	66	54	63	60	60	59	59	59	59	58	58	58
c	3	63	147	63	66	66	59	51	44	36	29	21	14	395
d	4	66	9	72	147	150	181	211	242	272	303	334	364	395
e	5	147	13	81	9	150	181	211	242	272	303	334	364	395
f	6	9	14	45	10	28	31	35	38	42	45	48	52	55
g	7	10	15	54	72	94	117	139	162	184	207	229	252	274

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

In the workbook "Second" i have

62267	1	3	5	7	9	11	13	15	17	19	21	23	25	27
62268	2	6	10	14	18	22	48	15	28	19	28	48	25	48
62269	3	9	15	28	27	33	51	15	56	19	56	51	28	51
62286	4	12	20	56	36	44	54	60	54	19	54	54	56	54
62287	5	15	25	54	45	55	57	65	57	28	57	57	54	57
62319	6	18	30	57	54	66	60	70	60	56	60	60	57	60
62325	7	21	35	60	63	77	63	75	63	54	63	63	60	63
62327	8	24	40	63	72	88	66	80	66	57	66	66	63	66
62340	9	27	45	66	81	99	56	85	147	60	147	57	66	54
62348	10	30	50	147	90	110	54	90	9	63	9	60	147	57

<colgroup><col width="64" span="15" style="width:48pt"> </colgroup><tbody>
</tbody>

And in workbook "Third" where i also have the code is

62267	1	a
62268	2	b
62269	3	c
62286	4	d
62287	5	e
62319	6	f
62325	7	g
62327	8	h
62340	9	i
62348	10	j
62372	11	k
62381	12	l
62382	13	m
62383	14	n
62384	15	o
62400	16	p
62423	17	q
62424	18	r
62425	19	s
62426	20	t
62427	21	u
62428	22	v

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[offthelip]

Can you post your code, knowing what is in the workbooks doesn't help