Forcing cell value update/refresh

[#DIV/0]

I'm pretty sure I read the solution to this but after searching for ages
can't find it so sorry if this is a repeat...

I'm placing values into a worksheet from a userform and the only way I can
get them to take the format of the destination cell is by double-clicking
then pressing enter.
Isn't there any way of forcing this ?

 

[Gary''s Student]

Say we want to refresh Z100:

Range("Z100").Calculate
or
Range("Z100").Replace What:="=", Replacement:="="

 

[Dave Peterson]

I'm not sure how you're doing it, but make sure you format the destination cell
before inserting the value.

with activesheet.range("a1")
.numberformat = "General" 'or what you want
.value = me.combobox1.value 'or whatever
end with

If this doesn't help, what are you putting into the cell?

 

[#DIV/0]

Hi
I tried both your suggestions but I'm still not getting results.
I'm putting formulae into various cells on the sheet. Part of the formulae
is made up of text strings I've picked up during the macro such as

ActiveCell.Formula = "=SUM('LISTING'!B1:B" & TheRow & ")"

What I see in the cell is #NAME? until I double-click it and press enter.
Then the cell realises it contains a formula and does the calculation.
Any ideas ?

 

[#DIV/0]

Hi Dave,
Thanks for your response but I'm afraid the wording in my post makes the
problem sound like formatting, but if you look at my reply to Gary's Student
you'll see that's not quite it.

--
David M
WinXP - Office2003 (Italian)

I'm not sure how you're doing it, but make sure you format the destination cell
before inserting the value.

with activesheet.range("a1")
.numberformat = "General" 'or what you want
.value = me.combobox1.value 'or whatever
end with

If this doesn't help, what are you putting into the cell?

 

[Gary''s Student]

Under some circumstances, putting a formula in a cell with code rather than
the Excel UI results in Excel not seeing the result as a formula. Try this
first:

ActiveCell.Clear
ActiveCell.Formula = "=SUM('LISTING'!B1:B" & TheRow & ")"

if this does not work, then try:

ActiveCell.Formula = "=SUM('LISTING'!B1:B" & TheRow & ")"
Application.SendKeys "{F2}"
Application.SendKeys "{ENTER}"
DoEvents

 

[Dave Peterson]

Did you try changing the numberformat to general first?

Hi Dave,
Thanks for your response but I'm afraid the wording in my post makes the
problem sound like formatting, but if you look at my reply to Gary's Student
you'll see that's not quite it.

 

[#DIV/0]

I stopped tracking this post and just looked in. This does it!
Thanks to Gary''s Student.

(makes mental note of SendKeys function)