Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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
share|improve this question
1  
to concatenate strings use '&' instead of '+'. So endCell = "B13:B" & endCell –  Francis P Mar 26 '12 at 17:26
add comment

4 Answers

up vote 1 down vote accepted

use the Transpose function:

nums = Application.WorksheetFunction.Transpose(Range(endCell).value)
share|improve this answer
2  
Not sure how that relates to the question... –  assylias Mar 26 '12 at 17:26
 
If not using Transpose, the result will be a Variant of Variants... –  Francis P Mar 26 '12 at 17:29
 
nums = Range("A1:B5") will return a 2D array starting from nums(1,1) (=A1) to nums(5,2) (=B5)... –  assylias Mar 26 '12 at 17:31
 
Not using Transpose will result in a 3D Array (which is not usefull when working with a single column). I recommend the Transpose method. –  Francis P Mar 26 '12 at 17:37
 
uh... no. 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. –  assylias Mar 26 '12 at 17:41
show 3 more comments
up vote 4 down vote

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
share|improve this answer
 
+1 but worth testing is endCell is more than 1 cell before varValues = Range(endCell).Value. Esle an error results. –  brettdj Mar 27 '12 at 2:40
 
@brettdj thanks for pointing that out; I've edited the answer. –  phoog Mar 27 '12 at 13:52
add comment
up vote 0 down vote

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
share|improve this answer
add comment
up vote 0 down vote

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.