formula lost

[Zrx]

Hello Guys,

I have been chasing this little devil for some time now and I'm fed up!!

I put a formula in cells formula bar and whenever it decides by itself poof its gone!!!

example formula would be say (in formula bar)

=SUMPRODUCT(--($A$5:$A$200>(h4/24)),--($A$5:$A$200<(h5/24)))

in say cell g31 with answer in cell 31.

Now it just dawn on me, this is an array correct so ctrol,shift, enter may be needed?? If so why do other cells with same idea work??

[VBA Noob]

Hi,

It's not an array.

Are you clicking the X instead of the tick or pressing return

VBA Noob

[Zrx]

clicking the "green" check mark and also tried enter key. Seems to work sometimes and not others. Did a total re-install of excell 2003 which made no diff.

[VBA Noob]

Is it a new worksheet or one with code

VBA Noob

[Zrx]

One with code for other things.

[VBA Noob]

Maybe the code has worksheet events which is causing the problem

VBA Noob

[Zrx]

Hello Noob,

Can we take this further then just events?? As it still is not solved.

Anyone have any ideas?

[Bryan Hessey]

Hello Noob,

Can we take this further then just events?? As it still is not solved.

Anyone have any ideas?

When you enter a formula in the fx bar, whether by typing or 'click' select, do you always press enter or click the green Tick?

Is it always new formula that disappear? or does an old formula disappear also?

Do you have any Events code, especially BeforeDoubleClick ?

---

[Zrx]

do you always press enter or click the green Tick

Yes alwats click the tick and new or old no matter.

What I have found though and maybe you can explain.

The textbox properties I had source as the cell. I changed this to "none" and now get value from VBA code.

TextBox1.Value = Range("h28").Value

K30 is cell real formula is in.
H28 has =k30 in fx bar kinda of a helper cell I guess. Keeps formula away from data. BUT this should not matter really should it??

Of coarse all this said TextBox1.Value = Range("k30").Value will work now with the propertire "none" being key I think as it puts focus or whatever on cell and wipes out formula.

I'm I close??

[Bryan Hessey]

Yes alwats click the tick and new or old no matter.

What I have found though and maybe you can explain.

The textbox properties I had source as the cell. I changed this to "none" and now get value from VBA code.

TextBox1.Value = Range("h28").Value

K30 is cell real formula is in.
H28 has =k30 in fx bar kinda of a helper cell I guess. Keeps formula away from data. BUT this should not matter really should it??

Of coarse all this said TextBox1.Value = Range("k30").Value will work now with the propertire "none" being key I think as it puts focus or whatever on cell and wipes out formula.

I'm I close??

hmm, confusion.


TextBox1.Value = Range("h28").Value will set the textbox with the value shown in H28, which will be the same value as K30 if the '=h28' formula is there.
It will not affect k30 not h28

How h28 or k30 get their value is immaterial, that value will be shown.

if your 'button' (to apply updates from the userform) has

Range("h28").Value = TextBox1.Value

then the h28 cell will be affected and lose it's formula

Range("k30").Value = TextBox1.Value

then the value in h28 will change (the formula will remain =k30), and the value in k30 will remove any formula.

Does this help your thoughts?
---