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]

sheet 1 is this

lop	wsd	qasd	asxc
62269		a
62286		b
62287		c
62319		d
62325		e
62327		f
62340		g
62348		h
62372		i
62381		j
62382		k
62383		l
62384		m
62400		n
62423		o
62424		p
62425		q
62426		r
62427		s
62428		t
62430		u
62443		v

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

sheet 2 is this

kiko	kikp	kikr	kike
a			20,00
b			30,00
c			40,00
d			50,00
e			60,00
f			70,00
g			80,00
h			90,00
i			100,00
j			110,00
k			120,00
l			130,00
m			140,00
n			150,00
o			160,00
p			170,00
q			180,00
r			190,00
s			200,00
t			210,00
u			220,00
v			230,00

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

nd sheet 3

62269	0,50
62286	0,50
62287	0,50
62319	0,50
62325	0,50
62327	0,50
62340	0,50
62348	0,50
62372	0,50
62381	0,50
62382	0,50
62383	0,50
62384	0,50
62400	25,00
62423	25,00
62424	25,00
62425	25,00
62426	25,00
62427	25,00
62428	25,00
62430	25,00
62443	25,00

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

 

[offthelip]

It would have been really helpful if you had told me which line your code was failing at, I have asked more than once and you didn't do it
I am still guessing as to where it fails but I can see a few problems:

[COLOR=#333333]Third = ActiveWorkbook.Name[/COLOR]

then

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

this line should be

[COLOR=#333333]Workbooks(Third).Activate[/COLOR]

this is because the first line assigns a variable called THIRD with the name of the activeworkbook. (which might be "third" ) or not
the second line activates the workbook who name is in the variable third i.e the one that used to be the active workbook
also you have got the indexing wrong which will cause an out of range error

Searcharr = Worksheets("Sheet1").Range("A2:C23")

this define an array from A2 to C23 which only has 22 rows going from 1 to 22
your indexing :

[COLOR=#333333]For i = 2 To 23[/COLOR]

this means it is going from the second row of the array (row3) to the 23rd row of the array ( which doesn't exist)
If you look back at the original code that I posted you will see that I very carefully assigned all of the arrays starting at row 1, and then I started the indexing at row 2 to the last row we were interested in. This is usually the easiest way of doing this. You can just load the rows you are interested in and then adjust the indexing to suit. If you change the indexing from 2 to 23 to 1 to 22 it should work.
Note you also need to change the j loop as well for the same reason

 

[Sotos13]

hi
First of all i thought that you had understood the range error but sorry in any case..i should of told you the exact line, you are correct.
Thanks to you I managed to make it work and indeed it was the Third in "" and the range starting from A1.
Apparently i can make it run only by having all the sheets in one workbook but i'm trying to figure out how to sum the results of each row and place them from D4 all the way down.
Now each result is placed one next to another(D4, E4, F4...., and then D5, E5, F5 .. and so on.
But i want in D4 to have the sum of the D4, E4, F4... and in D5 the sum of D5, E5, F5 ..etc
probably by using another variant and then saying

Xarray = Firstarray(j, y) * Secondarray(k, y)

Cells(i, y + 2)= Application.WorksheetFunction.Sum(Xarray).

this is the code now which works

sub test()


Third = ActiveWorkbook.Name
Workbooks(Third).Activate
Searcharr = Worksheets("Sheet1").Range("A1:C25")
Firstarray = Worksheets("Sheet2").Range("$A$1:$K$24")
Secondarray = Worksheets("Sheet3").Range("$A$1:$H$26")


For i = 2 To 25
For j = 2 To 24
If Searcharr(i, 3) = Firstarray(j, 1) Then
' found the first index
For k = 2 To 26
For y = 2 To 6
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, y + 2) = Firstarray(j, y) * Secondarray(k, y)

End If
Next y
Next k
End If
Next j
Next i

End Sub

 

[offthelip]

This code will add them up and put them in column DL

Sub test2()



'Third = ActiveWorkbook.Name ***these two lines do nothing
'Workbooks(Third).Activate   ***
Searcharr = Worksheets("Sheet1").Range("A1:C25")
Firstarray = Worksheets("Sheet2").Range("$A$1:$K$24")
Secondarray = Worksheets("Sheet3").Range("$A$1:$H$26")




For i = 2 To 25
For j = 2 To 24
If Searcharr(i, 3) = Firstarray(j, 1) Then
' found the first index
For k = 2 To 26
sumcells = 0
For y = 2 To 6
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
sumcells = sumcells + (Firstarray(j, y) * Secondarray(k, y))


End If
Next y
Cells(i, 4) = sumcells


Next k
End If
Next j
Next i


End Sub

Note a lessons to learn from this:
1: Post your code
2: Post what the error is and what line it is on.
I would have spotted those error ages ago if you had told what line the errors were on.
Finally there is no reason why you need to have the threee sheets in the same workbbok, EXCEL wil quite happily load a variant array from another workbook.

 

[Sotos13]

hi again,

thank you for the code and for the advice. The code though you sent me didn't work because the sumcells was coming out to zero for all the cells. i change the position of the sumcells=0 and wrote it after Next k and it works now. Check it out

Sub test()


Searcharr = Worksheets("Sheet1").Range("A1:C25")
Firstarray = Worksheets("Sheet2").Range("$A$1:$J$24")
Secondarray = Worksheets("Sheet3").Range("$A$1:$J$26")


For i = 2 To 25
For j = 2 To 24
If Searcharr(i, 3) = Firstarray(j, 1) Then
' found the first index

For k = 2 To 26

For y = 2 To 10
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
sumcells = sumcells + (Firstarray(j, y) * Secondarray(k, y))

End If
Next y
Cells(i, 4) = sumcells
Next k
sumcells = 0
End If
Next j
Next i

End Sub

Also how do you put the code in this blue font in this forum? it looks alot better than pasting it as i do...

 

[offthelip]

Sorry about the error on where to put the sumcells I was doing it quickly . you should also put

sumcells=0

right at the top to initialise the varaible to zero for the first iteration
to highlight your code click on # symbol in the menu at the top of the box you write into, then paste your code ,this will put
[ C O D E ] and [ / C O D E ] round your code. ( Note I have put spaces in to prevent the formatting)

 

[Sotos13]

ok thank you for the advice and all your help!!!