macro to find a value and return another value on another workbook

[Dani Lima]

Hi! everyone!

I am finishing a code, but I am not so luck with that part.

What I am tring to do is: I have 3 differents workbooks, I have to find the
hours to deliver each product. On the wb1 I have at least 5000 kinds of
products (not all are on wb2), i have to find the products that are on wb2
and wb2 and see what is its clasification, once i did it, i have to see days
to deliver on wb3 that is in days and change it to hours and return this
value on wb1 at the columm "hours to delivery", the ones that are not on wb2
has to stay without the periods to deliver. I've tried a lot of times, but
always there's an error with the code, and I am tired of looking for it and
cant imagine what could be

could u help me???
the examples are below.

wb1

product Product name hours to deliver
1762 AGAMIX 20 CESTAS ?
1763 AGAMIX 425
1763 AGAMIX 425

wb2

product product name clasification
1411 ACETILENO A A
1414 HALOCARBONO 12 B
1420 ETILENO A
1422 HALOCARBONO 22 B
1423 HALOCARBONO 11 B
1425 HÉLIO ESPECIAL A
1426 HÉLIO ESPECIAL EM B


wb3

Tipe of product days to deliver
Special Gas Class A 7
Special Gas Class B 12
Special Gas Class C 17
Special Gas Clas D 152

 

[Luke M]

Is there a particular reason you can't use a formula, rather than macro?
Something like this:

=IF(ISERROR(VLOOKUP(A2,Wb2_Table,3,FALSE)),"",VLOOKUP(VLOOKUP(A2,Wb2_Table,3,FALSE),Wb3_Table,2,FALSE)*24)

 

[Dani Lima]

Yes, Luke, there is. I have to do it with the 30 branches of my company, and
this is just part of the macro, there are more things to be done before and
after I get this values, becaming my job endless.

Well, I'll try to do it in a simple way: making the macro write this formula
and copying to the others cells. I tried it before with another formula, but
returned an error.

regards,

Dani

 

[Dani Lima]

Hi, please see the code.. i really dont know what i am writing wrong..
just the part with the observation: write the quantity of hours we have to
deliver special gases is not working..

'write the quantity of hours we have to deliver gases we have into stok

Windows("Tabela Prazos de Entrega - Anchieta.xls").Activate
Sheets("Produtos em Estoque").Select
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
Range("A2").Select
ActiveCell.FormulaR1C1 = "=RC[2]&RC[4]"
Range("A2").Select
Selection.Copy
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("C13").Select
Application.CutCopyMode = False
Windows("anc.xls").Activate
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-11],'[Tabela Prazos de Entrega -
Anchieta.xls]Produtos em Estoque'!R2C1:R65536C1,1,FALSE)),"""",""24"")"
Range("L2").Select
Selection.Copy
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

'write the quatity of hours we have to deliver special gases

Windows("anc.xls").Activate
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC[-7],'[Prazos de
entrega_matriz.xls]GE'!R2C1:R93C3,3,FALSE)),"",VLOOKUP(VLOOKUP(RC[-7],'[Prazos
de entrega_matriz.xls]GE'!R2C1:R93C3,3,FALSE),'[Tabela Prazos de Entrega -
Anchieta.xls]Prazo de entrega'!R13C1:R16C4,4,FALSE)*24)"
Range("I2").Select
Selection.Copy
Range("L2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

tk

Dani