I am trying to create a excel function to select a range for a drop down menu
Basically i want to drop down menu's: 1 for Products and 1 that lists the Run # specific to the product selected in the first drop down menu.
Since there are 24 product I cant just use if statements in the drop down formula. So I wanted to make my own Function using VB.
This is what I have but for some reason it doesn't work
Function MyRange(Cell As Range) As Range
Dim RowNum As Range
For i = 2 To 25
Set RowNum = Sheets(4).Range("A9") '& i)
If Cell.Value = RowNum.Value Then
FirstInRange = RowNum.Offset(0, 2).Address(ColumnAbsolute:=False)
LastInRange = RowNum.Offset(0, 11).Address(ColumnAbsolute:=False)
Set MyRange = Range(FirstInRange & ":" & LastInRange)
Then When I go to create the drop down menu in data Val I enter:
=MyRange( [cell] )
And Hopefully give me the list of run #s that go with the product listed in the cell I pass into the formula