VBA, total number of cells with text

[Jack Sons]

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal") of cells in
B1 to B200 and H1 tot H200 that contain text (there can be nothing else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =
""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"

but it results in aan errormessage (something like "error caused by the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any other and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Frank Kabel]

Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =
""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

 

[Jack Sons]

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal
=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"

took it right from your answer, but it halts again at that line of code.
Please test it, I can't find anything wrong, but want this thing to work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.

Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =
""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal") of cells in
B1 to B200 and H1 tot H200 that contain text (there can be nothing else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =
""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error caused by the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any other and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Frank Kabel]

Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &
countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal
=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line of code.
Please test it, I can't find anything wrong, but want this thing to work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.

Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =
""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal")

of
cells in

B1 to B200 and H1 tot H200 that contain text (there can be

nothing
else in

columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error caused

by
the

application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any

other
and

perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Jack Sons]

Frank,

It also won't work. With the data is nothing wrong, when put as a worsheet
function in L1 it works fine, but in code the execution halts at that line
of code. The code itself looks all right, so I can't imagine what could be
wrong.

When I try it on a sheet of a new workbook it works fine, but not in the
existing sheet of the existing workbook (which has no locked ceels ore
something like that). Even if I put this

Sub try()
Range("L1").Select
ActiveCell.FormulaR1C1 = "=""totaal aantal = ""
&counta(RC[-10]:R[199]C[-10])+counta(RC[-4]:R[199]C[-4])"
End Sub

in the sheet module it won't work.

So perhaps the right question is: what in a worksheet can cause code to halt
at a line with countif or counta if the equivalent worksheet function in the
worksheet itself works fine?

Jack Sons

Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &
countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal
=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line of code.
Please test it, I can't find anything wrong, but want this thing to work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.

Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =
""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal") of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be nothing
else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error caused by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any other
and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Jack Sons]

Frank,

It is even more weird. To my total confusion I discovered that when I copy
my existing sheet to a new workbook the code in the sheet module works
correct.

How is that possible, in the original workbook it won't work but after
copying the same sheet to a new workbook all problems are gone if the code
is executed in the new workbook.

Jack.

Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &
countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal
=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line of code.
Please test it, I can't find anything wrong, but want this thing to work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.

Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =
""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal") of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be nothing
else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error caused by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any other
and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Frank Kabel]

Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that when I copy
my existing sheet to a new workbook the code in the sheet module works
correct.

How is that possible, in the original workbook it won't work but after
copying the same sheet to a new workbook all problems are gone if the code
is executed in the new workbook.

Jack.

Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &
countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal

=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line

of
code.

Please test it, I can't find anything wrong, but want this thing

to
work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""

)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal

aantal")
of

cells in
B1 to B200 and H1 tot H200 that contain text (there can be nothing
else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""

)"

but it results in aan errormessage (something like "error

caused
by

the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any other
and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Jack Sons]

Frank,

Nothing else makes me think of a corrupt file. This file is our most
inportant file, our whole business depnds on it. It is a large and rather
complicated file with a huge amount of VBA procedures that more or less
automate all kinds of processes. So "just create a new workbook" is not
really an option.

The key question now seems to me: why would a simple subroutine like this

Sub Try
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"
End Sub

not work in the original sheet (not in any sheet of the original workbook!)
but does what it should do in another (in any existing or new) workbook?

I think that if my jile was corrupt I would have noticed other strange
behaviour, don't you think so?

Jack.

Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that when I copy
my existing sheet to a new workbook the code in the sheet module works
correct.

How is that possible, in the original workbook it won't work but after
copying the same sheet to a new workbook all problems are gone if the code
is executed in the new workbook.

Jack.

Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &
countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal

=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line of
code.
Please test it, I can't find anything wrong, but want this thing to
work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal")
of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be
nothing
else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =


""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error caused
by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any
other
and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Myrna Larson]

You say the formula "doesn't work". Can you explain what you mean? Does the
code not compile? Does it give a run-time error? Does Excel not accept it? Is
it accepted but the result isn't correct?

Out of curiosity, does the corresponding A1 formula work?

