I need to create an array from a list of data in excel. The data will be of different lengths so i will have to find the end of it then create the array. I then need to loop through the array and use each value. I have not used VBA before so anything is helpful. This is what i have been able to get so far:
Sub Calc()
Dim endCell As String
endCell = Range("B13").End(xlDown).Row
endCell = "B13:B" + endCell
Dim nums As Variant
nums = Range(endCell)
End Sub
You won't have to do that. Just do this:
Dim varValues() as Variant
' ...
varValues = Range(endCell).Value
For a range object that comprises more than one cell, the value property will return an array of values.
If you don't know whether the range has more than one cell, but you want to have an array of values regardless, you could write a function to achieve that (thanks to brettdj for the inspiration):
Function GetValue(rng As Range) As Variant()
Dim varResult() As Variant
If rng.Cells.Count = 1 Then
ReDim varResult(0 To 0) 'I prefer to be explicit about the base
varResult(0) = rng.Value
Else
varResult = rng.Value
End If
GetValue = varResult
End Function
varValues = Range(endCell).Value. Esle an error results.use the Transpose function:
nums = Application.WorksheetFunction.Transpose(Range(endCell).value)
nums = Range("A1:B5") will return a 2D array starting from nums(1,1) (=A1) to nums(5,2) (=B5)...
a = Range("A1:B5") returns a Variant/Variant(1 to 5, 1 to 2) and b = Application.WorksheetFunction.Transpose(Range("A1:B5").Value) returns a Variant/Variant(1 to 2, 1 to 5), which is the same as a, but transposed. More or less what you would expect but it does not change the number of dimensions.a = range method always returns a 2D array. Nice tip.Starting from your code, you could loop over the array in the following way:
Sub Calc()
Dim endCell As String
endCell = Range("B13").End(xlDown).Row
endCell = "B13:B" & endCell
Dim nums As Variant
Dim i As Long
nums = Range(endCell)
For i = LBound(nums,1) To UBound(nums,1)
'do something with nums(i,1)
Next i
End Sub
I am not sure if this is any better or cleaner.
Sub Calc()
Dim endCell As String
endCell = Range("B13").End(xlDown).Row
endCell = "B13:B" & endCell
Range(endCell).select
For each c in selection
use any attribute of the cells one at a time like, c.value c.formula.....
Next c
End Sub
