Formulas not filling in without F2+enter

[Zachary Harrison]

Hi all-

I am using vba code to setup a few blph() functions in a workbook. If
you are not familiar with bloomberg or this function, please read on as
my problem is more general!

After my code has finished running the formulas don't execute unless I
goto the cell and hit F2 then enter. Pressing F9 for calculate does
not do the trick.

I tried recording this action of F2+enter but the macro just shows
selecting the cell and setting the formula to what I already had it at.

Is there a way to simulate this F2+enter in vba code? Any other ideas?

Thank you,

Zach

 

[Chip Pearson]

Try something like

Dim Rng As Range
For Each Rng In Selection.SpecialCells(xlCellTypeFormulas)
If Rng.HasArray = True Then
Rng.FormulaArray = Rng.Formula
Else
Rng.Formula = Rng.Formula
End If
Next Rng



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Zachary Harrison]

Thank you for your quick reply, however this has not solved my problem.

 

[Dave Peterson]

Did you select your range and run Chip's code?

Another option.
Select your range (all the whole worksheet) and

edit|replace
what: = (equal sign)
with: = (equal sign)
replace all

 

[HEIM Dominique]

Force recalculation with two instructions :

Worksheets("XXX").EnableCalculation = False
Worksheets("XXX").EnableCalculation = True

GL

 

[David McRitchie]

Hi Zach,
I've not run across what Dominique posted before, so unless you
have done that yourself, I would think....

More likely you have calculation turned off, to turn back on
Application.Calculation = xlCalculationAutomatic

or use Tools, calculation (tab), calculation: automatic

Often gets turned of by a macro (or addin) that turns calculation
off, but neglects to restore calculation upon exiting or abnormal termination.

If it is the BLPH function you are using F2 on,
perhaps you have a space before the equal size, check
to see what =ISTEXT(C3) if C3 is the cell address function is in
If it shows Text then it is not recognized as a function by as text.

If it is a constant that you use F2 then enter on and it then works
it might be because you changed the format, or because you have
spaces or non-breaking spaces coming from
HTML so cells are not recognized as numbers until they are reentered
so I would suggest running the TRIMALL macro
http://www.mvps.org/dmcritchie/excel/join.htm
this will change constants not formulas.

HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm