Dim MyDict As Object, i As Long, MyVals As Variant
Set MyDict = CreateObject("Scripting.Dictionary")
MyVals = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value
For i = 1 To UBound(MyVals)
MyDict(MyVals(i, 1)) = MyVals(i, 2)
Next i
Hi Rory,There are numerous examples on the forum. Here's some sample code from one such post:
Rich (BB code):Dim MyDict As Object, i As Long, MyVals As Variant Set MyDict = CreateObject("Scripting.Dictionary") MyVals = Range("A2:B" & Cells(Rows.Count, "A").End(xlUp).Row).Value For i = 1 To UBound(MyVals) MyDict(MyVals(i, 1)) = MyVals(i, 2) Next i
I've assumed there would be no duplicate product names?
Hi Rick,@JohnExcel222 - What exactly are you wanting to do? If all you want is to store and existing range in memory, maybe do some calculations or manipulations to the data and then print it back out to a worksheet, then storing that data in a VBA array would be more efficient than using a Dictionary.
ah ok, I need to focus on arrays then@JohnExcel222 - What exactly are you wanting to do? If all you want is to store and existing range in memory, maybe do some calculations or manipulations to the data and then print it back out to a worksheet, then storing that data in a VBA array would be more efficient than using a Dictionary.
Loading an array is nearly instantaneous. Assuming a Variant variable named Arr and the range A2:M99ah ok, I need to focus on arrays then
Loading an array is nearly instantaneous. Assuming a Variant variable named Arr and the range A2:M99
Arr = Range("A2:M99").Value
You can then loop through the array by iterating a For..Next counter from 1 (arrays created this way always have a lower bound of 1) to UBound(Arr), do whatever manipulations you want and write the new values back to the original array, if appropriate, or to a new Variant variable ReDim'med to the size of the Arr array (remember, Arr is a two-dimensional array). You can then write the manipulated array (I'll use Arr for this example, but it could just as easily be the "new Variant variable" I mentioned a moment ago) back to whatever range you want (here, I start at Column P for example purposes, but you could write right over the original array if that is what is needed)...
Range("P2").Resize(UBound(Arr, 1), UBound(Arr, 2)) = Arr
Writing this array back is also nearly