I have a combo box whose source is a named range ("variablelist"). However I need to update the named range every time new data is input into the spreadsheet.
I have therefore written the following code to update the named range. However when I run it I get 'ERROR 424 - Object Required 'on the SET statement. I have MS DAO 3.6 Object Library referenced. Does anyone know what is causing this error?
Dim rngVariable As Range
Dim strSet as String
strset = "Settings"
Sheets(strSet).Select
Range("A2").Select
Set rngVariable = Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="VariableList", RefersTo:=rngVariable
I have therefore written the following code to update the named range. However when I run it I get 'ERROR 424 - Object Required 'on the SET statement. I have MS DAO 3.6 Object Library referenced. Does anyone know what is causing this error?
Dim rngVariable As Range
Dim strSet as String
strset = "Settings"
Sheets(strSet).Select
Range("A2").Select
Set rngVariable = Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Names.Add Name:="VariableList", RefersTo:=rngVariable