Excel error code 91

Posted on 2012-08-11
Last Modified: 2012-08-11
My vba (courtesy of harfang, and much appreciated) has started tripping over after populating the months of July only (july is last data set).

It says error 91 I have f8 through and everything looks ok.

Sub GetTotalNewCodesCategoryOthersCW2012()
    Set qdf = db.QueryDefs("qptotNewCodes 3 parameters")
    Set RstCurrentYearMonth = db.OpenRecordset("SELECT Max(CWSMMTBuilds.BuildYearMonth) AS MaxOfBuildYearMonth FROM CWSMMTBuilds;", dbReadOnly)
    'get current data month
    With RstCurrentYearMonth
    CurrentDataMonth = .Fields("MaxofBuildYearMonth").Value
    End With
    datMonth = #1/1/2012#
    Do While datMonth <= CurrentDataMonth

        qdf.Parameters("BYM") = DateSerial(Format(datMonth, "yyyy"), Format(datMonth, "mm"), 1)
        qdf.Parameters("[Yes=CW Codes/No=SMMT Codes]").Value = True
        qdf.Parameters("[Enter Cars, Bikes, Lcv, Others]").Value = "Others"

          With wks.Cells(12, Format(datMonth, "mm") + 1)

            .Value = "...."
            .Value = qdf.OpenRecordset!Nb
        End With
        datMonth = DateAdd("m", 1, datMonth)

Set RstCurrentYearMonth = Nothing
Set db = Nothing

End Sub

Open in new window

variables defines at top:
Option Explicit
Const DATA_SOURCE = "N:\Data\smmt\CWSMMT2012.mdb"
   Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim qdfMatched As DAO.QueryDef
    Dim intM As Integer
    Dim wks As Worksheet
    Dim RstCurrentYearMonth As DAO.Recordset
    Dim CurrentDataMonth As Date
    Dim datMonth As Date

Open in new window

I call the subs from here:

Sub GetTotalNewCodesSMMT2012()
Set wks = Sheets("2012 Data feed")
    Set db = DAO.OpenDatabase(DATA_SOURCE)
 Call GetTotalNewCodeCounts2012
 Call GetTotalNewCodesCategorySMMT2012
 Call GetTotalNewCodesCategoryCW2012
Call GetTotalNewCodesCategoryCarsSMMT2012
Call GetTotalNewCodesCategoryCarsCW2012
Call GetTotalNewCodesCategoryBikesSMMT2012
Call GetTotalNewCodesCategoryBikesCW2012
Call GetTotalNewCodesCategoryLCVSMMT2012
Call GetTotalNewCodesCategoryLCVCW2012
Call GetTotalNewCodesCategoryOthersSMMT2012
Call GetTotalNewCodesCategoryOthersCW2012

End Sub

Open in new window

Question by:PeterBaileyUk
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Can you specify which line gives the error?

    Can you upload a sample file?

    Author Comment

    Ok I have my files on:N\data\smmt so change data source in spredsheet vba to your location.

    The worksheet I am working on is 2012 datafeed

    itll only return values for 2012-07 as I have cut the data down but two routines are called and you should see it halt on error 91 on the last july column
    LVL 24

    Expert Comment

    When you set the variable wks maybe adding Thisworkbook. will help...

    Such as:

    Set wks = ThisWorkbook.Sheets("2012 Data feed")

    Open in new window


    Author Comment

    ok i will try it.
    LVL 44

    Expert Comment

    by:Martin Liss
    I've downloaded your files. How do i reproduce the error?
    LVL 44

    Accepted Solution

    You are unnecessarily closing the database. Remove these two lines

    Set db = Nothing

    from these routines


    Author Closing Comment

    all working fine now, thank you was the close db issue.

    Author Comment

    Just in case your interested I have another question open re the refreshing of cells, in the sheet just corrected the cell goes to ... then changes to the value but in the sheet overall stats the same code method does not work. heres the ref id:

    ID: 38279161

    might be another 500 to you
    LVL 44

    Expert Comment

    by:Martin Liss
    Please provide the URL for the question.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now