0

After months of running, out of nowhere the script keeps failing. Can someone please check my script as shown below:

Sub Data_Types()
    Dim MyList(200) As String
    Dim Rows_used_Componenten_Library




    Rows_used_Componenten_Library = Worksheets("Component types").UsedRange.Rows.Count


    For h = 0 To Rows_used_Componenten_Library
        MyList(h) = Worksheets("Component types").Range("A2").Offset(h, 0).Value
    Next h

    For i = 0 To 202
        With Worksheets("Component list").Range("H2").Offset(i, 0).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(MyList, ",")
        End With
    Next i

    MsgBox "Component keuze overgenomen"

    End Sub
Automate This
  • 30,726
  • 11
  • 60
  • 82
Mattijn Stegeman
  • 43
  • 6
  • Excel comes up with the error on this line: .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(MyList, ",") – Mattijn Stegeman Mar 14 '19 at 11:08
  • I didn't got any error when running the code with empty sheets. Maybe check what does `MyList` contain when the error is thrown. It might also not be a bad idea to check the size of your sheet's `UsedRange` since you make use of it. – user7393973 Mar 14 '19 at 11:24
  • @user7393973 Thank you for helping. That is the weirdest part of the story indeed. If I try to run the code with different worksheets, it's running perfectly fine. The MyList contains strings of tekst... Nothing weird at all. – Mattijn Stegeman Mar 14 '19 at 11:28
  • 1
    Note that you can add Validation to a range of cells, you don't have to do this individually per cell. It is also possible to get a range of values directly into an array. – Andy G Mar 14 '19 at 11:28
  • Are there any unexpected characters in the list? – Andy G Mar 14 '19 at 11:29
  • I don't have the answer but if you need to look it up I would suggest checking [this post](https://stackoverflow.com/a/9612349/7393973)'s answers and/or search for Excel VBA Validation.Add Errors or Error 1004. – user7393973 Mar 14 '19 at 11:32
  • @AndyG That's it! Very strange... This is what I have in my list: Noodstop, Stuurspanning niet aanwezig, Paneel te warm, Product output, Stof output, Residue output, Product supply, Product detection (FSAxxxx), Deurschakelaar (ESxxxx), Motor VSD, Motor Ster/Driehoek, Motor D.O.L., Heater, Tracing, XV klep zonder terugmelding, XV klep zonder terugmelding met puls pause, EP klep monostabiel met open/dicht melding, EP klep bistabiel met open/dicht melding, V klep 3 punts sturing met positie feedback en open/dicht/vrijgave meldingen, – Mattijn Stegeman Mar 14 '19 at 11:35
  • I would edit the code to just manually add a few simple words to the list and see if it works. This will help narrow it down to discover or confirm whether the list values are the problem. Also remove that additional comma at the end. Are there any foreign characters there that aren't showing in your comment? – Andy G Mar 14 '19 at 11:40
  • @AndyG The point is that it seems to work for a couple tags.. Kinda weird.. – Mattijn Stegeman Mar 14 '19 at 11:47
  • 1
    You could keep adding 'tags' and checking that it works until you discover which one is causing the problem. – Andy G Mar 14 '19 at 11:48
  • @AndyG Completely random... Really don't know what's going on. – Mattijn Stegeman Mar 14 '19 at 11:53
  • 1
    Maybe some hidden character within the text? You could keep testing to narrow it down. Check the Excel limits for Validation lists, although I doubt that you exceed them. – Andy G Mar 14 '19 at 11:56
  • @AndyG I am searching for it but can not find anything at all... Sometimes I hate Excel's VBA haha – Mattijn Stegeman Mar 14 '19 at 13:20
  • I would also first get rid of the loop `For i = 0 To 202` and, as mentioned, just apply the validation to the required range. – Andy G Mar 14 '19 at 13:54
  • @AndyG Already did that... Now it's like this: .Range("H2:H202").Validation – Mattijn Stegeman Mar 14 '19 at 14:03
  • Still not solved... I don't have any ideas where to search for it. Can someone help me please? – Mattijn Stegeman Apr 02 '19 at 13:10

0 Answers0