Have you ever used Rob Bovey's Code Cleaner on this workbook? If not, it might
be a good idea. I don't have the url to get it, but Google should be able to
find it.

Frank,

Nothing else makes me think of a corrupt file. This file is our most
inportant file, our whole business depnds on it. It is a large and rather
complicated file with a huge amount of VBA procedures that more or less
automate all kinds of processes. So "just create a new workbook" is not
really an option.

The key question now seems to me: why would a simple subroutine like this

Sub Try
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"
End Sub

not work in the original sheet (not in any sheet of the original workbook!)
but does what it should do in another (in any existing or new) workbook?

I think that if my jile was corrupt I would have noticed other strange
behaviour, don't you think so?

Jack.

Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that when I copy
my existing sheet to a new workbook the code in the sheet module works
correct.

How is that possible, in the original workbook it won't work but after
copying the same sheet to a new workbook all problems are gone if the code
is executed in the new workbook.

Jack.

"Frank Kabel" <[email protected]> schreef in bericht
Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &
countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal

=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line of
code.
Please test it, I can't find anything wrong, but want this thing to
work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal")
of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be
nothing
else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =


""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error caused
by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any
other
and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Frank Kabel]

Hi
sometimes such corruptions 'just happen'. I've a similar file issue
with a statement such as
..numberformat="dd.mm.yyyy"

very straight forward, isn't it. But in this one file it creates a
totally different format
d//\m\yyy

Just copying this worksheet to another workbook solved the problem. So
yes I can think of some very strange Excel behaviour.

So if your business really depends on this file (what makes me very
uncomfortable as this is an EXCEL fiel ) you really should try to
- use a recent backup and see if it happens there as well
- copy only the worksheets sheet by sheet to an empty workbook


In addition why are you testing your code on your production file??



--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

Nothing else makes me think of a corrupt file. This file is our most
inportant file, our whole business depnds on it. It is a large and
rather complicated file with a huge amount of VBA procedures that
more or less automate all kinds of processes. So "just create a new
workbook" is not really an option.

The key question now seems to me: why would a simple subroutine like
this

Sub Try
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"
End Sub

not work in the original sheet (not in any sheet of the original
workbook!) but does what it should do in another (in any existing or
new) workbook?

I think that if my jile was corrupt I would have noticed other strange
behaviour, don't you think so?

Jack.

Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that when
I copy my existing sheet to a new workbook the code in the sheet
module works
correct.

How is that possible, in the original workbook it won't work but after
copying the same sheet to a new workbook all problems are gone if
the code is executed in the new workbook.

Jack.

"Frank Kabel" <[email protected]> schreef in bericht
Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &

countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal

=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"

")"

took it right from your answer, but it halts again at that line
of code. Please test it, I can't find anything wrong, but want
this thing to work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't
work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""

)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal aantal")
of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be
nothing else in columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =

""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""

)"

but it results in aan errormessage (something like "error caused
by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any
other and perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Myrna Larson]

Hi, Frank:

In some discussions with Rob Bovey, he mentioned corruption being carried over
if copy entire worksheets. He suggested selecting the cells and copying and
pasting them rather than the whole sheet.

Myrna Larson

 

[Frank Kabel]

Hi Myrna
I agree with you. Personally I would create the workbook from scratch

 

[Jack Sons]

Myrna,

