Runtime error '91' on macro execution

Go To StackoverFlow.com

1

I have an Excel 2010 workbook for financial records. On one worksheet I have a menu. On this menu I select a button with an attached macro witch hides the cells which make up the menu and un-hides a transaction input form.

This transaction input for contains several values that are of no consequence here. After entering the data you hit the appropriately labeled enter button. Now when I wrote the doc this button worked fine, it went to the records worksheet inserted a blank row in the correct table went back to the form copied the data went back to the new blank row and inserted the data. It then hit sort newest to oldest on the date column to insure entries are in the correct order. Finally it heads back to the form and clears the data. This worked fine until I decided to hide the history sheet.

When I decided to hide the sheet I re-recorded the macro un-hiding the sheet preforming the operations and re-hiding the sheet. Now when I run the macro it un-hides the sheet. It then hangs with a "Runtime error '91' Object variable or With block variable not set" error. It does not complete the macro, leaving the history sheet visible and unchanged with the form still full of data.

Selecting debug in the options given when the error appears shows this:

  Sub transaction()
'
' transaction Macro
'

'
    Sheets("Income").Select
    Sheets("Transaction History").Visible = True
    Selection.ListObject.ListRows.Add (1)
    Sheets("Income").Select
    Range("B6:G6").Select
    Selection.Copy
    Sheets("Transaction History").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Transaction History").ListObjects("Table9").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Transaction History").ListObjects("Table9").Sort. _
        SortFields.Add Key:=Range("Table9[[#All],[Date]]"), SortOn:=xlSortOnValues _
        , Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Transaction History").ListObjects("Table9"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Transaction History").Select
    ActiveWindow.SelectedSheets.Visible = False
    Range("B6:G6").Select
    Selection.ClearContents
    Rows("6:8").Select
    Range("A8").Activate
    Selection.EntireRow.Hidden = False
    Range("B6").Select
    ActiveCell.FormulaR1C1 = "=R[1]C"
    Rows("7:7").Select
    Selection.EntireRow.Hidden = True
    Range("C6").Select
End Sub

With the row

Selection.ListObject.ListRows.Add (1)

Highlighted in yellow. I do not know any VBA, hence recording macros not writing them, and i can't make heads nor tales of this.

Edit: Okay I have firgured out how to reference the table:

Worksheets("Transaction History").ListObjects("thistory").ListRows.Add (1)

So from there on I am just going to re-write the whole thing, gonna try and do it in VBA rather than record it. Google is my friend. Thanks, for all your help. Any help you continue to give is greatly apreciated.

2012-04-03 21:25
by SpeedCrazy
Just realized it actually hangs before inserting the blank row - SpeedCrazy 2012-04-03 21:32
.Select is the major cause of errors :) You should directly perform the operations. Now regarding Selection.ListObject.ListRows.Add (1) Which sheet is the Selection in and what exactly is that Selection - Siddharth Rout 2012-04-03 21:37
Sorry for my n00bishness here but what exactly do you mean "Directly Preform the operations"? The rest of your comment is just as confusing to me. Sorr - SpeedCrazy 2012-04-03 21:39
Oh wait... are you saying instead of selecting the table and adding a row to it i should directly say tableX add 1 row? (however that is said in VBa) - SpeedCrazy 2012-04-03 21:41
Yup :) See this thread on how to avoid using .Select http://stackoverflow.com/questions/9916342/run-time-error-1004-select-method-of-range-class-failed-vba-2003. Now regarding the Selection, how does Excel Know, which table you are referring to and in which sheet? : - Siddharth Rout 2012-04-03 21:44
I don't know, i recorded it so i assumed by selecting the table and adding a row to it i told excel which table i was referring to. How would i tell it which table i want - SpeedCrazy 2012-04-03 21:54
Excel Macro is not intelligent enough to know what exactly what you want. It still uses code like Selection, Select, SmallScroll etc. You have to explicitly tell it which table (and in which sheet) you are referring to. For example refer to the code Worksheets("Transaction History").ListObjects("Table9") :) Any chance you can upload your excel file? If yes, then you can upload it in wikisend.com and then share the link here : - Siddharth Rout 2012-04-03 22:00
http://wikisend.com/download/713846/upload.xls - SpeedCrazy 2012-04-03 22:06
Is this what you are trying? Worksheets("Transaction History").ListObjects("thistory").ListRows.Add (1)Siddharth Rout 2012-04-03 22:14
Yes i figured that bit out thanks. You've been very helpful, thank you. How do i then paste into that row? Worksheets("Transaction History").ListObjects("thistory").ListRows.Add(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=FalseSpeedCrazy 2012-04-03 22:18
Posted a reply - Siddharth Rout 2012-04-03 22:23


2

Is this what you are trying?

Sub transaction()
    Dim wbI As Worksheet, wbO As Worksheet

    Set wbI = Sheets("Income")
    Set wbO = Sheets("Transaction History")

    With wbO
        .Visible = True

        .ListObjects("thistory").ListRows.Add (1)

        wbI.Range("B6:G6").Copy

        .Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

        With .ListObjects("thistory").Sort
            With .SortFields
                .Clear
                .Add Key:=Range("thistory[[#All],[Date]]"), SortOn:=xlSortOnValues _
                , Order:=xlDescending, DataOption:=xlSortNormal
            End With
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

        .Visible = False
   End With

   With wbI
        .Range("B6:G6").ClearContents
        .Rows("6:8").Hidden = False
        .Range("B6").FormulaR1C1 = "=R[1]C"
        .Rows(7).Hidden = True
   End With

End Sub
2012-04-03 22:23
by Siddharth Rout
Yes. Thanks many times over - SpeedCrazy 2012-04-03 22:27