Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a multi dimensional array
Hi,
I can't quite piece together the answer I need from the existing threads; how do I loop through a worksheet, picking up data from cells as I go a long? and add them to my array. I have the basic code I need to cycle through the worksheet, but can't work out how to keep adding data to the array (I can do it on the first instance). The array will end up being a 3 column x 122 row array; the third column data is static i.e. it will always have a fixed value, but the first and second items are scraped from a worksheet. The values are text not numbers. Many thanks for your responses. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a multi dimensional array
You will need something along these lines:
dim r as long dim c as long dim arr(1 to 122, 1 to 3) as string for r = 1 to 122 for c = 1 to 2 arr(r, c) = cells(r, c).text next c arr(r, 3) = "constant" next r RBS "Wendy" wrote in message ... Hi, I can't quite piece together the answer I need from the existing threads; how do I loop through a worksheet, picking up data from cells as I go a long? and add them to my array. I have the basic code I need to cycle through the worksheet, but can't work out how to keep adding data to the array (I can do it on the first instance). The array will end up being a 3 column x 122 row array; the third column data is static i.e. it will always have a fixed value, but the first and second items are scraped from a worksheet. The values are text not numbers. Many thanks for your responses. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a multi dimensional array
This might help. Note the sweet ending which allows you to insert the
array into another worksheet in a single VBA statement. Sub move() Dim arr() As Variant Dim i As Long Dim b As Workbook Dim s1 As Worksheet Dim s2 As Worksheet Set b = ThisWorkbook Set s1 = b.Sheets("Sheet1") Set s2 = b.Sheets("Sheet2") For i = 1 To 122 ReDim Preserve arr(1 To i) arr(i) = Array(s1.Cells(i, 1), s1.Cells(i, 2), s1.Cells(i, 3)) Next s2.Range("A1:C122").Value = Application.Transpose(Application.Transpose(arr)) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a multi dimensional array
Wendy wrote:
Hi, I can't quite piece together the answer I need from the existing threads; how do I loop through a worksheet, picking up data from cells as I go a long? and add them to my array. I have the basic code I need to cycle through the worksheet, but can't work out how to keep adding data to the array (I can do it on the first instance). The array will end up being a 3 column x 122 row array; the third column data is static i.e. it will always have a fixed value, but the first and second items are scraped from a worksheet. The values are text not numbers. Many thanks for your responses. Efficient code depends on from where on the worksheet the first and second items are "scraped." Alan Beban |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating a multi dimensional array
Hi,
Thanks for responses. I'm still a bit stuck. I've changed my array to a 122x2 array for ease, but I'm still having problems with passing values into the array. I get a type mismatch error, so I guess I haven't declared my properties correctly but just can't work it out. The data is being collected from a sheet, that has data randomly entered across 18 columns, I need to pick up the value from Column A, row x, check columns 1-18 for value set to 1, then lookup the column heading and take that value, with both these values I need to populate the array, then move on to the next row and repeat. Thanks Wendy ============== Option Base 1 Sub Enrol_Scrape() Dim x As Integer Dim col As Integer Dim row As Integer Dim rng As Range Dim PCode, Subject As Variant Dim MyArray() As Variant Numrows = Range("CStudent", Range("CStudent").End(xlDown)).Rows.Count Range("A4").Select For x = 0 To Numrows ActiveCell.Offset(x, 0).Activate PCode = ActiveCell.Value For col = 0 To 18 If ActiveCell.Offset(x, col).Value = 1 Then MsgBox (ActiveCell.Offset(x, col).Address) row = ActiveCell.row - 2 Subject = ActiveCell.Offset(-row, col).Text End If Next col For i = 1 To 122 ReDim Preserve MyArray(i, 2) x = x + 1 MyArray(i) = Array(PCode(i, 1), Subject(i, 2)) Next i Next x ' move to next row 'Sheets("PASS").Activate 'Range("A2:C2").Activate 'Range("A2:C2").Value = MyArray End Sub ================================== " wrote: This might help. Note the sweet ending which allows you to insert the array into another worksheet in a single VBA statement. Sub move() Dim arr() As Variant Dim i As Long Dim b As Workbook Dim s1 As Worksheet Dim s2 As Worksheet Set b = ThisWorkbook Set s1 = b.Sheets("Sheet1") Set s2 = b.Sheets("Sheet2") For i = 1 To 122 ReDim Preserve arr(1 To i) arr(i) = Array(s1.Cells(i, 1), s1.Cells(i, 2), s1.Cells(i, 3)) Next s2.Range("A1:C122").Value = Application.Transpose(Application.Transpose(arr)) End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multi Dimensional Array | Excel Programming | |||
Multi Dimensional Array | Excel Programming | |||
Populating Two Dimensional Array | Excel Programming | |||
Multi-Dimensional Array Let & Get | Excel Programming | |||
Viewing Multi dimensional array | Excel Programming |