Error with data validation and string contruction

Question

I am trying to add data validation to a set of cells based on a range of cells from another worksheet. Problem is that the range of cells in the other worksheet is not static and can change.

Overall I am looking for a set of dropdown boxes in the A10:A29 cells with the ingredients in them

When I use =INDIRECT("Ingredients!A2:A320) just using the excel validation wizard it works but I need the end cell to be dynamic.

I have this current vba code

Dim endrow As Integer

endrow = Sheets("Ingredients").Range("A" & Rows.Count).End(xlUp).Row

Range("A10:A29").Select
 With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
         xlBetween, Formula1:="=INDIRECT(" & Chr(34) & "Ingredients!A2:A" & endrow & Chr(34) & ")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

I get a 1004 error on this code.

To make it easier for anyone looking at this the end result I am aiming for in the formula section is this:

=INDIRECT("Ingredients!A2:A*endrow*)

You don't need VBA to do this. Use named range instead. Also you can find a lot of article regarding dynamic validation. If I can find time, I'll post an answer for you. — L42
@L42 is right - just search for dynamic range (eg: support.microsoft.com/kb/830287). As a side note, be careful with Integer: it fails after row 32,767.. And Using the Selection object is probably not a good idea in most cases (including this one). See stackoverflow.com/questions/10714251/… — Ioannis
I did look at dynamic range naming but the source worksheet is sometimes completely deleted and and a new one copied in from another workbook so all range naming is lost. That's why I am trying to do VBA data validation when the worksheet and data is changed. — Matt Bartlett
@MattBartlett Then just replace the reference to the sheet. If you want to stick with your logic though, I'll see if I can try to help you with your code. :) — L42

L42 L42 · Accepted Answer · 2014-03-03T02:08:30

As commented here is my answer to your question without VBA:

This Demo is done using Excel 2010:

From the ribbon choose Formula Tab

Formula Tab

Then Select Name Manager.

Name Manager

Select New.
Provide a name for the named range (in my example it is MyRange).
Then put below formula in Refers To.

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1)

Press OK.
Take note that I assumed Sheet1 as validation list source.

New Name dialogue

You will return to Name Manager dialogue:
Notice that your newly created name was already added.
Press Close

Name Manager with New NAme

Now, go to Validation.
In the validation dialogue, choose List in Allow Field.
Then in enter below formula in Source.

=MyRange

Validation Criteria

Of course you need to press OK.

Important: You can directly input the formula used in the Named Range here.
But having a named range provides data visibility.

Suppose we apply Validation in Sheet1 Cell C1, it will look like below:

Validation Sample

Adding another item or data will result to:

Validation Sample2

Notice that the validation list automatically adjusted.
Hope this helps you a bit.

EDIT1: If you want to stick to your logic, above can be done in VBA using below code:

Sub DynamicValidation()

Dim ws As Worksheet
Dim rng As Range, valrng As Range
Dim valformula As String

Set ws = ThisWorkbook.Sheets("Sheet1")
Set rng = ws.Range("C1") '~~> where you want to put the validation
'~~> check if you have list, exit if none
If ws.Range("A2").Value = "" Then Exit Sub
'~~> Below contains your dynamic range
Set valrng = ws.Range(ws.Range("A2"), ws.Range("A" & ws.Rows.Count).End(xlUp))
'~~> your formula based on your dynamic range
valformula = "=" & valrng.Address
'~~> actual application of validation
With rng.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=valformula '~~> use the variable here
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub

Above code is tried and tested.
Everytime you run the macro, it will update the validation list.
Just adjust the references to suit your needs.

Error with data validation and string contruction

3 Answers