The code results in aan errormessage (something like
"error 1004: by application or by object caused error"
(badly (?) translated from Dutch). Execution of the sub halts at the line
(it is yellow) with

ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but when I put the equivalent worksheet formula manually in L1 in that
worksheet
=AANTAL.ALS(B1:B200;"*")
it works perfect.

Notice that AANTAL.ALS is the Dutch version equivalant of countif.

I never used Rob Bovey's Code Cleaner on this workbook, I am a bit scared to
do it. Besides, what in the existing code in other modules could cause the
rejection of this code? The worksheet in question contains no links to
anything else, it also contains no formulas etc. Just a few numbers and
text, nothing more, all togeteher just one page.

And then, why no problem whatsoever occurs when I do exactly the same after
I copy that sheet to another (new) workbook or to another existing worbook?

Also strange, the code won't work in any of the sheets on the original
workbook. Some mysterious force refuses to place the formula in L1 or any
other cell.

Jack.

You say the formula "doesn't work". Can you explain what you mean? Does the
code not compile? Does it give a run-time error? Does Excel not accept it? Is
it accepted but the result isn't correct?

Out of curiosity, does the corresponding A1 formula work?

Have you ever used Rob Bovey's Code Cleaner on this workbook? If not, it might
be a good idea. I don't have the url to get it, but Google should be able to
find it.

Frank,

Nothing else makes me think of a corrupt file. This file is our most
inportant file, our whole business depnds on it. It is a large and rather
complicated file with a huge amount of VBA procedures that more or less
automate all kinds of processes. So "just create a new workbook" is not
really an option.

The key question now seems to me: why would a simple subroutine like this

Sub Try
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"
End Sub

not work in the original sheet (not in any sheet of the original workbook!)
but does what it should do in another (in any existing or new) workbook?

I think that if my jile was corrupt I would have noticed other strange
behaviour, don't you think so?

Jack.

Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that when I
copy
my existing sheet to a new workbook the code in the sheet module
works
correct.

How is that possible, in the original workbook it won't work but
after
copying the same sheet to a new workbook all problems are gone if the
code
is executed in the new workbook.

Jack.

"Frank Kabel" <[email protected]> schreef in bericht
Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &

countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal


=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line
of
code.
Please test it, I can't find anything wrong, but want this thing
to
work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't
work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =


""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal
aantal")
of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be
nothing
else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =



""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error
caused
by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any
other
and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Frank Kabel]

Hi Jack
I think you really should create a new workbook. Sounds like
corruption!


--
Regards
Frank Kabel
Frankfurt, Germany

Myrna,

The code results in aan errormessage (something like
"error 1004: by application or by object caused error"
(badly (?) translated from Dutch). Execution of the sub halts at the
line (it is yellow) with

ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but when I put the equivalent worksheet formula manually in L1 in that
worksheet
=AANTAL.ALS(B1:B200;"*")
it works perfect.

Notice that AANTAL.ALS is the Dutch version equivalant of countif.

I never used Rob Bovey's Code Cleaner on this workbook, I am a bit
scared to do it. Besides, what in the existing code in other modules
could cause the rejection of this code? The worksheet in question
contains no links to anything else, it also contains no formulas
etc. Just a few numbers and text, nothing more, all togeteher just
one page.

And then, why no problem whatsoever occurs when I do exactly the same
after I copy that sheet to another (new) workbook or to another
existing worbook?

Also strange, the code won't work in any of the sheets on the original
workbook. Some mysterious force refuses to place the formula in L1 or
any other cell.

Jack.

You say the formula "doesn't work". Can you explain what you mean?
Does the code not compile? Does it give a run-time error? Does Excel
not accept it? Is it accepted but the result isn't correct?

Out of curiosity, does the corresponding A1 formula work?

Have you ever used Rob Bovey's Code Cleaner on this workbook? If
not, it might be a good idea. I don't have the url to get it, but
Google should be able to find it.

Frank,

Nothing else makes me think of a corrupt file. This file is our most
inportant file, our whole business depnds on it. It is a large and
rather complicated file with a huge amount of VBA procedures that
more or less automate all kinds of processes. So "just create a new
workbook" is not really an option.

The key question now seems to me: why would a simple subroutine
like this

Sub Try
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"
End Sub

not work in the original sheet (not in any sheet of the original
workbook!) but does what it should do in another (in any existing
or new) workbook?

I think that if my jile was corrupt I would have noticed other
strange behaviour, don't you think so?

Jack.


"Frank Kabel" <[email protected]> schreef in bericht
Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that
when I copy my existing sheet to a new workbook the code in the
sheet module works correct.

How is that possible, in the original workbook it won't work but
after copying the same sheet to a new workbook all problems are
gone if the code is executed in the new workbook.

Jack.

"Frank Kabel" <[email protected]> schreef in bericht
Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &

countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal


=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line
of code. Please test it, I can't find anything wrong, but want
this thing to work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't
work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =


""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal
aantal")
of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be
nothing else in columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =



""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error
caused
by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any
other and perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Jack Sons]

