执行时Excel运行时错误'91' [英] Excel Run-time error '91' while executing

问题描述

我有一个Excel 2010工作簿，用于财务记录.在一个工作表上，我有一个菜单.在此菜单中，我选择一个带有附加宏的按钮，该宏将隐藏组成菜单的单元格并取消隐藏交易输入表单.

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

此事务输入表单包含几个在这里无关紧要的值.输入数据后，您点击了带有适当标签的回车按钮.现在，当我编写文档时，此按钮可以正常工作；它转到记录工作表，在正确的表中插入空白行，返回到表单，复制数据，返回到新的空白行，然后插入数据.然后，它在日期列上按从新到旧的顺序进行排序，以确保输入顺序正确.最后，它返回表格并清除数据.在我决定隐藏历史记录表之前，此方法一直很好.

This transaction input form 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 ensure 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.

当我决定隐藏工作表时，我重新记录了宏，取消隐藏工作表以执行操作并重新隐藏工作表.现在，当我运行宏时，它将取消隐藏工作表.然后，它挂起并显示运行时错误'91'对象变量或未设置块变量"错误.它不会完成宏，使历史记录表可见且保持不变，窗体仍充满数据.

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

与行

Selection.ListObject.ListRows.Add (1)

以黄色突出显示.我不知道任何VBA，因此记录了宏而不写它们，我对此也无所适从.

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.

好的，我已经弄清楚了如何引用该表:

Okay I have firgured out how to reference the table:

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

所以从那里开始，我将重新编写整个内容，然后尝试在VBA中进行操作，而不是记录下来. Google是我的朋友.感谢你的帮助.您继续给予的任何帮助都非常可贵.

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.

推荐答案

这是您要尝试的吗?

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

这篇关于执行时Excel运行时错误'91'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！