Capture a group of cells as an array
Hello,
I have a worksheet with data presented in repeating clusters which I would like to rearrange into a single row. There are 5 items spread across 2 colums and 5 rows. Would it make sense to assign the cell values to an array, then print that array to a single row and repeat for as many clusters? Code is below: Thanks in advance! Sub FormatRatings() Dim RateData As Variant Dim AsOf As Date Dim a As Long, b As Long, c As Long, d As Long, e As Long, x As Long 'a through e are cell row references 'x = Load Sheet row 'Starting reference a = 12 b = 13 c = 14 d = 15 c = 16 x = 2 For i = 1 To 30 Step 1 'Gather Data Sheets(2).Select RateData = Array(AsOf, Cells(a, 1).Value, Cells(b, 2).Value, Cells(c, 2).Value, Cells(d, 2).Value, Cells(e, 2).Value) 'Print Data Sheets(1).Select Range(Cells(x, 1), Cells(x, 6)).Value = RateData 'Increment reference locations a = a + 5 b = b + 5 c = c + 5 d = d + 5 e = e + 5 x = x + 1 Next i End Sub |
Capture a group of cells as an array
Anything which reduces I/O helps.
But if the whole procedure takes seconds then trying to optimize may not be worth the effort... One can also argue that if speed is critical then VBA is not a good choice... :-) Declare the Array in the beginning and not create one like you do with each iteration. Assign each element in a loop... "Bythsx-Addagio" wrote: Hello, I have a worksheet with data presented in repeating clusters which I would like to rearrange into a single row. There are 5 items spread across 2 colums and 5 rows. Would it make sense to assign the cell values to an array, then print that array to a single row and repeat for as many clusters? Code is below: Thanks in advance! Sub FormatRatings() Dim RateData As Variant Dim AsOf As Date Dim a As Long, b As Long, c As Long, d As Long, e As Long, x As Long 'a through e are cell row references 'x = Load Sheet row 'Starting reference a = 12 b = 13 c = 14 d = 15 c = 16 x = 2 For i = 1 To 30 Step 1 'Gather Data Sheets(2).Select RateData = Array(AsOf, Cells(a, 1).Value, Cells(b, 2).Value, Cells(c, 2).Value, Cells(d, 2).Value, Cells(e, 2).Value) 'Print Data Sheets(1).Select Range(Cells(x, 1), Cells(x, 6)).Value = RateData 'Increment reference locations a = a + 5 b = b + 5 c = c + 5 d = d + 5 e = e + 5 x = x + 1 Next i End Sub |
All times are GMT +1. The time now is 03:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com