Adding String in an Array with a For Each loop

Question

I'm trying to create a Drop Down list in Excel with the help of an Array. Unfortunately, I have some problems with my code (I won't show all my code I'm afraid it is too long).

Here is the part of the code for adding what I want in the Array :

Dim Range_Protection As Range
Dim Row_Range As Range
Dim Tableau As Range

Dim Protection_First_Value As String
Dim Protection_Last_Value As String
Dim Array_List() As String

Dim Taille_Array As Integer

If Not Range_Protection Is Nothing Then

'The value I want to get are String, don't know if I should use "Cells.Text" instead
Protection_First_Value = Tableau.Cells(1, 1).Value

    For Each Row_Range In Range_Protection.Rows

        Protection_Last_Value = Row_Range.Cells(1, 1).Value

        'I'm checking the value of each rows
        'Everytime there is a new value, I add it to the Array
        If Protection_First_Value <> Protection_Last_Value Then

            Protection_First_Value = Protection_Last_Value

            'Taille_Array is already determined earlier in the code
            For Count = 0 To Taille_Array

                Array_List(Taille_Array) = Protection_Last_Value

            Next Count
        Else

        End If

    Next Row_Range

End If

And the code for creating the Drop Down list :

With Range("ListeD_Protection").Validation
    .Add Type:=xlValidateList, Formula1:=Join(Array_List, ",")
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True

End With

I always have the same error, no matter what I try to do at the same line :

    .Add Type:=xlValidateList, Formula1:=Join(Array_List, ",")

Here is the message :

'1004': Application-defined or object-defined error

I did some researches on the internet but could find nothing about a problem similar as mine. After hours of thinking, I'm stuck and can't see what's wrong even though it's certainly just a small error somewhere in my code.

Can someone tell me if you can understand what is the problem, I would be really thankful.

user3598756 · Accepted Answer · 2016-10-03 12:39:19Z

1

it's because in Range("ListeD_Protection") there must already be a validation drop down list

so add .Delete before adding the new validation

With Range("ListeD_Protection").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Array_List, ",")
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

answered Oct 3, 2016 at 12:39

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

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

2 Comments

Karaelfte Over a year ago

You just made the few hours I spent searching seem like wasted with your correct answer. Thank you sir.

user3598756 Over a year ago

You are welcome. No other chance: learning is trial and error.

score 1 · Accepted Answer · 2016-10-03 13:44:18Z

1

I would use a defined name to define a dynamic range that would automatically update itself.

Here is how you would do it without code:

Formula: =OFFSET(A1,1,0,COUNTA(A:A)-1,1)

Here is the code to do it programmably:

Worksheets("Sheet1").Range("OFFSET(A1,1,0,COUNTA(A:A)-1,1)").Name = "Accepted_Colors"

With Selection.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="=Accepted_Colors"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

Here is how you could refactor your code using an ArrayList:

Dim Range_Protection As Range
Dim Row_Range As Range
Dim Tableau As Range
Dim ValidationList As String

Dim Array_List As Object

Set Array_List = CreateObject("System.Collections.ArrayList")

Dim Taille_Array As Integer

If Not Range_Protection Is Nothing Then

'The value I want to get are String, don't know if I should use "Cells.Text" instead
Protection_First_Value = Tableau.Cells(1, 1).Value

    For Each Row_Range In Range_Protection.Columns(1)
        If Not Array_List.Contains(Row_Range.Value) Then Array_List.Add Row_Range.Value
    Next Row_Range

    Array_List.Sort

    ValidationList = Join(Array_List.ToArray, ",")
End If

edited Oct 3, 2016 at 13:44

answered Oct 3, 2016 at 13:14

user6432984

2 Comments

Karaelfte Over a year ago

Thank you fore your answer, I could use it but not in my case. My sheets contains more than 15.000 rows and the elements in my Array depends on the selection of a certain row with a certain value in the first column. So instead of doing it like you propose, I wanted to code it in a more dynamic way

user6432984 Over a year ago

No worries. I updated my answer to show you how you can use an actual ArrayList.

Collectives™ on Stack Overflow

Adding String in an Array with a For Each loop

2 Answers 2

2 Comments

Here is how you could refactor your code using an ArrayList:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Here is how you could refactor your code using an ArrayList:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related