Create multiple validation lists with variables

Question

I have 50 dynamic named ranges that are called "Line1_S", "Line2_S", "Line3_S",... to "Line50_S".

On my sheet "Pricing Tool", I want to create 50 validation (drop down) lists in cells C7 to C56.

Cell C7 should have named range "Line1_S", Cell C8 should have "Line2_S" and so forth until C56 has "Line50_S".

I keep getting an error in the formula part of my code; can you please have a look and help me resolve the issue?

Sub test()

Dim j As Integer

For j = 1 To 50

    With Sheets("Pricing Tool").Cells(j + 6, 3).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Line" & j & "_S" ' this is where the error occurs
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Next

End Sub

Many thanks for your help!

Edit - below a sample named range:

=OFFSET('Size Selection'!$E$4, 0, 0, 1, COUNT(IF('Size Selection'!$E$4:$AJ$4="", "", 1)))

what exactly error says? code works fine for me, check names of all your named ranges for tipo — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 14, 2014 at 7:36
Thanks for your help simoco. It says Run-time error '1004'; Application-defined or object defined error. The named ranged seem good — jcv
– jcv, Commented Mar 14, 2014 at 7:42
check the Scope of your named ranges: trumpexcel.com/wp-content/uploads/2013/12/… . Are they have workbook or sheet scope? — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 14, 2014 at 7:45
can you somehow share your workbook (e.g. using dropbox.com) because I don't see something wrong with your code — Dmitry Pavliv
– Dmitry Pavliv, Commented Mar 14, 2014 at 7:51

Dmitry Pavliv · Accepted Answer · 2014-03-14 08:24:50Z

1

As follow up from comments, error triggered by OFFSET(..) when it returns #REF!.

the offset is governed by other drop down lists, and some of these did not have a selection in them, i.e. they were empty

The main idea how to solve it is to choose something from drop-down lists that Offset returns something (not #REF!), run code (it applies validation) and then clear drop-down lists.

answered Mar 14, 2014 at 8:24

Dmitry Pavliv

35.9k13 gold badges84 silver badges85 bronze badges

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

Collectives™ on Stack Overflow

Create multiple validation lists with variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related