Populating a multi dimensional array

[Guest]

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.

 

[RB Smissaert]

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

 

[gimme_this_gimme_that]

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

 

[Alan Beban]

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

 

[Guest]

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