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
- 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!