Сбой макроса VBA при использовании переменных Ranges вместо фиксированных

Question

Я попытался включить комментарии в код, чтобы сделать его читабельным, тем более, что я уверен, что есть лучшие способы сделать это!

У меня есть таблица с покупками и продажами для конкретного продукта. Например

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, согласно которому он рассчитывает, сколько продается за конкретный продукт. В целом расчет работает нормально, но у меня возникают трудности с его запуском для каждого идентификатора продукта.

Для каждого продукта я попытался выбрать рассчитанный диапазон на основе определения того, где продукт начинается и заканчивается, как показано ниже, а затем выберите этот диапазон, чтобы выполнить расчет.

Однако, когда я использую мои переменные 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

Answer 1

Диапазоны должны быть присвоены переменным с помощью ключевого слова Set .

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

Answer 2

Вы также можете создать диапазоны, используя метод Cells на рабочем листе:

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

Этот синтаксис дает функции Range начальную точку и конечную точку, каждая из которых представляет собой одну ячейку на листе. Столбец указан не как буква, а как соответствующий индекс, следовательно, 3. Я бы рекомендовал явно указать рабочую таблицу, на которой вы хотите создать или работать с желаемым диапазоном. Так предпочитаю

Set ar = sheet.Range(...)

над

Set ar = Range(...)

Для меня, последнее звучит так, как будто Excel создает диапазон из ничего. Если вы дадите VBA только ключевое слово Range , оно, вероятно, относится к активному рабочему листу, но нет способа узнать, надежно ли оно и будет ли оно работать так же во всех будущих версиях. Если вы хотите настроить таргетинг на активный лист, используйте это:

Set ar = ActiveSheet.Range(...)

Надеюсь, это поможет; о)