I'm new to VBA and I'm confused with arrays, other than they calculate at huge speed, which is what I want.
This sample code works perfectly, but i want to do the next step which is rather than put values into worksheet cells, i want to add them to a temporary array then perform counts, averages, medians etc then clear the array. I have 29 variables, and 4 quarters & yearly data that I am processing.
The output is a report, nicely formatted for end-users. I realise I might not be using vba and arrays to their full potential, but things are working well. I've searched forums, but I can't find a response that I can understand
Dim arr As Variant arr = wsData.Range("A1").CurrentRegion.Value '91 columns, rows will vary Dim Y as Integer Y=2021 Dim i As Long, RowQ1 As Long, RowQ2 As Long, RowQ3 As Long, RowQ4 As Long, RowY As Long RowQ1 = 1 RowQ2 = 1 RowQ3 = 1 RowQ4 = 1 RowY = 1 '***Extracting all data, Quarter1 For i = LBound(arr) To UBound(arr) If arr(i, 7) = Y And arr(i, 6) = 1 Then wsTMP.Range("A2").Offset(RowQ1) = arr(i, 10) 'Age Q1 wsTMP.Range("B2").Offset(RowQ1) = arr(i, 'LOS Q2 wsTMP.Range("C2").Offset(RowQ1) = arr(i, 62) 'FCC Q20 wsTMP.Range("D2").Offset(RowQ1) = arr(i, 66) 'TCC Q21 wsTMP.Range("E2").Offset(RowQ1) = arr(i, 70) 'EoLCP Q22 RowQ1 = RowQ1 + 1 End If Next i
Repeats for 3 Q and whole year. I perform calculations and put them in the report, and there is 'clear' function at the start
I basically need help to construct code to replace wsTMP.Range ("A2").Offset (RowQ1) to store in an array (1 dim), on which I can perform calcs, then clear it.
Basic? Yep but I just can't get my head around single column arrays.
Thanks in anticipation!