Match and VLOOKUP from open workbook, storing values in Active wkb
 

I have a macro which opens workbooks and checks for the existance of Sheet1.
If sheet 1 is there, I want to populate other fields in the active workbook
from the open workbook.

I have the following equations I want to use:

Range("BW2").Select
ActiveCell.FormulaR1C1 = _
"=MATCH(RC[-2],'[Book1.xls]Sheet1'!R33)"
Range("BX2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,'[Book1.xls]Sheet1'!R1:R50,RC75,FALSE)"
Range("BY2").Select


1) Is there a more elegant way to write these equations?
2) How do I ensure that I get the value in the active cell of the active
workbook, not the equation?
3) Book1.xls is from column G of the workbook. I have a For/Next loop
going from the first to the last book I'm evaluating with the counter being
i.

How would I rewrite these equations to get what I want?

Thanks in advance for your assistance.

Barb Reinhardt

Match and VLOOKUP from open workbook, storing values in Active wkb
 

Dim cell as Range, eq1 as String, eq2 as String
Dim eq1a as String, eq1b as String
Dim bk as Workbook
eq1 = "=MATCH(RC[-2],'[ZZZ]Sheet1'!R33)"
eq2 = ""=VLOOKUP(R1C,'[ZZZ]Sheet1'!R1:R50,RC75,FALSE)"

With ThisWorkbook.worksheets("Data")
for each cell in .Range("G2:G20")
set bk = Workbook.Open(cell)
eq1a = replace(eq1,"ZZZ",bk.name)
eq2a = replace(eq2,"ZZZ",bk.Name)
.cells(cell.row,"BW").Formula = eq1a
.cells(cell.row,"BW").Formula = .cells(cell.row,"BW").Value
.cells(cell.row,"BX").Formula = eq2a
.cells(cell.row,"BX").Formula = .cells(cell.row,"BX").Value
bk.close SaveChanges:=False
Next
End With

I understood you to want to replace the formula with the value returned by
the formula. If not, then take out lines like

.cells(cell.row,"BW").Formula = .cells(cell.row,"BW").Value

--
Regards,
Tom Ogilvy

"Barb Reinhardt" wrote in message
...
I have a macro which opens workbooks and checks for the existance of
Sheet1.
If sheet 1 is there, I want to populate other fields in the active
workbook
from the open workbook.

I have the following equations I want to use:

Range("BW2").Select
ActiveCell.FormulaR1C1 = _
"=MATCH(RC[-2],'[Book1.xls]Sheet1'!R33)"
Range("BX2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R1C,'[Book1.xls]Sheet1'!R1:R50,RC75,FALSE)"
Range("BY2").Select


1) Is there a more elegant way to write these equations?
2) How do I ensure that I get the value in the active cell of the active
workbook, not the equation?
3) Book1.xls is from column G of the workbook. I have a For/Next loop
going from the first to the last book I'm evaluating with the counter
being
i.

How would I rewrite these equations to get what I want?

Thanks in advance for your assistance.

Barb Reinhardt