Weird Cell Behaviour

M

Matt

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
 
M

Matt

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

Dave Peterson

First, I think I would have use:

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
 
D

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)
 
M

Matt

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
 
D

Dave Peterson

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.
 
M

Matt

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
 
R

Ron Rosenfeld

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
 
D

Dave Peterson

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

Don't leave us hanging!
 
M

Matt

Dave said:
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
 
M

Matt

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
 
D

Dave Peterson

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

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

And avoid the cell if you want.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top