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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 28, 2005
Messages
6
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Take the (0) out and dim the variable as Variant.
 

Fausto

New Member
Joined
Jul 28, 2004
Messages
44

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

New Member
Joined
Nov 28, 2005
Messages
6
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

New Member
Joined
Jul 28, 2004
Messages
44

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

New Member
Joined
Nov 28, 2005
Messages
6
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
 

Fausto

New Member
Joined
Jul 28, 2004
Messages
44
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!
 

faye

New Member
Joined
Nov 28, 2005
Messages
6
:biggrin: , that's what i want!!! It worked!
Many thanks for the attention to my problem
 

Watch MrExcel Video

Forum statistics

Threads
1,118,381
Messages
5,571,804
Members
412,420
Latest member
Quintankerus
Top