Frank,

In addition why are you testing your code on your production file??

Testing code sounds too heavy for what I did. Two lines of simple code in
the otherwise blank sheet module, it could not cause any havoc, I mean the
only thing that could go wrong was that anyhow it would not work, as was the
case.

I try creating a new workbook, not by copying whole sheets but by copying
the cells of each sheet. I am afraid that will case a lot of problems
because many other wokbooks are linked to it. Also, how do I put all
existing code modules in the new workbook? What about my custom buttons that
start procedures in those code modules?

Jack.

Hi
sometimes such corruptions 'just happen'. I've a similar file issue
with a statement such as
.numberformat="dd.mm.yyyy"

very straight forward, isn't it. But in this one file it creates a
totally different format
d//\m\yyy

Just copying this worksheet to another workbook solved the problem. So
yes I can think of some very strange Excel behaviour.

So if your business really depends on this file (what makes me very
uncomfortable as this is an EXCEL fiel ) you really should try to
- use a recent backup and see if it happens there as well
- copy only the worksheets sheet by sheet to an empty workbook


In addition why are you testing your code on your production file??



--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

Nothing else makes me think of a corrupt file. This file is our most
inportant file, our whole business depnds on it. It is a large and
rather complicated file with a huge amount of VBA procedures that
more or less automate all kinds of processes. So "just create a new
workbook" is not really an option.

The key question now seems to me: why would a simple subroutine like
this

Sub Try
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"
End Sub

not work in the original sheet (not in any sheet of the original
workbook!) but does what it should do in another (in any existing or
new) workbook?

I think that if my jile was corrupt I would have noticed other strange
behaviour, don't you think so?

Jack.

Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that when
I copy my existing sheet to a new workbook the code in the sheet
module
works
correct.

How is that possible, in the original workbook it won't work but
after
copying the same sheet to a new workbook all problems are gone if
the code is executed in the new workbook.

Jack.

"Frank Kabel" <[email protected]> schreef in bericht
Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &

countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal


=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line
of code. Please test it, I can't find anything wrong, but want
this thing to work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't
work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =


""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal
aantal")
of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be
nothing else in columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =



""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error
caused
by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any
other and perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Myrna Larson]

Then the workbook MUST be corrupt, at least the VBA part of it.

You say you are afraid to run the code cleaner. Why? Create a copy of the
workbook and run CodeCleaner on the copy. If it corrects the problem, you now
know the cause. If it doesn't, delete the cleaned copy and go back to your
original.

Myrna,

The code results in aan errormessage (something like
"error 1004: by application or by object caused error"
(badly (?) translated from Dutch). Execution of the sub halts at the line
(it is yellow) with

ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but when I put the equivalent worksheet formula manually in L1 in that
worksheet
=AANTAL.ALS(B1:B200;"*")
it works perfect.

Notice that AANTAL.ALS is the Dutch version equivalant of countif.

I never used Rob Bovey's Code Cleaner on this workbook, I am a bit scared to
do it. Besides, what in the existing code in other modules could cause the
rejection of this code? The worksheet in question contains no links to
anything else, it also contains no formulas etc. Just a few numbers and
text, nothing more, all togeteher just one page.

And then, why no problem whatsoever occurs when I do exactly the same after
I copy that sheet to another (new) workbook or to another existing worbook?

Also strange, the code won't work in any of the sheets on the original
workbook. Some mysterious force refuses to place the formula in L1 or any
other cell.

Jack.

You say the formula "doesn't work". Can you explain what you mean? Does the
code not compile? Does it give a run-time error? Does Excel not accept it? Is
it accepted but the result isn't correct?

Out of curiosity, does the corresponding A1 formula work?

Have you ever used Rob Bovey's Code Cleaner on this workbook? If not, it might
be a good idea. I don't have the url to get it, but Google should be able to
find it.

Frank,

Nothing else makes me think of a corrupt file. This file is our most
inportant file, our whole business depnds on it. It is a large and rather
complicated file with a huge amount of VBA procedures that more or less
automate all kinds of processes. So "just create a new workbook" is not
really an option.

The key question now seems to me: why would a simple subroutine like this

Sub Try
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"
End Sub

not work in the original sheet (not in any sheet of the original workbook!)
but does what it should do in another (in any existing or new) workbook?

I think that if my jile was corrupt I would have noticed other strange
behaviour, don't you think so?

Jack.


"Frank Kabel" <[email protected]> schreef in bericht
Hi
maybe a corrupt file?
Just create a new workbook

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

It is even more weird. To my total confusion I discovered that when I
copy
my existing sheet to a new workbook the code in the sheet module
works
correct.

How is that possible, in the original workbook it won't work but
after
copying the same sheet to a new workbook all problems are gone if the
code
is executed in the new workbook.

Jack.

"Frank Kabel" <[email protected]> schreef in bericht
Hi
try the following:
ActiveCell.FormulaR1C1 = "=""totaal aantal = "" &

countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"")"


