Storing a range in an array

[rahulkumar88]

Hi
I need to store the given range in a spreadsheet in an array. Can you please tell me why this code doesn't work and the solution to the problem as well.

Sub test()
Dim result() As Variant
For i = 1 To cells(1,1).end(xldown).row
For j = 1 To cells(1,1).end(xltoright).column
result(i, j) = Cells(i + 1, j + 1).Value
Next j
Next i
End Sub

Thanks

 

[pgc01]

Hi
I need to store the given range in a spreadsheet in an array. Can you please tell me why this code doesn't work and the solution to the problem

....
Dim result() As Variant
...

Hi rahulkumar88

You have declared the array without space and so you cannot write values in it. You'd have to allocate space to it.

In this case, however, you can write the values in the range directly.

Try:

Sub test()
Dim vResult As Variant
Dim lLastRow As Long
Dim lLastColumn As Long
 
lLastRow = Cells(1, 1).End(xlDown).Row
lLastColumn = Cells(1, 1).End(xlToRight).Column
vResult = Range(Cells(1, 1), Cells(lLastRow, lLastColumn)).Value
End Sub

 

[rahulkumar88]

Hi pgc01
Thanks for replying. Your code works perfectly. I need a little more help though. I'm having trouble understanding how arrays work. In the macro after I've stored the entire range in an array, I'm attempting to find the sum of all the values in each of the rows and then storing that sum in another array. I'm able to find the sum of the values in the each of the rows but I'm unable to store those values in another array.

Sub test()
Dim set1 As Variant
Dim set2 As Variant
Dim lLastRow As Long
Dim lLastColumn As Long
 
lLastRow = Cells(1, 1).End(xlDown).Row
lLastColumn = Cells(1, 1).End(xlToRight).Column
set1 = Range(Cells(1, 2), Cells(lLastRow, lLastColumn)).Value
set1 = Range(frstcell, lstcell)
For i = 1 To UBound(set1)
For j = 1 To 14 
' there are 14 data values in each row
rwsum = rwsum + set1(i, j)
Next j
set2(i) = rwsum
Next i
End Sub

I get a type mismatch at the line "set2(i)=rwsum".

 

[rahulkumar88]

I've forgotten to add the line to reset the rwsum value to 0 at the end of each instance. But that isn't the problem.

 

[pgc01]

OK. Try this:

Sub test()
Dim vResult As Variant
Dim lLastRow As Long
Dim lLastColumn As Long
Dim lRow As Long, lColumn As Long
Dim dSums() As Double, dSum As Double
 
lLastRow = Cells(1, 1).End(xlDown).Row
lLastColumn = Cells(1, 1).End(xlToRight).Column
vResult = Range(Cells(1, 1), Cells(lLastRow, lLastColumn)).Value
 
' allocate space for the results of the sums
ReDim dSums(LBound(vResult) To UBound(vResult))
 
For lRow = LBound(vResult) To UBound(vResult)
    dSum = 0
    For lColumn = LBound(vResult, 2) To UBound(vResult, 2)
        dSum = dSum + vResult(lRow, lColumn)
    Next lColumn
    dSums(lRow) = dSum
Next lRow
    
' the sums are in dSums()
' ...
End Sub

 

[Norie]

Hope you don't mind but I was having a play with using arrays, in particular using worksheet functions, eg Sum, in VBA.

This is what I came up with, probably not much use though.

Option Explicit
 
Sub test()
Dim rng As Range
Dim rw As Range
Dim lLastRow As Long
Dim lLastColumn As Long
Dim dSums() As Double, dSum As Double
Dim I As Long
 
    lLastRow = Cells(1, 1).End(xlDown).Row

    lLastColumn = Cells(1, 1).End(xlToRight).Column
    
    Set rng = Range(Cells(2, 1), Cells(lLastRow, lLastColumn))
 
    ReDim dSums(1 To rng.Rows.Count)
 
    For Each rw In rng.Rows
    
        I = I + 1
        
        dSum = Application.Sum(rw)
        
        dSums(I) = dSum
 
    Next rw
    
    rng.Offset(, rng.Columns.Count).Resize(UBound(dSums), 1) = Application.Transpose(dSums)
      
End Sub

PS Might have borrowed a line or two of code here and there for it.

 

[rahulkumar88]

@pgc01: Thanks! It works fine now. I had to use the redim statement for the sums array so that it's dimensions are the same.
@norie: Actually I'm glad you helped out. Now I know how to implement worksheet functions also. Thanks a lot!