☰

使用变量范围而不是固定范围时，VBA 宏崩溃

tag-icon • tag-icon vba array range
使用变量范围而不是固定范围时，VBA 宏崩溃

我尝试在代码中添加注释以使其更易读，特别是因为我确信有更好的方法可以做到这一点！

我有一张包含特定产品购买量和销售额的表格。例如

ID  PURCHASES PRICES SALES CALCULATED VALUE
1   X         X      X     X
1   X         X      X     X
1   X         X      X     X
2   X         X      X     X
2   X         X      X     X
2   X         X      X     X

我正在尝试运行 FIFO 计算，从而计算出每件特定产品的销量。总体而言，计算运行正常，但我很难让它针对每个产品 ID 单独运行。

对于每种产品，我尝试根据如下所示的产品起始和结束位置来选择计算范围，然后选择该范围进行计算。

但是，当我在函数中使用我的startRow和endRow变量时Range()，应用程序就崩溃了。

当我手动使用数字时，它可以完美地运行（尽管仅适用于我选择的产品）。

您能告诉我我做错了什么吗？如果您能提供任何关于如何改进我的代码的建议，我将不胜感激！

Sub RowCount()
    Dim sell As Long
    Dim i As Integer
    Dim j As Integer
    Dim r As Integer
    Dim cnt As Long
    Dim sale As Long
    Dim startRow As Integer
    Dim endRow As Integer
    Dim cStage As Integer
    Dim pID As New Collection, ID
    Dim productIDs() As Variant
    Dim currProduct As Long
    Dim ar As Variant
    Dim Var As Variant

    'CLEAR PREVIOUS
    Range("G10:G65536").ClearContents

    'COLLECT ALL PRODUCTS
    productIDs() = Range("B10", Range("B65536").End(xlUp)) 'IDs

    On Error Resume Next
    For Each ID In productIDs
        pID.Add ID, ID
    Next

    'CALCULATE SALES FOR EACH PRODUCT
    For currProduct = 1 To pID.Count
        '
        ' FIND START AND END ROW FOR currProduct
        '
        cStage = 0 'searching for the first row
        'calculate start and end row numbers for product
        For r = 1 To Rows.Count 'for each row
            If pID(currProduct) = Range("B" & r) And cStage = 0 Then 'found first of current product ID
                startRow = r 'first row is current row
                cStage = 1 ' moving to searching for the end row
            ElseIf pID(x) <> Range("B" & r) And cStage = 1 Then 'found the first row and now passed the final row
                cStage = 2 'search no more
                endRow = r - 1 'final product was previous row
            End If
        Next r

        'Working (for product number 2)
        ar = Range("C14:C19") 'Purchases
        Var = Range("D14:D19") 'Prices

        'Not Working
        'ar = Range("C" & startRow, Range("C" & endRow).End(xlUp)) 'Purchases
        'Var = Range("D" & startRow, Range("D" & endRow).End(xlUp)) 'Prices

        '
        ' PERFORM CALCULATIONS ON PRODUCT SALES/EACH ROW
        ' WORKS WHEN currProduct's RANGE IS CORRECT
        '
        For i = 10 To Range("A" & Rows.Count).End(xlUp).Row
            If pID(currProduct) = Range("B" & i) Then
                sell = Range("E" & i)
                sale = 0
                j = 1
                Do While sell > 0 And pID(currProduct) = Range("B" & i)
                    cnt = ar(j, 1)
                    ar(j, 1) = IIf(ar(j, 1) > sell, ar(j, 1) - sell, 0) 'iif
                    sell = sell - (cnt - ar(j, 1))
                    sale = sale + (cnt - ar(j, 1)) * Var(j, 1)
                    j = j + 1
                Loop
                Range("G1000").End(xlUp)(2) = sale 'output the sales
            End If
        Next i 'next sale
    Next currProduct 'next product
End Sub

答案1

必须使用关键字为变量分配范围Set。

Set ar = Range("C" & startrow, Range("C" & endrow).End(xlUp))

答案2

您还可以使用细胞工作表方法：

Set ar = sheet.Range(sheet.Cells(startrow, 3), sheet.Cells(endrow, 3))

此语法为 Range 函数提供了起点和终点，每个起点和终点都是工作表中的单个单元格。此处的列不是以字母形式指定，而是以相应的索引指定，因此3。我建议明确指定要在其上创建或使用所需范围的工作表。因此最好

Set ar = sheet.Range(...)

超过

Set ar = Range(...)

对我来说，后者读起来就像 Excel 凭空创造了这个范围。如果你给 VBA 只是范围关键字，它可能与活动工作表相关，但无法知道这是否可靠，并且是否会在所有版本中继续这样工作。如果您想用范围定位活动工作表，请使用以下命令：

Set ar = ActiveSheet.Range(...)

希望有帮助 ;o)

相关内容