VBA Range/Array Problem. Cannot choose multiple ranges.

Question

0.00/5 (No votes)

See more:

Hi all,

Edit: The part where it crashes is where I update

VB

ar = Range("C14:C19") 'Purchases
Var = Range("D14:D19") 'Prices

which is where I select the range with the numbers to

VB

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

which is where the range is selected using the start and end range calculated using the row by row, which the output does come out as the correct start and end rows.

I've tried to include comments with the code to make it readable, especially as I'm sure there are better ways of doing this!

I have a table with purchases and sales for a specific product. E.g.

Date	Name	Buy	Price	Sell	Close	Sold Cost	Addition	FIFO PL
10/01/1900	AA0011	 10 	 2 	 -   	 10 	 -   	 20 	 20 
20/01/1900	AA0011	 -   	 1 	 5 	 5 	 10 	-10 	-30 
20/01/1900	AA0011	 20 	 4 	 -   	 25 	 -   	 80 	 110 
20/01/1900	AA0011	 -   	 2 	 15 	 10 	 50 	-50 	-160 
20/01/1900	AA0012	 10 	 2 	 -   	 10 	 -   	 20 	 20 
20/01/1900	AA0012	 -   	 1 	 5 	 5 	 10 	-10 	-30 
20/01/1900	AA0012	 20 	 4 	 -   	 25 	 -   	 80 	 110 
20/01/1900	AA0012	 -   	 2 	 15 	 10 	 50 	-50 	-160 
20/01/1900	AA0012	 10 	 2 	 -   	 20 	 -   	 20 	 180 
20/01/1900	AA0012	 -   	 1 	 5 	 15 	 20 	-20 	-200 
20/01/1900	AA0016	 20 	 4 	 -   	 20 	 -   	 80 	 80 
20/01/1900	AA0016	 -   	 2 	 20 	 -   	 60 	-60 	-140 
20/01/1900	AA0016	 10 	 2 	 -   	 10 	 -   	 20 	 160 
20/01/1900	AA0016	 -   	 1 	 5 	 5 	 20 	-20 	-180 
20/01/1900	AA0016	 20 	 4 	 -   	 25 	 -   	 80 	 260

I am trying to run a FIFO calculation whereby it calculates how many are sold per specific product. The calculation works fine overall, but I'm having difficulty making it run specifically for each product ID.

I tried to for each product select the calculated range based on finding where the product starts and the product ends as below, then select that range to do the calculation on.

However, when I use my startRow and endRow variables in a Range() function, I just get the application crashing.

When I use the numbers by hand, it works perfectly (although only for the product I've selected).

Do you have any advice as to what I am doing wrong with this? Also I'd be grateful for any tips how to improve my code!

Thanks.

VB

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

Posted 1-Sep-15 22:02pm

DroCLaw

Updated 2-Sep-15 4:09am

Patrice T

v3

Add a Solution

Comments

CHill60 2-Sep-15 9:44am

In what way does the program crash? Which line causes the problem?
It would also be a lot easier to help if you had shared some sample data or at least put the column names in the table

DroCLaw 2-Sep-15 9:59am

Thanks for your comment. I've updated the question to be more detailed with data and column names.

The program crashes where I select the range to do the calculation on using the start row and end row that is calculated in the For r section. It works fine when I use just numbers manually (e.g. type Range(C10:C1000)), but I need the range to be updated each time a new product comes round in the loop, so I have to have a dynamic range.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-09-02T05:34:00

Well it took me a while but I think I've got there.

The problem is in your calculation of endRow and startRow

When stepping through the code I couldn't understand why r (the loop counter) kept getting reset to 0. It is because it is declared as an integer but you are using Rows.Count in your loop which is a Long.

So step 1 is to use

VB

Dim r As Long
Dim startRow As Long
Dim endRow As Long

However, the values are still not being calculated at this stage. Problem was in the comparison. So step 2 is to change Range("B" & r) to be

Range("B" & r).Value2

That got me as far as getting the startRow but endRow was coming up as 23 instead of 13. I tracked that one down to ElseIf pID(x). x is undefined and so is defaulting to 0. I believe that should be

ElseIf pID(currProduct)...

You can avoid problems like that by using the following line at the top of your code module

Option Explicit

That got values appearing on the sheet, but it takes a long time!
Instead of using Rows.Count why not stop at the last populated row in the sheet e.g.

VB

Dim lastRow As Long
lastRow = Me.UsedRange.Rows(Me.UsedRange.Rows.Count).Row

(Note - the Me assumes that this VBA code is on the Sheet that is being used - if you put this code into a basic module you will have to explicitly identify the sheet instead).

Also, instead of starting with r=1 each time, initialise

VB

endRow = 9

then always start that loop from endRow + 1. I ended up with something like this

VB

endRow = 9

'CALCULATE SALES FOR EACH PRODUCT
For currProduct = 1 To pID.Count
    '
    ' FIND START AND END ROW FOR currProduct
    '
    Dim lastRow As Long
    lastRow = Me.UsedRange.Rows(Me.UsedRange.Rows.Count).Row
    cStage = 0 'searching for the first row
    'calculate start and end row numbers for product
    For r = endRow + 1 To lastRow 'for each row
        If pID(currProduct) = Range("B" & r).Value2 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(currProduct) <> Range("B" & r).Value2 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

I didn't actually carry on looking into the next bit, but I suspect you can make it more efficient too. I will mention one last horror though

On Error Resume Next

Don't do it!

Maciej Los · Answer 2 · 2015-09-02T06:36:00

One general note: Use code in proper context.

For example, these two lines of code can return different results depending on what sheet is currently active.

VB

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

So, you have to replace it with:

VB

Dim rngPurchases As Range, rngPrices As Range
'...
Set rngPurchases = ThisWorkbook.Worksheets("Sheet1").Range("C" & startRow & ":" Range("C" & endRow).End(xlUp).Address)
Set rngPrices = ThisWorkbook.Worksheets("Sheet1").Range("D" & startRow & ":"  Range("D" & endRow).End(xlUp).Address)

I do recommend to return to basics[^].

Finally, strongly do believe that you can achieve that using array formula[^]. For further information, please see:
Excel 2010 Performance: Tips for Optimizing Performance Obstructions[^]

VBA Range/Array Problem. Cannot choose multiple ranges.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0