method range of object _global failed in named range

Question

I have seen the same kind of questions here but not able to solve... please help. Hai sir presently I am in a project to transfer all excel macro did in 2003 format to 2007.The problem i am facing is method range of object _global failed Please provide me a solution.

Please see the code... there range("Phasewt") not updating.... none of the named ranges in this work book are updating, everywhere its showing error 1004: Iam using now office 2007.

Private Sub FormatRowsAndSum(ByVal TotRows As Integer, StartRow As Integer)
    Dim rngFormat As Range

        'adds formatting
    If TotRows > 2 Then
        Set rngFormat = Range(Cells(StartRow + 2, 2), Cells(TotRows + StartRow + 1, 8))
        'Range("B11:H11").Select
        Range(Cells(StartRow + 1, 2), Cells(StartRow + 1, 8)).Select

        Selection.Copy
        rngFormat.Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

        'adds formula
        Set rngFormat = Range(Cells(StartRow + 2, 11), Cells(TotRows + StartRow, 12))
        'Range("K11:L11").Select
        Range(Cells(StartRow + 1, 11), Cells(StartRow + 1, 12)).Select

        Selection.Copy
        rngFormat.Select
        Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

        Application.CutCopyMode = False

    End If

    ActiveSheet.PageSetup.FitToPagesWide = 1

    Cells(TotRows + StartRow + 1, 2) = "TOTALS"
    Cells(TotRows + StartRow + 1, 2).Font.Bold = True
    Cells(TotRows + StartRow + 1, 2).RowHeight = 20

    Range(Cells(TotRows + StartRow + 1, 2), Cells(TotRows + StartRow + 1, 8)).Select
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With


    'writes total weight
    With Cells(TotRows + StartRow + 1, 6)
        .Value = "=SUM(K" & StartRow & ":K" & TotRows + StartRow & ")"
        .Font.Bold = True
        .NumberFormat = "#,##0.00"
    End With
    If Range("ReleaseTo") <> "STR" Then
        Range("PhaseWt") = Cells(TotRows + StartRow + 1, 6) ' writes total wt in cover page
    End If

   'writes total cladding Area
    With Cells(TotRows + StartRow + 1, 7)
        .Value = "=SUM(L" & StartRow & ":L" & TotRows + StartRow & ")"
        .Font.Bold = True
        .NumberFormat = "#,##0.00"
    End With
    If Cells(TotRows + StartRow + 1, 7) > 0 Then
        If Range("ReleaseTo") <> "STR" Then
            Range("PhaseArea") = Cells(TotRows + StartRow + 1, 7) ' writes total Area in cover page
        End If
    End If

    'writes total quantity
    With Cells(TotRows + StartRow + 1, 3)
        .Value = "=SUM(C" & StartRow & ":C" & TotRows + StartRow & ")"
        .Font.Bold = True
        .NumberFormat = "#,##0"
    End With

    Range("B4").Select
End Sub

thanks for your attention.

Welcome to Stack Overflow! You can take the tour first and learn How to Ask a good question and create a Minimal, Complete, and Verifiable example. That makes it easier for us to help you. — Stephen Rauch

Wujaszkun Wujaszkun · Accepted Answer · 2017-01-24T12:26:56

The "object _global failed" error shown because Excel can't find the given ranges in the workbook, so make sure you have the ranges "ReleaseTo", "PhaseWt", "PhaseArea" defined in the somewhere in the file.

EDIT: Try to call your code from this :

Sub test()
With ActiveSheet
.Range("B13").Name = "jobno"
.Range("D13").Name = "bldgno"
.Range("F13").Name = "phaseno"
.Range("D15").Name = "project"
.Range("D16").Name = "Client"
.Range("J15").Name = "location"
.Range("K12").Name = "PhaseWt"
.Range("K13").Name = "PhaseArea"
End With

Call FormatRowsAndSum(1, 10)

End Sub

method range of object _global failed in named range

1 Answers