Inputting a column of data in excel to an array

[brellis1]

I have a table of data. I need to input this data into an array, manipulate this data. Then output this data to a spreadsheet. My problem is knowing how the best way to accomplish this currently I am doing this to input a column of data

Function GetData()

'Initializes variables
Number_Of_Entrees = 0
Mover = 0

'This loop loops through until there is no information provided
Do Until IsEmpty(Worksheets("force input&quot.Cells(Number_Of_Entrees + 2, 2).Value)
Number_Of_Entrees = Number_Of_Entrees + 1
Loop

'Resizes the array
ReDim Acceleration_Of_Projectile(0 To (Number_Of_Entrees - 1))


'The following loop is used to input all the accelerations, sets the values in the arrray to the acceleration
Do Until IsEmpty(Worksheets("force input&quot.Cells(Mover + 2, 2).Value)
Acceleration_Of_Projectile(Mover) = Worksheets("force input&quot.Cells(Mover + 2, 2).Value
Mover = Mover + 1
Loop

GetData = Number_Of_Entrees 'Returns the number of data points

End Function

I was hoping there was an easier way to input all the data without two for loops for this takes lots time for a large spreadsheet

 

[ssVBAdev]

What about something like this (I did this quick from memory and have not tested it so you may need to trouble shoot)...


Function GetData()

'Presuming there will be at least 1 data entry...
Dim Mover as Integer
Mover = 1
Dim Acceleration_Of_Projectile()
Redim Acceleration_Of_Projectile(Mover)

Do
If Mover > UBound(Acceleration_Of_Projectile) Then
ReDim Preserve Acceleration_Of_Projectile(UBound(Acceleration_Of_Projectile) + 1)
End If

Acceleration_Of_Projectile(Mover) = Worksheets("force input&quot.Cells(Mover + 2, 2).Value

Mover = Mover + 1

If Worksheets("force input&quot.Cells(Mover, 2).Value) = "" then Exit Do
Loop

GetData = UBound(Acceleration_Of_Projectile) 'Returns the number of data points

End Function

 

[xlbo]

Dunno exactly what you are trying to accomplish but the easiest and fastest way to assign a range of data to an array is:

Dim mArr As Variant, lRow As Long
lRow = Sheets("Force Input&quot.Range("A65536&quot.End(xlUp).Row
mArr = Range("A1:A" & lRow)

and back out again
lRow = ubound(mArr)
range("B1:B" & lRow) = mArr

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes