Using VBA to assign range of cell values to array of variables

Question

I'm very new to VBA, to bear with me here.

I want to assign a set of variables the value of a set of ranges ie. run a brief code to simplify the following

Dim Sample 1 as string
Sample1 = activeworksheet.range("C17").value

Dim Sample 2 as string
Sample2 = activeworksheet.range("C18").value}

and so on

Following an excelfunctions.net tutorial, I know that I can shorten the declaration to

Dim Sample(1 to 20) as a string

But the tutorial drops it there(because it's a tutorial about names), suggesting I populate it as follows

sample(1)=activesheet.range("C7").value
sample(2)=activesheet.range("C7").value

and so on

I found the discussion below to be on the right track to answer my quest, but I am having trouble applying it to my situation. (Excel VBA Array Ranges for a loop)

As a follow up note, I am ultimately trying to assign values to these variables for use in the following procedures, rather than declaring and assigning them each time.

Thanks!

Maxime Porté · Accepted Answer · 2015-07-29 17:12:32Z

7

You should :

Define the range you want to retrieve data

For each cell of the range, retrieve your datas

dim tab() As string, cell as range, i as integer
i = 0
redim tab(0)
for each cell in ActiveWorksheet.Range("C1:C20")
    tab(i) = cell
    i = i + 1
    redim preserve tab(i)
next

edit : I indent the code to display it correctly

answered Jul 29, 2015 at 17:12

Maxime Porté

1,0948 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SydneyJ Over a year ago

All sets of code worked, the code that Maxime suggested had the added bonus of preserving that output for later use. Thanks.

BruceWayne · Accepted Answer · 2015-07-29 17:15:03Z

7

Try something like this:

Sub test()
Dim sampleArr(1 To 20) As String
Dim i As Integer
Dim rng As Range, cel As Range

i = 1
Set rng = Range("C1:C20") 

For Each cel In rng
    sampleArr(i) = cel.Value
    i = i + 1
Next cel
For i = LBound(sampleArr) To UBound(sampleArr)
    Debug.Print sampleArr(i)
Next i

Also, if you know the range you want to put into an array, you can simply set an array to that range:

Sub test()
Dim sampleArr() As Variant
Dim i As Integer
Dim rng As Range, cel As Range

i = 1
Set rng = Range("C1:C20") ' Note, this creates a 2 Dimensional array

sampleArr = rng ' Right here, this sets the values in the range to this array.

For i = LBound(sampleArr) To UBound(sampleArr)
    Debug.Print sampleArr(i, 1) ' you need the ",1" since this is 2D.
Next i

End Sub

answered Jul 29, 2015 at 17:15

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

1 Comment

Jaco-Ben Vosloo Over a year ago

The second part of the answer is the easier method, which I assume is what the OP was expecting Dim Arr() As Variant ' declare an unallocated array. Arr = Range("A1:C5")' Arr is now an allocated array

Mohammed Almudhafar · Accepted Answer · 2018-08-14 11:51:04Z

1

Additional way to the above you can only use:

Arr = ActiveWorksheet.Range("C1:C20").Value

Then you can directly use:

Arr(i,1) where i is C1 to C20 range!

answered Aug 14, 2018 at 11:51

Mohammed Almudhafar

676 bronze badges

1 Comment

GTaylor Over a year ago

That causes error 13; type mismatch.

Collectives™ on Stack Overflow

Using VBA to assign range of cell values to array of variables

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related