2

This works to put the values in the column: 

Sub JR_ArrayToDebugPint2()  
     ' written by Jack in the UK for [url]www.OzGrid.com[/url]  
     ' our web site [url]www.excel-it.com[/url]  
     ' Excel Xp+ 14th Aug 2004  
     ' [url]http://www.ozgrid.com/forum/showthread.php?t=38111[/url]  
    Dim JR_Values(500)  
    Dim JR_Count As Integer  
    Dim R As Long  
    R = 2  
    For JR_Count = 1 To 500 Step 1  
        JR_Values(JR_Count) = Evaluate("=INDEX('Client'!$O$2:$O$347473,MATCH(1,(('Client_Cost'!$D$2:$D$347473=BC" & CStr(R) & ")*('Client_Cost'!$E$2:$E$347473=BE" & CStr(R) & ")),0))")  
        Sheet1.Range("BG" & CStr(R) & "").Value = JR_Values(JR_Count)  
        R = R + 1  
        'Debug.Print JR_Values(JR_Count)  
        Next JR_Count  
End Sub

I've modified the original code I found on mrexcel.com

I get the correct list of values whether I Debug.Print or print to the worksheet. So in my mind, I ought to be able to put the values in an array as they are calculated, then use Range("BG2:BG500").Value = Application.Transpose(myarray).

I am assuming if I do this the values will be placed in the cells in the column all at once, rather than one at a time, which is what this code, and all others I've tried, is doing. I am also assuming that, if the values are placed in the cells in the column all at once, it is MUCH faster than placing the values in the cells one at a time.

What I'm not able to do is get the code to put the value in an array once the formula is evaluated. I've tried variations of the following with no success - statements to set the array and have the array take the value of the calculation are in caps and marked by ==>. The most common error I get is type mismatch.

Sub JR_ArrayToDebugPint2()  
    Dim JR_Values(500)  
    Dim JR_Count As Integer  
    Dim R As Long  
 ==>   DIM arrPRICE(0 TO 500) AS VARIANT  
    R = 2  
    For JR_Count = 1 To 500 Step 1  
        JR_Values(JR_Count) = Evaluate("=INDEX('Client'!$O$2:$O$347473,MATCH(1,(('Client_Cost'!$D$2:$D$347473=BC" & CStr(R) & ")*('Client_Cost'!$E$2:$E$347473=BE" & CStr(R) & ")),0))")  
     ==>   arrPRICE(R) = JR_VALUES(JR_COUNT)  
        R = R + 1  
        'Debug.Print JR_Values(JR_Count)  
    Next JR_Count  
End Sub
Improve this question
6
  • What worksheet are the values from column BC and BE coming from? (e.g. BE" & CStr(R)) Commented Dec 6, 2015 at 20:37
  • Why change worksheet reference methods midstream to the codename Sheet1? Is Sheet1 either 'Client' or 'Client_Cost'? Commented Dec 6, 2015 at 20:42
  • Jeeped, you are correct. All three worksheets are the same. Let's call all of them Client. Commented Dec 6, 2015 at 23:05
  • Let me correct that. The variables - BC and BE - are on the Client Worksheet. This is the same worksheet where the formula / results will go. The columns with the ranges with absolute references are on the Client Cost worksheet. Commented Dec 6, 2015 at 23:06
  • arrPRICE(JR_Count) = JR_Values(JR_Count) for a start. Also your arrays are missing their first entry, since they start from 0. So dim them from 1 to 499. What is the error you get with the code then? Commented Dec 7, 2015 at 1:44

1 Answer 1

Reset to default
0

When you dimension the variant array like Dim JR_Values(500) you are creating a one-dimensioned array based upon a zero-based index. This first element within the array is JR_Values(0) and the last is JR_Values(500) for a total of 501 array elements. While you could work from 0 to 499 with a little math, you can also force a one-based index on the variant array by declaring it that way.

The assumed worksheet parentage of the BC and BE columns where the individual row data criteria comes from is not definitive when using Application Evaluate like it is when the same formula is used on a worksheet. A worksheet knows who it is; VBA may or may not know what worksheet you are implying.

Sub JR_ArrayToDebugPint2()
    Dim olr As Long, rws As Long, JR_Count As Long, JR_Values As Variant

    'get some dimensions to the various data ranges
    With Worksheets("Client_Cost")
        'only use as many rows as absolutely necessary
        olr = Application.Min(.Cells(Rows.Count, "C").End(xlUp).Row, _
                              .Cells(Rows.Count, "E").End(xlUp).Row)
    End With
    With Worksheets("Client")
        rws = Application.Min(.Cells(Rows.Count, "BC").End(xlUp).Row, _
                              .Cells(Rows.Count, "BE").End(xlUp).Row)
        'override the above statement unless you want to run this overnight
        rws = 500
    End With

    ReDim JR_Values(1 To rws)   'force a one-based index on the array
    'Debug.Print LBound(JR_Values) & ":" & UBound(JR_Values)

    For JR_Count = LBound(JR_Values) To UBound(JR_Values) Step 1
        'Debug.Print Evaluate("INDEX('Client'!O2:O" & olr & _
                 ", MATCH(1, (('Client_Cost'!D2:D" & olr & "='Client'!BC" & JR_Count+1 & ")" & _
                            "*('Client_Cost'!E2:E" & olr & "='Client'!BE" & JR_Count+1 & ")), 0))")

        'R would be equal to JR_Count + 1 if R was still used (starts as R = 2)
        JR_Values(JR_Count) = _
          Evaluate("INDEX('Client'!O2:O" & olr & _
                 ", MATCH(1, (('Client_Cost'!D2:D" & olr & "='Client'!BC" & JR_Count + 1 & ")" & _
                            "*('Client_Cost'!E2:E" & olr & "='Client'!BE" & JR_Count + 1 & ")), 0))")
        'Debug.Print JR_Values(JR_Count)
    Next JR_Count

    With Worksheets("Client")
        .Range("BG2").Resize(UBound(JR_Values), 1) = Application.Transpose(JR_Values)
    End With

End Sub

I've left a lot of comments for you to review and subsequently clean up. I recently wrote a narrative of declaring one-dimension and two-dimension variant arrays in How to assign the variable length of an array to integer variable.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you very much for that, Jeeped. I see that your code eliminates any hardcoding I had in place. Of course, it always looks simpler when I see it than it does when I'm trying to figure it out - I knew that the basic formula for putting a value in an array is array([number]) = value, but for some reason I couldn't get that syntax correct. Am I correct to assume that your code will fill the column faster than what I had because all calculations are done in memory? My code took 90 seconds to fill 500 rows. Thank you again.
The array formula being Evaluated is till an array formula that requires cyclic calcualtion. >300K rows is going to take a lot of time. I'll experiment with some random data and full in-memory calculation to see if I can cut it down scooch. One major hurdle (not previously discussed) is that any single change to Client_Cost's D:D, E:E or Client's O:O requires recalculation of all results.
OK. I've tried the code and it seems to work. I just did a test run and got some errors, but that's OK - they are N/A errors and I don't have all related columns filled. That said, though, when I run for longer rows, it still takes time to run. So at this point I have your code that is faster than what I was doing manually and is better written, but the time saved is not as much as I'd have liked, not due to lack of trying on your part. I have calculation set to manual so I don't know if your last statement is a factor.
Thank you for all your efforts.
@BruceKaufmann - I used a Scripting.Dictionary and got the whole process (350K rows) down to 45 seconds. Don't know if you want to post here in a new question or over at Code Review.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.