| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 .. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Give me a Function | Excel Worksheet Functions | |||
| function to give me cell reference | Excel Worksheet Functions | |||
| Record a macro to use in any workbook--give it to others. | Excel Discussion (Misc queries) | |||
| Function to give value of a BLANK cell based on another | Excel Worksheet Functions | |||
| How to give function by cell color to sum two value in Excel | Excel Worksheet Functions | |||
Hybrid Mode
