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

[Guest]

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

 

[Tom Ogilvy]

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