give a cell a function with a macro
 

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
..

give a cell a function with a macro
 

see answer in worksheet functions

"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
.

give a cell a function with a macro
 

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"


In article ,
"thomas" wrote:

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
.

give a cell a function with a macro
 

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
.

give a cell a function with a macro
 

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

give a cell a function with a macro
 

"Dave Peterson" skrev i melding
...
I think =Vlookup() will work against closed files (seems to work ok for
me,
anyway <bg).

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

Best wishes Harald

give a cell a function with a macro
 

You're welcome, Ron, er, Harald!
<vvbg



Harald Staff wrote:

"Dave Peterson" skrev i melding
...
I think =Vlookup() will work against closed files (seems to work ok for
me,
anyway <bg).

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

Best wishes Harald

--

Dave Peterson

give a cell a function with a macro
 

Oops. I got my vacation all right....

Best wishes Harald

"Dave Peterson" skrev i melding
...
You're welcome, Ron, er, Harald!

Harald Staff wrote:
Another grave memory error. I
need a vacation.

give a cell a function with a macro
 

Some things I'll never understand. I think this is one of them.



Harald Staff wrote:

Oops. I got my vacation all right....

Best wishes Harald

"Dave Peterson" skrev i melding
...
You're welcome, Ron, er, Harald!

Harald Staff wrote:
Another grave memory error. I
need a vacation.

--

Dave Peterson