Help again!!!

D

Dani Lima

Hey.. I am a brend new user of excell programming, and I still have lost of
difficults to write codes.

Could someone help me again??

I am trying to look for some datas of an workbook on another one.
I can't fix the name of the file because it changes according to the file I
am working, so I've listed all of them (address on my computer and name) in a
worksheet.
Now comes the problem, I have to write the code (vlook up (what), (where)
etc..) but how do I write the "where"???

The code:

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Workbooks.Open Filename:="" & Range("N7") & "\" & Range("O7") & ".xls"

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Workbooks.Open Filename:="" & Range("H7") & "\" & Range("I7") & ".xls"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[1]&RC[3]"
Range("B2").Select
Selection.Copy
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("I4").Select

'write the time spent to deliver

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Windows("" & Sheets("Extract BD").Range("O7") & ".xls").Activate

Sheets("Produtos em Estoque").Activate
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
Range("A3").Select
ActiveWorkbook.Names.Add Name:="Valor", RefersToR1C1:= _
"='Produtos em Estoque'!R2C1:R65536C6"

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Windows("" & Sheets("Extract BD").Range("I7") & ".xls").Activate


Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-11],????,valor,1,FALSE)),"""",""24"")"
 
N

Normek

Hi Dani Lima,
If I understand you correctly the simplest way to get the correct syntax for
accessing information from one excel workbook to another excel workbook, is
to open both workbooks, put an equals sign (=) into an empty cell of one
workbook and select a cell in the other workbook.

The address will be in the formula bar. You may need to save the workbook
and reopen it to get the full address, which includes the folders and
subfolders.

Hope this helps
--
Normek


Dani Lima said:
Hey.. I am a brend new user of excell programming, and I still have lost of
difficults to write codes.

Could someone help me again??

I am trying to look for some datas of an workbook on another one.
I can't fix the name of the file because it changes according to the file I
am working, so I've listed all of them (address on my computer and name) in a
worksheet.
Now comes the problem, I have to write the code (vlook up (what), (where)
etc..) but how do I write the "where"???

The code:

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Workbooks.Open Filename:="" & Range("N7") & "\" & Range("O7") & ".xls"

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Workbooks.Open Filename:="" & Range("H7") & "\" & Range("I7") & ".xls"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[1]&RC[3]"
Range("B2").Select
Selection.Copy
Range("B3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("I4").Select

'write the time spent to deliver

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Windows("" & Sheets("Extract BD").Range("O7") & ".xls").Activate

Sheets("Produtos em Estoque").Activate
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
Range("A3").Select
ActiveWorkbook.Names.Add Name:="Valor", RefersToR1C1:= _
"='Produtos em Estoque'!R2C1:R65536C6"

Windows("Prazos de entrega_matriz").Activate
Sheets("Extract BD").Select
Windows("" & Sheets("Extract BD").Range("I7") & ".xls").Activate


Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-11],????,valor,1,FALSE)),"""",""24"")"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

wrong code 1
VBA problem. 4
Help merging two VBA codes 2
Macro Related to Paste Values 2
works in 2007 but not in 2010 0
= Today()+1 into Sheet1, Sheet2, and Sheet3 2
lenghty code 4
Code not working as expected 2

Top