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

 

[Sotos13]

Sub test()
Third = ActiveWorkbook.Name




Workbooks("First").Activate
' note I load the array from A1 to make the indexing simpler
Firstarray = Worksheets("Sheet1").Range("$A$1:$AI$137")
Workbooks("Second").Activate
' ditto
SecondArray = Worksheets("Sheet1").Range("$A$1:$O$7927")
Workbooks("Third").Activate
Searcharr = Range("A1:C500")
For i = 2 To 500
For j = 2 To 137
If Searcharr(i, 3) = Firstarray(j, 1) Then
' found the first index
For k = 1 To 7927
If Searcharr(i, 1) = SecondArray(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) = Firstarray(j, 4) * SecondArray(k, 2)
End If
Next k
End If
Next j
Next i
End Sub

 

[offthelip]

which line does it fail on?

 

[offthelip]

A quick bit a googling came up with a possible answer try adding the extension to the workbooks line:

[COLOR=#333333]Workbooks("First.xlsx").Activate[/COLOR]

or whatever the ext is for your file

 

[Sotos13]

A quick bit a googling came up with a possible answer try adding the extension to the workbooks line:

[COLOR=#333333]Workbooks("First.xlsx").Activate[/COLOR]

or whatever the ext is for your file

I found that as well, and i tried it but noooo..it didn't work.
Did you try it with the data that i sent you?
Are you able to execute it or no.

 

[offthelip]

another possible problem is with screen updating if you have truned that off see:
https://support.microsoft.com/en-gb...ivated-when-you-run-a-macro-that-calls-the-wo

 

[offthelip]

the data in the workbook doesn't matter , I can load data from a remote workbook quite happily , I have done it many times. I don't know what is different about your set up.

 

[Sotos13]

Hi..Still nothing..i tried it...i'm also googling but nothing

 

[offthelip]

What version of Excel are you running ,what operating system. are the files stored locally or on aremote drive?
What line of your code is the error on and what is the error

 

[Sotos13]

What version of Excel are you running ,what operating system. are the files stored locally or on aremote drive?
What line of your code is the error on and what is the error

office 365 - 32 bit and it's locally stored.

 

[Sotos13]

hi again,

i added all the sheets in one workbook and i changed the code. iget a result but i also get the same error. can you try it ?

Sub test()


Third = ActiveWorkbook.Name


Workbooks("Third").Activate


Searcharr = Worksheets("Sheet1").Range("A2:C23")
Firstarray = Worksheets("Sheet2").Range("$A$1:$G$22")
SecondArray = Worksheets("Sheet3").Range("$A$1:$E$24")

For i = 2 To 23
For j = 2 To 22
If Searcharr(i, 3) = Firstarray(j, 1) Then
Debug.Print
' found the first index
For k = 1 To 24
If Searcharr(i, 1) = SecondArray(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) = Firstarray(j, 4) + SecondArray(k, 2)
End If
Next k
End If
Next j
Next i
End Sub