Assigning values to array vba

Question

I don't have experience using arrays in VBA and I got lost. What I try to do is the following:

In the column A I have ~15 strings (number is not fixed sometimes it is more sometimes less)

I remove duplicates and then for each name in the column A I would like to create separate sheet in the file.

I created an array to which I tried to assign each name from A with this loop:

Sub assigningvalues()

Dim i As Integer
Dim myArray(20) As Variant
Dim finalrow As Long

ActiveSheet.Range("A1", Range("A1").End(xlDown)).RemoveDuplicates Columns:=Array(1)

finalrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlDown).Row

'For i = 2 To finalrow -> I get overflow error when I use this range   
For i = 2 To Cells(20, 1)  
    myArray(i) = Cells(i, 1).Value    
Next i

'I check with the lines below if values were assigned    
Cells(2, 4).Value = myArray(4)
Cells(3, 4).Value = myArray(2)

End Sub

Nevertheless values from the cells to do not assign to the array

Moreover when I try to use finalrow as range for the loop I get overflow error (It is not a big problem as there are workarounds, although it would be nice to know what I've done wrong)

Shai Rado · Accepted Answer · 2018-03-30 12:07:50Z

3

Try the code below:

Option Explicit

Sub assigningvalues()

Dim i As Long
Dim myArray(20) As Variant
Dim FinalRow As Long
Dim Sht As Worksheet

Set Sht = ThisWorkbook.Sheets("Sheet1") ' modify "Sheet1" to your sheet's name

With Sht
    .Range("A1", .Range("A1").End(xlDown)).RemoveDuplicates Columns:=Array(1)

    FinalRow = .Cells(.Rows.Count, "A").End(xlUp).Row ' get last row in column "A"

    For i = 2 To FinalRow
        myArray(i) = Cells(i, 1).Value
    Next i

    'I check with the lines below if values were assigned
    .Cells(2, 4).Value = myArray(4)
    .Cells(3, 4).Value = myArray(2)
End With

End Sub

Note: you can read the contents of the Range to a 1-D Array without a For loop, using Application.Transpose, you need to change the line you define it to:

Dim myArray As Variant

and populate the entire array using:

myArray = Application.Transpose(.Range("A2:A" & FinalRow))

edited Mar 30, 2018 at 12:07

answered Mar 30, 2018 at 12:02

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

2 Comments

Gary's Student Over a year ago

Nice tutorial content (+1)

Tomek Over a year ago

Thank you for the answer and the tip.I will use it in the future

FloLie · Accepted Answer · 2018-03-30 12:27:51Z

Try the code below:

    Sub assigningvalues()

    Dim myArray As Variant
    ActiveSheet.Range("A1", Range("A1").End(xlDown)).RemoveDuplicates Columns:=Array(1)

    myArray = ActiveSheet.Range("A1", Range("A1").End(xlDown))


    For Each element In myArray
        ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)).Name = element
    Next element

End Sub

NOTES: The problem with your above code was, that

ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlDown).Row

returned the absolut number of rows in the sheet, not the used ones. Since your array has length 20 and the sheet about 1 Mio. rows, you have an overflow. you can check this by using

Debug.Print ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlDown).Row

In the above code, after you remove dublicates, you again go down from A1 to the end and save the range to an array. The array myArray now contains all the cell values in your reduced range.

Now you loop over the elements with

For Each element in myArray

and create a new sheet with Workbook.Sheets.Add and assign the name my setting Sheets(index).name = element

The above code should work for you. Few remarks:

Instead of using "ActiveSheet", ThisWorkbook, etc. You should always start a Sub with this:

Dim wb As Workbook
Set wb = ThisWorkbook 'for the workbook containing the code
Set wb = Workbooks(workbookName) 'to reference an other Workbook

'And for all the sheets you are using
Dim ws As Worksheet
Set ws = wb.Sheets(sheetName) 'this way you avoid problems with multiple 
                               workbooks that are open and active or 
                               unactive sheets.

Collectives™ on Stack Overflow

Assigning values to array vba

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related