Populating VBA Arrays with Sumproduct (Or better alternative)

[Scott Cooper]

Hi All,

I've been experimenting with VBA Arrays as both a learning exercise and to speed up some of my reporting which due to the volume of data and complexity of formula can take a very long time to update.

I am however a little stuck and would appreciate some help and pointers. In the attached example workbook the range A:AH in sheet1 would form Array1 and the range AJ:AN would form a second array.

What I am looking to do is populate the second array with countifs of the first array (without doing what i'm currently doing which is write the array back to a worksheet and then use a formula.) I've put the an example of the worksheetfunction in range AK that I am trying to replicate within the VBA Array.

I Hope this makes sense and look forward to your help

 

[NARAYANK991]

Hi ,

Something like this ?

Public Sub Calculate_Type1()
           Dim lastrow As Long
           Dim currval As String
        
           lastrow = Me.Range("AJ2").End(xlDown).Row
        
           For i = 0 To lastrow - 2
               currval = Me.Range("AJ2").Offset(i).Value
               Me.Range("AJ2").Offset(i, 1).Formula = "=COUNTIFS($B:$B," & """" & currval & """" & ",$F:$F,""RET - RET"",J:J,""P"")"
           Next
End Sub

Narayan

 

[Scott Cooper]

Hi NARAYANK991, thank you for looking into this, forgive me if i'm wrong but this in essence is putting the formula into the worksheet and copying it down to the desired row length?

if so that's what i'm currently doing (via a different method) but its taking a long time as there are 46K rows in approximately 26 different data sets that are being examined in this way - I f I can do this in somefashion within the VBA array i'm hoping it'll be much much faster than the ~20 minutes it currently takes.

 

[NARAYANK991]

Hi ,

There is no reason why calculations should take 20 minutes , unless your formulae extend over many thousands of rows ; this is not what I could understand from your 'sample' workbook.

If you can upload a more realistic workbook , which has all the features of your actual workbook , except that instead of 46K rows , it can have a lesser number of rows , say 10000 , then testing can be done at a practical level ; testing any approach on 400 rows is not going to reveal anything.

Narayan

 

[jeffreyweir]

without doing what i'm currently doing which is write the array back to a worksheet and then use a formula

Oftentimes this is actually the best approach. Sometimes it's much more efficient to have Excel perform these via it's highly optimised worksheet functions than hand-rolled VBA.