Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find a value and return another value on another workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find a value and return another value on another workbook
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)),"",VLOO KUP(VLOOKUP(A2,Wb2_Table,3,FALSE),Wb3_Table,2,FALS E)*24) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Dani Lima" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find a value and return another value on another work
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 "Luke M" wrote: 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)),"",VLOO KUP(VLOOKUP(A2,Wb2_Table,3,FALSE),Wb3_Table,2,FALS E)*24) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Dani Lima" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro to find a value and return another value on another work
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 "Luke M" wrote: 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)),"",VLOO KUP(VLOOKUP(A2,Wb2_Table,3,FALSE),Wb3_Table,2,FALS E)*24) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Dani Lima" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a macro to find a cell value and return a statement in a | Excel Programming | |||
Find Macro across workbook | Excel Programming | |||
Find Macro across workbook | Excel Worksheet Functions | |||
Find a value in a workbook and return the worksheet name | Excel Discussion (Misc queries) | |||
macro to find a text and return a message | Excel Programming |