CODEWITHSUNDEEP
excelvba
jmaz
jmaz

Reputation: 527

Method 'Range of object' _Worksheet' failed -- but only if I call the sub from another sub

The failure occurs at the line beginning with "Set x = "

This may be the big clue: When I run this code as a separate sub, it runs fine. I only get the error when I call this sub from another sub. Why should this be the case?

Option Explicit

Sub EIRPChartUpdate()
'update EIRP Chart
Application.ScreenUpdating = False

    Dim x As Range, y1 As Range, y2 As Range, y3 As Range, y4 As Range
    Dim points As Long

    Set EIRPChart = Sheets("EIRP Chart")
    Set EIRPSummary = Sheets("EIRP Summary")

    'get last row of EIRP Summary
    lastEIRPSummaryRow = EIRPSummary.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

    'count the number of data points
    points = lastEIRPSummaryRow - [ESDataRow1].Row + 1

'set independent and dependent data ranges
With EIRPSummary
    Set x = .Range(Cells([ESDataRow1].Row, [ESCaseNum].Column), Cells(lastEIRPSummaryRow, [ESCaseNum].Column))
    Set y1 = .Range(Cells([ESDataRow1].Row, [ESPriSpec100].Column), Cells(lastEIRPSummaryRow, [ESPriSpec100].Column))
    Set y2 = .Range(Cells([ESDataRow1].Row, [ESPriPred100].Column), Cells(lastEIRPSummaryRow, [ESPriPred100].Column))
    Set y3 = .Range(Cells([ESDataRow1].Row, [ESPriPred95].Column), Cells(lastEIRPSummaryRow, [ESPriPred95].Column))
    Set y4 = .Range(Cells([ESDataRow1].Row, [ESPriPred90].Column), Cells(lastEIRPSummaryRow, [ESPriPred90].Column))
End With

'more code ...

Upvotes: 1

Views: 84

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

As follow up from comments,

Cells should be fully qualified, i.e. you should specify sheet to which cells belongs:

With EIRPSummary
    Set x = .Range(.Cells([ESDataRow1].Row, [ESCaseNum].Column), .Cells(lastEIRPSummaryRow, [ESCaseNum].Column))
    Set y1 = .Range(.Cells([ESDataRow1].Row, [ESPriSpec100].Column), .Cells(lastEIRPSummaryRow, [ESPriSpec100].Column))
    Set y2 = .Range(.Cells([ESDataRow1].Row, [ESPriPred100].Column), .Cells(lastEIRPSummaryRow, [ESPriPred100].Column))
    Set y3 = .Range(.Cells([ESDataRow1].Row, [ESPriPred95].Column), .Cells(lastEIRPSummaryRow, [ESPriPred95].Column))
    Set y4 = .Range(.Cells([ESDataRow1].Row, [ESPriPred90].Column), .Cells(lastEIRPSummaryRow, [ESPriPred90].Column))
End With

Upvotes: 1

Related Questions