时间戳

首页 > 解决方案 > 在 Macos 上运行 marco 时下标超出范围

vba - 在 Macos 上运行 marco 时下标超出范围

问题描述

我正在努力解决代码中超出范围的错误下标：

Set wsCondition = wbCondition.Worksheets(2)

这段代码在 Windows 上运行得很好，但是当我在 MacOS 上尝试时，会发生错误。我是 VBA 的新手，我完全不明白为什么会发生这个错误。

提前感谢你们的建议。

Option Explicit
Public Sub btn1_Click()
Dim i As Double
Dim N As Double
Dim strKeyWord As String
Dim myCount As Integer
Dim OrderCount As Integer
Dim SubTotal As Range, Country As Range, DisCount As Range, Quantity As Range, ItemName As Range, OrderName As Range, RequiredData As Range

Dim wsOrder As Worksheet
Dim wsResult As Worksheet
Dim wsCondition As Worksheet
Dim wbOrder As Workbook
Dim wbCondition As Workbook



Dim OrderFile As String
Dim ConditionFile As String

'Open Order wb
OrderFile = Application.GetOpenFilename()
Set wbOrder = Workbooks.Open(OrderFile)

Set wsOrder = wbOrder.Worksheets(1)

'Open Condition wb
ConditionFile = Application.GetOpenFilename()

Set wbCondition = Workbooks.Open(ConditionFile)
Set wsCondition = wbCondition.Worksheets(2)
Set wsResult = wbCondition.Worksheets(1)


With wsResult
    .Range("A1").Value = "Product code"
    .Range("B1").Value = "Order Condition"
    .Range("C1").Value = "Order Name"
    .Range("D1").Value = "Subtotal"
    .Range("E1").Value = "Discount"
    .Range("F1").Value = "Quantity"
    .Range("G1").Value = "Item Name"
    .Range("H1").Value = "Country"

    .Range("A1").Characters(1, 12).Font.Bold = True
    .Range("B1").Characters(1, 16).Font.Bold = True
    .Range("C1").Characters(1, 16).Font.Bold = True
    .Range("D1").Characters(1, 12).Font.Bold = True
    .Range("E1").Characters(1, 12).Font.Bold = True
    .Range("F1").Characters(1, 12).Font.Bold = True
    .Range("G1").Characters(1, 12).Font.Bold = True
    .Range("H1").Characters(1, 12).Font.Bold = True

    .Range("A1").WrapText = True
    .Range("B1").WrapText = True
    .Range("C1").WrapText = True
    .Range("D1").WrapText = True
    .Range("E1").WrapText = True
    .Range("F1").WrapText = True
    .Range("G1").WrapText = True
    .Range("H1").WrapText = True

    .Range("A1").ColumnWidth = 13
    .Range("A1").RowHeight = 17
    .Range("B1").ColumnWidth = 12
    .Range("B1").RowHeight = 17
    .Range("C1").ColumnWidth = 14.5
    .Range("C1").RowHeight = 17
    .Range("G1").ColumnWidth = 99
    .Range("G1").RowHeight = 17
End With

'using the CountA ws function (all non-blanks)
myCount = Application.CountA(wsCondition.Range("A:A"))

For i = 2 To myCount Step 1
    strKeyWord = wsCondition.Range("A" & i)
    wsOrder.Range("R:R").AutoFilter Field:=1, Criteria1:="=*" & strKeyWord & "*"

    If wsOrder.Cells(Rows.Count, 1).End(xlUp).Row > 1 Then

        Set SubTotal = wsOrder.Range("I2", wsOrder.Range("I" & Rows.Count).End(xlUp))
        Set Country = wsOrder.Range("AG2", wsOrder.Range("AG" & Rows.Count).End(xlUp))
        Set DisCount = wsOrder.Range("N2", wsOrder.Range("N" & Rows.Count).End(xlUp))
        Set Quantity = wsOrder.Range("Q2", wsOrder.Range("Q" & Rows.Count).End(xlUp))
        Set OrderName = wsOrder.Range("A2", wsOrder.Range("A" & Rows.Count).End(xlUp))
        Set ItemName = wsOrder.Range("R2", wsOrder.Range("R" & Rows.Count).End(xlUp))

        Set RequiredData = Union(SubTotal, Country, DisCount, Quantity, OrderName, ItemName)
        RequiredData.SpecialCells(xlCellTypeVisible).Copy
        OrderCount = wsOrder.Range("A2", wsOrder.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count

        With wsResult
            If OrderCount >= 2 Then
                For N = 1 To OrderCount Step 1
                    .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = strKeyWord
                    .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "Available"
                Next N
            Else
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = strKeyWord
                .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "Available"
            End If
                .Cells(.Rows.Count, "C").End(xlUp).Offset(1).PasteSpecial
        End With

    Else

        With wsResult
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = strKeyWord
            .Cells(.Rows.Count, "B").End(xlUp).Offset(1).Value = "No Order"
            .Cells(.Rows.Count, "C").End(xlUp).Offset(1).Value = "N/A"
            .Cells(.Rows.Count, "D").End(xlUp).Offset(1).Value = "N/A"
            .Cells(.Rows.Count, "E").End(xlUp).Offset(1).Value = "N/A"
        End With

    End If
        OrderCount = 0
Next i

wbCondition.Sheets("Result").Activate
wsOrder.AutoFilterMode = False

End Sub

标签： vbaexcel

解决方案

这与此代码是在 Mac 还是 Win 环境中运行无关。您必须检查您打开的文件中是否存在第二个工作表。在您的情况下，您可以添加以下代码

If wbCondition.Worksheets.Count > 1 Then 
   Set wsCondition = wbCondition.Worksheets(2)
else
   ' Do sth in order to fix the error or end the sub
end if

如果下标超出范围，则意味着您尝试访问不存在的工作表。这可能由于以下原因而发生

  • 给 Worksheets 的工作表名称拼写错误。
  • 工作表的名称已更改。工作表已删除。
  • 索引太大，例如您使用了 Worksheets(5) 但只有四个工作表
  • 使用了错误的工作簿，例如 Workbooks(“book1.xlsx”).Worksheets(“Sheet1”) 而不是 Workbooks(“book3.xlsx”).Worksheets(“Sheet1”)

你在这里找到这个

推荐阅读