I think =Vlookup() will work against closed files (seems to work ok for me,
anyway <bg).

And to the OP.

When I have to write a complex formula in VBA, I write it in the worksheet. If
I want it as R1C1 notation, I'll convert to R1C1 and the highlight, copy and
paste into my line of code.

Then I double up the quote marks: " becomes "".

I find this less prone to errors. Ok, I still get errors, just fewer of them!

Harald Staff wrote:

Hi Thomas

This one is hard to test for me as posted. But two things leaps to mind when reading it:

VLOOKUP won't work pointing to closed files. And FormulaR1C1 uses R1C1 notation, so
addresses like "$A1" would err; it should rather read something like R[-32]C1. Try
"ActiveCell.Formula" instead and see if it helps.

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"thomas" wrote in message ...
Im trying and trying but it wont work.

I want to give a cell a function and want to do this with
the help of a macro. But everytime VB gives an error.

I now have this lines in VB:

Sheets("01").Select
Columns("A:A").Select
Range("A33").Select
ActiveCell.FormulaR1C1 =

"HERE BEGINS THE FUNCTION!!"
"=IF(ISERROR(VLOOKUP('I:\300\390\391\numbers\2 003
halfjaar\Cijfers\ExportenMonarch\[exportmonarch_bankiersDeb
etHJ2003.xls]Bankiers_debet'!A1,'I:\300\390\391
\Jaarrekening\2002
jaar\Cijfers\ExportenMonarch\[exportmonarch_bankiersDebet20
02.xls]Bankiers_debet'!$A$1:$A$50,1,FALSE)),'I:\300\390\3 91
\Jaarrekening\2003
halfjaar\Cijfers\ExportenMonarch\[exportmonarch_bankiersDeb
etHJ2003.xls]Bankiers_debet'!$A1,0)"

The error is "runtime error 1004"
"Application defined or object-defined error"

Is the function to long or something??
How can i solve this?

thanks
thomas
.



--

Dave Peterson