CODEWITHSUNDEEP
vbaexcelobject
Cameron Bradley
Cameron Bradley

Reputation: 99

Excel VBA - Object reference not set to an instance of an object

I'm using a long process which creates and populates a series of 'route' workbooks and populates a master 'summary' workbook while doing this.

In brief, the error occurs immediately at the start of importing the data from the route workbook as soon as I try to use the relevant worksheet in the summary.

While quoting the entire code would be impractical, I have copied from where I think to be 'relevant' here:

        ' Get Route Number
    RNum = wshCtrl.Cells(2, 2 + i).Value    ' Number for routes being processed (blank if not processed)
    RawRNum = wshCtrl.Cells(4, 2 + i).Value ' Raw Number
    ' Get Route Direction
    RDir = wshCtrl.Cells(9, 2 + i).Value
    ' Get Name of Worksheet
    NamewshRoute = "Route " & RawRNum & " - " & RDir
    ' Check if Route Worksheet exists
    Set wshRoute = Nothing
    On Error Resume Next
    Set wshRoute = Sheets(NamewshRoute)
    On Error GoTo 0

    ' If Route Worksheet doesn't exist and Route being processed
    If wshRoute Is Nothing And RNum <> "" Then
        ' Create Route Worksheet
        ' Copy Template
        wshTemplate.Copy After:=wshTemplate
        ' Rename Copied Template
        Worksheets("Template (2)").Name = NamewshRoute
        ' Set as Route Worksheet
        Set wshRoute = Sheets(NamewshRoute)
        ' Enter Route Number and Direction
        wshRoute.Cells(2, 3).Value = RNum
        wshRoute.Cells(2, 4).Value = RDir

    ' If Route Worksheet doesn't exist and Route isn't being processes
    ElseIf wshRoute Is Nothing Then
        ' Do Nothing

    ' Else Route Worksheet already exists and Route being processed
    Else

        ' Update Progress Bar
        Percent = j / (NumRoutes + 0.5) - (1 / (NumRoutes + 1) * 8 / 8)
        RefreshStatusBar Percent, "Processing " & NamewshRoute, "Initialising Route"

        ' Update Date
        wshRoute.Range("AW9").Value = Month & Year

The error occurs on one of the following two lines: 

wshRoute.Cells(2, 3).Value = RNum

or

wshRoute.Range("AW9").Value = Month & Year

depending on whether the worksheet exists or not.

Note that:

  1. Option Explicit is present at the top of the code.
  2. As far as I can tell all variables are defined and set appropriately.
  3. I have used the debugging process and using 'Add Watch' have confirmed that wshRoute is set appropriately and so are RNum, Month and Year.
  4. wshRoute.Cells(2, 3).Value returns the correct value and so does wshRoute.Range("AW9").Value.
  5. The only inconsistency I can see is that when I look further into the properties of wshRoute, the properties 'OnCalculate' through to 'OnSheetDeactivate' all have a value of . Not sure if this is relevant or not.

This code worked fine (I have been using for approximately a year) until I added another macro at the start. The new macro opens a different workbook and creates 'data' files. It doesn't interact with the 'Summary' workbook in any way. If I run the code without the extra new macro, it runs fine again.

Any suggestions please fire them through. This is high priority for me so I will be testing promptly.

Thanks, Cameron

Upvotes: 2

Views: 4922

Answers (2)

Cameron Bradley
Cameron Bradley

Reputation: 99

After a bit of back and forth... thanks to all those who contributed.

The problem was I was trying to modify protected sheets

I found this by placing Application.DisplayAlerts = False before the line causing the error. When this happened, the original 'Object reference not set to an instance of an object' error was substituted for a 'The cell or chart that you are trying to change is protected and therefore read-only.' error.

I fixed it by making sure all of my worksheets were unprotected when the macro was running.

On a side note - I got caught out with not qualifying my sheets with workbooks.

Thanks again for all of your suggestions.

Upvotes: -1

Tim Williams
Tim Williams

Reputation: 166835

Try adding these debug statements and see what you get:

Set wshRoute = Sheets(NamewshRoute)

Debug.Print wshRoute Is Nothing
Debug.Print wshRoute.Name & " is in workbook " & wshRoute.Parent.Name
Debug.Print wshRoute.Cells(2, 3) Is Nothing
Debug.Print "RNum is type " & TypeName(RNum)

wshRoute.Cells(2, 3).Value = RNum

Upvotes: 2

Related Questions