CODEWITHSUNDEEP
excelvba
NigelH
NigelH

Reputation: 109

How to resolve Range Names without selecting cells

Is it possible to force Excel to resolve All Range Names with VBA when importing a worksheet? In some instances the names do not resolve until individual cells are selected, highlighted in the formula bar then deselected. It appears that if the sheet contains any name that does not exist in the name manager, all subsequent names are ignored.

My application is running the VBA code in a 'manager' workbook, from there I open a second 'template' workbook for generating a test report. Report pages (worksheets) can be appended to the template with a ribbon control. These appended worksheets have various cells with names to automatically fill-in the report from data already in the template workbook. So for example when worksheet("Results") is imported the view shows:

Address     Formula       Value     Display
F18        =WeightAir     335        335
F19        =WeightWater   Name missing in this workbook
B39        =Length        1.1       #NAME?
C47        =Author        N H       #NAME?

If I select cell C47, click in the Formula Bar and back out again the cell value is correctly displayed. Cell B39 remains in error. There are other cells in the same worksheet not getting populated, this is just to show an example. I have tried using VBA to select a range of cells (including all that show #NAME? error) to force calculation but this seems to have no effect

DestWB.Worksheets(ShtCount).Range("A1", "Z65536").Select
DestWB.Worksheets(ShtCount).Calculate
DestWB.Worksheets(ShtCount).Range("A1").Select

I picked up a suggestion to use this which I apply before closing the 'source workbook'

DestWB.Worksheets(ShtCount).UsedRange.Formula = _
          OrigWB.Worksheets(1).UsedRange.Formula

But if the sheet contains any name that does not exist in the name manager, for this workbook, all subsequent (i.e. cells with higher column,row address) names are ignored. Is there a way to force all known names to resolve, leaving only missing values? Any suggestions please?

Upvotes: 3

Views: 172

Answers (1)

EEM
EEM

Reputation: 6659

Although I could not replicate the problem as created by a "copy" of a worksheet, I was able to generate "Formulas in Error due to missing Names".

The following procedure updated the Formulas in Error for which the Names were present in the Workbook, leaving the error only to the nonexistent Name.

Sub Formulas_InError_Refresh()
Dim wb As Workbook
Dim rErr As Range
Dim rArea As Range
    Set wb = ActiveWorkbook 'Change to the required workbook
    Set rErr = wb.Worksheets("Results").UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
    For Each rArea In rErr.Areas
        With rArea
            .Formula = .Formula
    End With: Next
    End Sub

enter image description here

Upvotes: 1

Related Questions