Adding validation list to dynamic cell range via VBA in Excel 2013

Question

I'm just beginning to get into Excel VBA coding, having basic VB knowledge and searching for the appropriate solutions on the net ;) I'm working on a staff rostering solution in Excel. It is growing quite complex for a newbie, but I've got time and I am eager to learn.

On my main worksheet, the number of columns equals the number of days for the current month and the number of rows is created by a vba function that loads a list of all clients from another worksheet, so far, so good.

Now i want to populate the validation lists of each cell in this range (firstDayfirstClient:lastDaylastClient) with the list of employee IDs from yet another worksheet. To do so, I managed to write the following function:

Sub createDD()
Dim bSh As Worksheet
Set bSh = Sheets("sht_MA")
Sheets("sht_Main").Activate
Dim c As Range
c = Range(Cells(4, 4), Cells(Cells(Rows.Count, 1).End(xlUp).Row, _
    Cells(3, 256).End(xlToLeft).Column))
With c.Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
     Operator:=xlBetween, Formula1:=bSh.Range("tbl_MA[ID]")
    .IgnoreBlank = True
    .InCellDropdown = True
End With
End Sub

This always throws me an error 91 (with block variable not set), even if I hardcode the Range with

c = Range("D4", "AH6")

Can anyone tell me if I am missing something or haven't got the concept right? I have also tried to not use the active worksheet when defining the range but to address it explicitly like so

Dim aSh, bSh As Worksheet
Set bSh = Sheets("sht_MA")
Set aSh = Sheets("sht_Main")
Dim c As Range
c = aSh.Range(aSh.Cells(4, 4), aSh.Cells(aSh.Cells(aSh.Rows.Count, 1) _ 
    .End(xlUp).Row, aSh.Cells(3, 256).End(xlToLeft).Column))

which is more confusing but doesn't help either.

I hope this gets across what I am trying to say, as I am neither a native speaker of English nor VBA ;) Any help is greatly appreciated!

Chris

You need to use the Set keyword for objects, as in Set c = ... — Doug Glancy

Unknown Unknown · Accepted Answer · 2014-05-18T16:20:28

Because you already did Dim c As Range, that means right now c is a Range Object. Every time you wanna assign value/something to an object, remember to start with keyword SET!

In this case, you should go like this:

Set c = Range("D4", "AH6")

Hope this helps.

Adding validation list to dynamic cell range via VBA in Excel 2013

1 Answers