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

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

On 17 Oct 2005 15:28:13 -0700, "Matt" <web1000@shaw.ca> 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

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

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

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

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

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

Ron, Dave,

I got it to work

Thanks for your help!

Matt

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