Excel – How to transform Range Values to Array

--> (Word)	--> (PDF)	--> (Epub)
This article has been published [fromdate]

SCENARIO

Let's say that the first column of data is Range("A1:A5") how do I get an array to be filled with the ranges values. Array() = Range("A1:A5") this doesn't seem right.

SOLUTION

From memory you need to use a Variant for this sort of thing. Something like this:

Sub HipHipArray() 
    Dim vArr As Variant 
    Dim l As Long, m As Long 
     
    vArr = Range("A1:A5").Value 
     'pass range values to array
     
    For l = LBound(vArr, 1) To UBound(vArr, 1) 
        For m = LBound(vArr, 2) To UBound(vArr, 2) 
            If vArr(l, m) = 321 Then vArr(l, m) = 123 
        Next m 
    Next l 
     'do stuff with array
     
    Range("B1:B5").Value = vArr 
     'pass array values To appropriately sized range
     '(THIS IS ONLY TO BE SURE THAT THE ARRAY HAS BEEN CORRECTLY POPULATED)
     
End Sub 

SOURCE	LINK (Ozgrid.com)	LANGUAGE	ENGLISH