Excel VBA - Dynamic Size of Arrays and Element Assignment

Question

How do I size arrays dynamically while trying to assign values to individual elements of the array from the sheet? In columns A and B I have

A   B
1   Houston
2   Miami
3   New York
4   Toronto
5   Los Angeles

I want the VBA to determine the number of elements and size the arrays based on how many elements are there. Then, the defined array gets the values from column B assigned to the elements. In the code below I am trying a For loop to get the values and assign them to each of the elements.

Here is the code I have:

Sub getNames()

Dim n As Integer 'denotes the number of elements
Dim i As Integer 'index
Dim Name() As String
Dim flag As Boolean

'Initialize values
i = 0
n = 0
flag = True

'For loop to determine number of elements
While flag = True
  'check if the current cell has data in it
   If Cells(i + 1, 1) <> "" Then
        i = i + 1
   Else
        flag = False
   End If
Wend

n = i


ReDim Name(n)

For i = 1 To n
Name(i) = cells(i,2).value
Next i

End Sub

However, I keep getting Syntax Error when trying to assign the value from the Cell.

Scott Craner · Accepted Answer · 2018-09-14 17:03:00Z

1

Declare Name as variant

Dim Name as Variant

Then fill it in 3 lines:

With ActiveSheet 'Should change to the sheet in question; WorkSheets("Sheet1")
     Name = .Range("B1", .Cells(.Cells(.Rows.Count,1).End(xlup).Row,2)).Value
End With

edited Sep 14, 2018 at 17:03

answered Sep 14, 2018 at 16:51

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

4 Comments

Scott Craner Over a year ago

@DisplayName my personal preference is to never allow vba to assume and as such even if ActiveSheet, I always specify.

fonsi Over a year ago

How do I pull the elements from name individually?

Scott Craner Over a year ago

Just like you would normally, you would refer to its location Name(1),Name(2),... @fonsi

Scott Craner Over a year ago

Sorry, it would be a 2D array so Name(1,1) and Name(2,1) and so on @fonsi

Collectives™ on Stack Overflow

Excel VBA - Dynamic Size of Arrays and Element Assignment

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related