--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

I used
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal


=""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*"
")"

took it right from your answer, but it halts again at that line
of
code.
Please test it, I can't find anything wrong, but want this thing
to
work.

I also tried the simpler

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "countif(RC[-10]:R[199]C[-10],""*"")"

but that won't work either.

With the Dutch version of the worsheet function it also won't
work:

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "AANTAL.ALS(RC[-10]:R[199]C[-10];" * ")"

but when I put manually in L1 in the worksheet
=AANTAL.ALS(B1:B200;"*") it works perfect.

It drives me crazy.

Jack.



"Frank Kabel" <[email protected]> schreef in bericht
Hi
not tested but try:
Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=" & "totaal aantal =


""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

--
Regards
Frank Kabel
Frankfurt, Germany

Hi all,

I want to put in L1 the total number (in Dutch "totaal
aantal")
of
cells in
B1 to B200 and H1 tot H200 that contain text (there can be
nothing
else in
columns B and H). I tried

Range("L1").Select
ActiveCell.FormulaR1C1 = _
"=""totaal aantal =



""&countif(RC[-10]:R[199]C[-10],""*"")+countif(RC[-4]:R[199]C[-4],""*""
)"

but it results in aan errormessage (something like "error
caused
by
the
application" (roughly translated from Dutch).

Why? Should I use specialcells in stead of this formula? Any
other
and
perhaps more suitable way to accomplish this?

Your help will be appreciated.

Jack Sons
The Netherlands

 

[Jack Sons]

Frank,

I copied everything to a new workbook but more or less to no avail, the
formulas disappeared (thousands of them, some extremely large and/or
complicated, the new workbook contains only values). Smart copying won't
help either.
But, this only is the case in my largest and - of course - most important
sheet (A1 to FH700). Another sheet (A1 to FL700 but with considerably less
formulas) did get its formulas. Is there a limit to the amount of formulas
that can be copied?

What now?

Jack.

 

[Jack Sons]

Frank,

Disregard my post about not getting the formulas, I looked in a part of the
worksheet where long ago I replaced them with there values. Think I got a
bit stressed too much. Sorry for that, I should have stayed cool.

Remains the question how to get the code modules across without confusing
the system. I mean that the code refers to a wrong worksheet because I can't
copy it under the same name tot the same directory. If copied to a different
directory the code modules perhaps get confused and the buttons can't find
their code anymore. The same if I copy it to the old directory but with a
new filename.

Jack.

 

[Myrna Larson]

From the VB Editor, click on your project, select a module, go to the File
menu and select Export. Repeat for all modules, including the ThisWorkbook
module and all sheet modules if you have used them.

In the new workbook, use the Import function.

You'll have to double check all of the buttons, that they call a macro in the
new workbook rather than the old one. An easy way to find problems is to
rename the old book, outside of Excel. Then when you click on the button,
you'll get the message about the workbook not being found.

 

[Jack Sons]

Myrna and Frank,

Thanks for your assistance. I copied all of the original workbook and the
original problem does not occur in the new workbook. I even works much
faster. But it seems I got a new problem, not unlike the first. In another
sheet de code that should insert a formula in a cell (code that always
worked nicely) won't work now, same error message as before. If I can't
solve that problem I might again ask for help. Without help of gurus like
you both I would still be in the Excel stone ages and remain there.

Jack.