Weird Cell Behaviour
 

Guys,

I have the following formula:

Range("G1").Formula = "=count(R5C4:R65000C4)"

Which counts filled cells in column D. It comes to the right result of
27, which is then displayed on the cell. (G1)

I then pick this value up into a variable for further calculations
which were wrong...

Tq = Range("G1").Value

So I checked the value of the variable Tq:

Range("g2").Value = Tq

and it gives 1362 ... I wonder how this is?

Also when i have the macro paste special values G1 into G1 it goes from
27 (correct) to 1362 (wrong).

What am I doing wrong?

Matt

Weird Cell Behaviour
 

strangely enough ... this variable is always 1362 ... no matter whats
in G1 . no clue why that is :(

Weird Cell Behaviour
 

First, I think I would have use:


Matt wrote:

Guys,

I have the following formula:

Range("G1").Formula = "=count(R5C4:R65000C4)"

Which counts filled cells in column D. It comes to the right result of
27, which is then displayed on the cell. (G1)

I then pick this value up into a variable for further calculations
which were wrong...

Tq = Range("G1").Value

So I checked the value of the variable Tq:

Range("g2").Value = Tq

and it gives 1362 ... I wonder how this is?

Also when i have the macro paste special values G1 into G1 it goes from
27 (correct) to 1362 (wrong).

What am I doing wrong?

Matt

--

Dave Peterson

Weird Cell Behaviour
 

Darn fingers!

First, I think I would have used:

Range("G1").FormulaR1C1 = "=count(R5C4:R65000C4)"

And this should count the number of cells that contain numbers--not just filled
with anything (text or formulas).

If you wanted anything, you could have used:

Range("G1").Formular1c1 = "=counta(R5C4:R65000C4)"

As for the descrepancy between the values, do you have calculation set for
automatic?

(Tools|Options|Calculation tab)





Matt wrote:

Guys,

I have the following formula:

Range("G1").Formula = "=count(R5C4:R65000C4)"

Which counts filled cells in column D. It comes to the right result of
27, which is then displayed on the cell. (G1)

I then pick this value up into a variable for further calculations
which were wrong...

Tq = Range("G1").Value

So I checked the value of the variable Tq:

Range("g2").Value = Tq

and it gives 1362 ... I wonder how this is?

Also when i have the macro paste special values G1 into G1 it goes from
27 (correct) to 1362 (wrong).

What am I doing wrong?

Matt

--

Dave Peterson

Weird Cell Behaviour
 

I added the R1C1 ... makes no difference .... the count formula counts
correctly... the problem is the value of cell G1 is not assigned to my
variable ..

It works if I just write a dummy number into G1 then the variable
assumes that value.... but it will not work with the formula in the
cell....

not sure how to change the automatic setting ..

Matt

Weird Cell Behaviour
 

Any chance you're not picking up the right G1. Maybe you're getting G1 from
sheet2 instead of G1 from Sheet1.

I'd be more specific:

Tq = worksheets("sheet1").range("g1").value
and
worksheets("sheet1").Range("g2").Value = Tq

And excel/vba was very forgiving for me, too (with the .formulaR1C1 stuff). But
it might not be with all formulas. I'd still be careful.

Matt wrote:

I added the R1C1 ... makes no difference .... the count formula counts
correctly... the problem is the value of cell G1 is not assigned to my
variable ..

It works if I just write a dummy number into G1 then the variable
assumes that value.... but it will not work with the formula in the
cell....

not sure how to change the automatic setting ..

Matt

--

Dave Peterson

Weird Cell Behaviour
 

nope .. no difference ...

it will put values in the cells it should .. just the wrong values ...

If I put a value NOT a formula inot G1 it will work too ... is there a
trick to picking the value of a cell that contains a formula?

Like, do you have to do a paste special value first to get rid of the
formula?

Matt

Weird Cell Behaviour
 

On 17 Oct 2005 15:28:13 -0700, "Matt" wrote:

strangely enough ... this variable is always 1362 ... no matter whats
in G1 . no clue why that is :(

I cannot reproduce your problem with the code you've supplied. It seems to
work without error here.


--ron

Weird Cell Behaviour
 

Ron, Dave,

I got it to work :)

Thanks for your help!

Matt

Weird Cell Behaviour
 

Aren't you going to share what you did to make it work???

Don't leave us hanging!

Matt wrote:

Ron, Dave,

I got it to work :)

Thanks for your help!

Matt

--

Dave Peterson

Weird Cell Behaviour
 

Dave Peterson wrote:
Aren't you going to share what you did to make it work???

Well ... I really appreciate your and the other guys help over the last
weeks here but this one would embarass me quiet a bit :(

Have to say that I am not a programmer and do this by trial and error
...

Matt

Weird Cell Behaviour
 

Ok.

But i think that we've all had that "slap the forehead really hard" moment.

Matt wrote:

Dave Peterson wrote:
Aren't you going to share what you did to make it work???

Well ... I really appreciate your and the other guys help over the last
weeks here but this one would embarass me quiet a bit :(

Have to say that I am not a programmer and do this by trial and error
..

Matt

--

Dave Peterson

Weird Cell Behaviour
 

To be quiet honest I dont know why it didnt assign the cell value to
the variable but I had the variable assgined before the macro populated
the cells which the count formula counted. Now I assigned the
variable after count has something to count after the macro populates
the cells and it works fine...

Why that is, I have no clue..

Matt

Weird Cell Behaviour
 

You can use some worksheet functions directly in excel, too.

dim TQ as long
tq = application.count(worksheets("sheet1").range("d5:d 65000"))

And avoid the cell if you want.

Matt wrote:

To be quiet honest I dont know why it didnt assign the cell value to
the variable but I had the variable assgined before the macro populated
the cells which the count formula counted. Now I assigned the
variable after count has something to count after the macro populates
the cells and it works fine...

Why that is, I have no clue..

Matt

--

Dave Peterson

Weird Cell Behaviour
 

that may be a good way .. also it gets rid of the R1C1 clutter ...

Matt