storing a range of value to an array

faye

New Member
Joined
Nov 28, 2005
Messages
6
I really need help on this issue. Is there's anyway i can store a range("A1:G1") to an array? if there's a way to do it, can you show me the codes?

Thanks a million
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi, welcome to the board!

Something like this perhaps:

Sub test()
Dim x
x = Range("A1:G1")
MsgBox x(2, 1)
End Sub

The array needs to then be referenced as the first number is row and the second number is column.
 
Upvote 0
Thanks for the super fast response!

i think i need to explain a little more in details. i am doing on a loop that will(i wish) pass in a range into an array and later use the range of values stored in the array to paste into another worksheet.

Something like this:

myArray(0) = Range("A1:G5") 'this will result in an error....

thanks again
 
Upvote 0
Hello and welcome to the board.

This solution should work:

Sub arrtornge()
Dim arrx(6)
Dim cell As Range
For Each cell In Range("A1:G1")
arrx(cell.Column - 1) = cell.Value
Next cell
Range("A2:G2") = arrx
End Sub

The last part is just to show that the array has been populated by writing it to Range B2:G2

Hope this is what you are looking for!!
[/code]
 
Upvote 0
thanks! i think i'm in the right track. Fausto, your codes worked great. However, if i need to store multiple range in the array, then what must i do??? How can i reference to the different ranges stored in the Array? Sorry for asking so many questions.....
 
Upvote 0
Hello Faye

I'm not entirely sure what you are trying to do. My understanding is that you want to have multiple ranges. Why not try to use different arrays for different ranges. If you assign many ranges to the same array, things could get a little hairy!!

I suggest you use a number of arrays to reference different ranges, it will make it a lot easier for you and anyone who uses your code afterwards!!

To reference to the different items in the array is relatively easy. The array in my example is made up of 7 elements (0 to 6). If you wanted to see what the value of the third item in the array is, arrx(2) would be the place to look. Effectively if you add:

msgbox arrx(2) after Next cell in my previous example, the third item of the array will pop up in the message box.

I hope this is what you were asking for, if not please post again, and be more specific about your needs.
 
Upvote 0
Sorry for the confusion that i've cause :(

i am actually doing a loop that will return several ranges of values. And i need an array to store these various set of ranges.

The codes below will better explained what i've said

Code:
Dim i As integer
Dim rng As Range 
Dim partsRng
Dim cell As Object
Dim cellAdd As String
Dim c As String 
Dim h As Integer
h = 0

ReDim partsArray(3)
partsArray(0) = "C80001"
partsArray(1) = "C80010"
partsArray(2) = "C80100"

ReDim array1(5)
ReDim array2(5)
ReDim array3(5)


For i = 0 to 2
set partsRng = Range("A1:A100")
For each cell in partsRng
if cell.Value  = partsArray(i) then
c = cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
cellAdd = Mid(c, 1, 1)
Set rng = Range("A" & cellAdd & ":" &  "G" & cellAdd)

If i = 0 then
array1(h) = rng
Elseif i = 1 then
array2(h) = rng
Else
array3(h) = rng
End if

h = h + 1
Next
i = i + 1
Next
 
Upvote 0
Hello Faye

I have edited your example slightly... Please see if this serves your purposes better.

Code:
Sub test()
Dim h As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim rng As Range
Dim partsRng As Range
Dim cell As Range
Dim topmost As Integer
Dim array1()
Dim array2()
Dim array3()
Set partsRng = Range("A1:A100")
topmost = Application.WorksheetFunction.CountIf(partsRng, "=C80001")
If topmost < Application.WorksheetFunction.CountIf(partsRng, "=C80010") Then topmost = Application.WorksheetFunction.CountIf(partsRng, "=C80010")
If topmost < Application.WorksheetFunction.CountIf(partsRng, "=C80100") Then topmost = Application.WorksheetFunction.CountIf(partsRng, "=C80100")
ReDim array1(topmost)
ReDim array2(topmost)
ReDim array3(topmost)
For Each cell In partsRng
If cell.Value = "C80001" Or cell.Value = "C80010" Or cell.Value = "C80100" Then
    Set rng = Range("A" & cell.Row & ":" & "G" & cell.Row)
    If cell.Value = "C80001" Then
            array1(h) = rng
            h = h + 1
        ElseIf cell.Value = "C80010" Then
            array2(i) = rng
            i = i + 1
        ElseIf cell.Value = "C80100" Then
            array3(j) = rng
            j = j + 1
    End If
End If
Next cell
For k = 0 To topmost
    Sheets("Sheet2").Range("A1:G1").Offset(k, 0) = array1(k)
    Sheets("Sheet2").Range("H1:N1").Offset(k, 0) = array2(k)
    Sheets("Sheet2").Range("O1:U1").Offset(k, 0) = array3(k)
Next

I hope this helps!
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top