storing a range of value to an array

[faye]

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

 

[Scott Huish]

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.

 

[faye]

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

 

[Scott Huish]

Take the (0) out and dim the variable as Variant.

 

[Fausto]

ADVERTISEMENT

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]

 

[faye]

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.....

 

[Fausto]

ADVERTISEMENT

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.

 

[faye]

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

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

 

[Fausto]

Hello Faye

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

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!

 

[faye]

, that's what i want!!! It worked!
Many thanks for the attention to my problem