Hello,
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
Display More
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!