Force Calculation

[Ken Valenti]

I am working on a worksheet that has numerous formulas using range names that
do not exist in the worksheet.

I have a macro to copy the the sheet to the workbook that has the data along
with the range names to make the formulas valid.

Problem is, the formulas won't re-calculate.

Using Calculate in VBA doesn't work and Cntrl Alt F9 doesn't work
(manually). Edit Enter works, but that's about it.

Is there a way to programatically force a calculation so the formulas
recognize the range names of the new workbook?

Thanks in advance.

 

[Ken Valenti]

Sub AddReport()
'Assume activeworkbook contains the range names and Sheet1 contains the
formulas referring to those range names
Sheet1.Copy before:=Sheets(1)
Application.Calculate
End Sub

 

[Kenneth Hobson]

That is adding a copy of the sheet to the same workbook. Are you saying
that the names in the formulas do not work in the new sheet?

If you are using relative names, you may need to add $ before the names
to get what you expect.

 

[Ken Valenti]

Sorry for the confusion.

Sheet1 resides in a seperate workbook (with the macros) and is copied to the
activeworkbook, which has the data and range names the formulas require.

Sheet1 has no range names defined, only formulas referring to range names
that don't exist in the workbook.

 

[Bernie Deitrick]

Ken,

Try converting all the formulas to strings, then copying the sheet, and
converting the strings back to formulas in both the original and copied
sheets. Example macro below. (Or have your Sheet1 have the formulas as
strings -cell contents with a single quote then the formula string, and
don't bother with the conversion except for in the copy.)

HTH,
Bernie
MS Excel MVP

Sub Test()
Dim myC As Range
Dim myS1 As Worksheet
Dim myS As Worksheet


With Application
.EnableEvents = False
.Calculation = xlCalculationManual
End With

Set myS1 = Workbooks("Name.xls").Worksheets("Sheet1")
For Each myC In myS1.Cells.SpecialCells(xlCellTypeFormulas)
myC.Value = "'" & myC.Formula
Next myC

myS1.Copy before:=ThisWorkbook.Sheets(1)
Set myS = ThisWorkbook.Sheets(1)

For Each myC In myS1.Cells.SpecialCells(xlCellTypeConstants)
If Mid(myC.Value, 1, 1) = "=" Then myC.Formula = Mid(myC.Value, 2)
Next myC

For Each myC In myS.Cells.SpecialCells(xlCellTypeConstants)
If Mid(myC.Value, 1, 1) = "=" Then myC.Formula = Mid(myC.Value, 2)
Next myC

With Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub

 

[Ken Valenti]

Thanks for your help -that's what I do now...

I search for "=" and replace it with "|="

Then search for "|" and replace it with ""

I just need to make sure that everything is converted back to formulas
before I can insert rows & columns, etc.

 

[PBezucha]

Try

Sub RecalculationForced()
Dim sF As String, Cell As Range
For Each Cell In ActiveSheet.UsedRange
If Cell.HasFormula = True Then
sF = Cell.Formula
If Not Cell.HasArray Then
Cell.Formula = sF
Else
Cell.CurrentArray = sF
End If
End If
Next Cell
End Sub