why errormessage?

[Jack Sons]

Hi all,

I saw no response to my earlier post below, so I post again. Hope my
question is not too stupid to be answered.
----------------------------------------------------------------------------
-------------------------------------

Hi all,

The lines below cause my macro to halt with the error message : "error
determined by application" (or something like that, I translated from
Dutch). What could be wrong? When I put the formula in the normal way in
cell L1 it works fine.

Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten
=""&COUNTA(C[-11])+COUNTA(C[-5])-COUNTIF(R[1]:R[65526],""*-03-05"")-1"


Jack Sons
The Netherlands

 

[Jazzer]

Hi,

Could you also include the formula which you would like have as
result of this macro?

- Asse

 

[Dave Peterson]

It worked fine for me in my USA version.

Is your worksheet protected?

Hi all,

I saw no response to my earlier post below, so I post again. Hope my
question is not too stupid to be answered.
----------------------------------------------------------------------------
-------------------------------------

Hi all,

The lines below cause my macro to halt with the error message : "error
determined by application" (or something like that, I translated from
Dutch). What could be wrong? When I put the formula in the normal way in
cell L1 it works fine.

Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten
=""&COUNTA(C[-11])+COUNTA(C[-5])-COUNTIF(R[1]:R[65526],""*-03-05"")-1"

Jack Sons
The Netherlands

 

[Jack Sons]

Dave,

No protection at all.

When I put in L1 the formula "manual" it works fine, but
COUNTIF(R[1]:R[65526],""*-03-05"")
should be
COUNTIF(R[2]:R[65526],""*-03-05"") to avoid circular reference.

Putting the R[2]: in the code in stead of R[1]: doesn't help.Any other idea?

Jack.

It worked fine for me in my USA version.

Is your worksheet protected?

Hi all,

I saw no response to my earlier post below, so I post again. Hope my
question is not too stupid to be answered.
--------------------------------------------------------------------------

--

-------------------------------------

Hi all,

The lines below cause my macro to halt with the error message : "error
determined by application" (or something like that, I translated from
Dutch). What could be wrong? When I put the formula in the normal way in
cell L1 it works fine.

Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten
=""&COUNTA(C[-11])+COUNTA(C[-5])-COUNTIF(R[1]:R[65526],""*-03-05"")-1"

Jack Sons
The Netherlands

 

[Jack Sons]

Dave,

On a blank sheet in a new workbook this works

Sub Macro2()
Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten ="""
End Sub

but this won't

Sub Macro2()
Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten = ""&COUNTA(C[-11])"
End Sub

Strange?!

Jack.

It worked fine for me in my USA version.

Is your worksheet protected?

Hi all,

I saw no response to my earlier post below, so I post again. Hope my
question is not too stupid to be answered.
--------------------------------------------------------------------------

--

-------------------------------------

Hi all,

The lines below cause my macro to halt with the error message : "error
determined by application" (or something like that, I translated from
Dutch). What could be wrong? When I put the formula in the normal way in
cell L1 it works fine.

Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten
=""&COUNTA(C[-11])+COUNTA(C[-5])-COUNTIF(R[1]:R[65526],""*-03-05"")-1"

Jack Sons
The Netherlands

 

[Dave Peterson]

I don't have a solution, but maybe you could try this:

Switch your workbook to R1C1 reference style (if you're not using it now).
then have your code put the same formula, but start with $= instead of just =.

Since this is just a string, it won't cause an error.

Now switch back to excel and change that formula manually. (remove the leading
$).

See if/how excel yells.

Dave,

No protection at all.

When I put in L1 the formula "manual" it works fine, but
COUNTIF(R[1]:R[65526],""*-03-05"")
should be
COUNTIF(R[2]:R[65526],""*-03-05"") to avoid circular reference.

Putting the R[2]: in the code in stead of R[1]: doesn't help.Any other idea?

Jack.

It worked fine for me in my USA version.

Is your worksheet protected?

Hi all,

I saw no response to my earlier post below, so I post again. Hope my
question is not too stupid to be answered.
--------------------------------------------------------------------------

--

-------------------------------------

Hi all,

The lines below cause my macro to halt with the error message : "error
determined by application" (or something like that, I translated from
Dutch). What could be wrong? When I put the formula in the normal way in
cell L1 it works fine.

Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten
=""&COUNTA(C[-11])+COUNTA(C[-5])-COUNTIF(R[1]:R[65526],""*-03-05"")-1"

Jack Sons
The Netherlands

 

[Dave Peterson]

I don't deal with any international issues, but do you use an English version of
excel?

If no, maybe using your language's version of =counta() would help. (I was
under the impression that you wrote the formula in VBA in English and it would
convert to your native tongue.

In fact, I thought you would have to use:
activecell.FormulaR1C1Local = ...
to use your non-english functions.

(well, that's the way I understand the help in VBA.)

Dave,

On a blank sheet in a new workbook this works

Sub Macro2()
Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten ="""
End Sub

but this won't

Sub Macro2()
Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten = ""&COUNTA(C[-11])"
End Sub

Strange?!

Jack.

It worked fine for me in my USA version.

Is your worksheet protected?

Hi all,

I saw no response to my earlier post below, so I post again. Hope my
question is not too stupid to be answered.
--------------------------------------------------------------------------

--

-------------------------------------

Hi all,

The lines below cause my macro to halt with the error message : "error
determined by application" (or something like that, I translated from
Dutch). What could be wrong? When I put the formula in the normal way in
cell L1 it works fine.

Range("L1").Select
ActiveCell.FormulaR1C1 = "=""aantal cursisten
=""&COUNTA(C[-11])+COUNTA(C[-5])-COUNTIF(R[1]:R[65526],""*-03-05"")-1"

Jack Sons
The Netherlands