A macro VBA falha ao usar intervalos variáveis em vez de fixos

Question

Eu tentei incluir comentários com o código para torná-lo legível, especialmente porque tenho certeza de que há maneiras melhores de fazer isso!

Eu tenho uma tabela com compras e vendas para um produto específico. Por exemplo,

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

Eu estou tentando executar um cálculo FIFO em que ele calcula quantos são vendidos por produto específico. O cálculo funciona bem no geral, mas estou com dificuldades para que ele seja executado especificamente para cada ID de produto.

Para cada produto, tentei selecionar o intervalo calculado com base em descobrir onde o produto é iniciado e o produto termina conforme abaixo e, em seguida, selecione esse intervalo para fazer o cálculo.

No entanto, quando eu uso minhas variáveis startRow e endRow em uma função Range() , acabei de obter a falha do aplicativo.

Quando uso os números manualmente, funciona perfeitamente (embora apenas para o produto que selecionei).

Você tem algum conselho sobre o que estou fazendo de errado com isso? Também ficaria grato por qualquer dica de como melhorar meu código!

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

Os intervalos devem ser atribuídos a variáveis usando a palavra-chave Set .

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

    

Answer 2

Você também pode criar intervalos usando o método Células da planilha:

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

Essa sintaxe fornece à função Range um ponto inicial e um ponto final, cada um dos quais é uma única célula na planilha. A coluna não é especificada como uma letra aqui, mas como o respectivo índice, portanto, o 3 . Eu recomendaria especificar explicitamente a planilha na qual você deseja criar ou trabalhar com o intervalo desejado. Então prefira

Set ar = sheet.Range(...)

mais de

Set ar = Range(...)

Para mim, o último lê como o Excel está criando o intervalo fora do ar. Se você der ao VBA apenas a palavra-chave Range , provavelmente ele está relacionado à planilha ativa, mas não há como saber se isso é confiável e continuará funcionando dessa maneira em todas as versões futuras. Se você deseja segmentar a planilha ativa com seus intervalos, use:

Set ar = ActiveSheet.Range(...)

Espero que ajude; o)