give a cell a function with a macro

  • Thread starter Thread starter thomas
  • Start date Start date
T

thomas

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\2003
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\391
\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
..
 
You can't use A1-style references in a .FormulaR1C1 statement.
Either convert your function to R1C1, or use

Sheets("01").Range("A33").Formula = "<your formula here>"
 
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.
 
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 said:
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 said:
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\2003
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\391
\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
.
Click to expand...
Click to expand...
 
Dave Peterson said:
I think =Vlookup() will work against closed files (seems to work ok for me,
anyway <bg>).
Click to expand...

Sorry, you are right as always Dave, it works. Another grave memory error. I
need a vacation.

Best wishes Harald
 
You're welcome, Ron, er, Harald!
<vvbg>



Harald said:
Sorry, you are right as always Dave, it works. Another grave memory error. I
need a vacation.

Best wishes Harald
Click to expand...
 
Back
Top