How to create a excel Fn to select a range?

Microsoft Office excel 2003
March 1, 2011 at 09:49:27
Specs: Windows 7
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)
End If
End Function

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

See More: How to create a excel Fn to select a range?

Report •

March 1, 2011 at 20:33:58
You don't need VBA to accomplish your goal.

Just use a Dependent Drop Down as described here:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 2, 2011 at 09:55:11
Thank you that worked great!

Report •

Related Solutions

Ask Question