Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning an array in excel
Hi, I'm failrly new to VBA and I wondered if anyone could
help me with this problem. I am trying to use an array from a worksheet 'Data' to return a picklist when entering details on another worksheet. I can get the required picklist to work if I specify the range (see code below) but the data list is not static in that the end cell range can be greater or more than the defined end range cell in my code(A60). How can I change my code so that the specified range starts at cell A2 and ends in the last cell where data is input on the 'Data' worksheet. The code i'm currently using is: Sub AddDropDown(Target As Range) Dim ddbox As DropDown Dim vaCompany As Variant Dim i As Integer vaCompany = Array(Sheets("data").Range("A2:A60")) With Target Set ddbox = Sheet3.DropDowns.Add (.Left, .Top, .Width, .Height) End With With ddbox ..OnAction = "Sheet3.EnterCompName" For i = LBound(vaCompany) To UBound(vaCompany) ..AddItem vaCompany(i) Next i End With End Sub Thanks for your help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning an array in excel
Sub AddDropDown(Target As Range)
Dim ddbox As DropDown Dim vaCompany As Variant Dim i As Integer, rng As Range With Sheets("Data") Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)) End With vaCompany = rng.Value With Target Set ddbox = Sheet3.DropDowns.Add(.Left, .Top, .Width, .Height) End With With ddbox ..OnAction = "Sheet3.EnterCompName" ..List = vaCompany End With End Sub -- Regards, Tom Ogilvy "mick" wrote in message ... Hi, I'm failrly new to VBA and I wondered if anyone could help me with this problem. I am trying to use an array from a worksheet 'Data' to return a picklist when entering details on another worksheet. I can get the required picklist to work if I specify the range (see code below) but the data list is not static in that the end cell range can be greater or more than the defined end range cell in my code(A60). How can I change my code so that the specified range starts at cell A2 and ends in the last cell where data is input on the 'Data' worksheet. The code i'm currently using is: Sub AddDropDown(Target As Range) Dim ddbox As DropDown Dim vaCompany As Variant Dim i As Integer vaCompany = Array(Sheets("data").Range("A2:A60")) With Target Set ddbox = Sheet3.DropDowns.Add (.Left, .Top, .Width, .Height) End With With ddbox .OnAction = "Sheet3.EnterCompName" For i = LBound(vaCompany) To UBound(vaCompany) .AddItem vaCompany(i) Next i End With End Sub Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Returning an array in excel
Thanks Tom, your help is much appreciated. I can see where
I was going wrong. Regards Mick. -----Original Message----- Sub AddDropDown(Target As Range) Dim ddbox As DropDown Dim vaCompany As Variant Dim i As Integer, rng As Range With Sheets("Data") Set rng = .Range(.Cells(2, 1), .Cells(2, 1).End(xlDown)) End With vaCompany = rng.Value With Target Set ddbox = Sheet3.DropDowns.Add (.Left, .Top, .Width, .Height) End With With ddbox ..OnAction = "Sheet3.EnterCompName" ..List = vaCompany End With End Sub -- Regards, Tom Ogilvy "mick" wrote in message ... Hi, I'm failrly new to VBA and I wondered if anyone could help me with this problem. I am trying to use an array from a worksheet 'Data' to return a picklist when entering details on another worksheet. I can get the required picklist to work if I specify the range (see code below) but the data list is not static in that the end cell range can be greater or more than the defined end range cell in my code(A60). How can I change my code so that the specified range starts at cell A2 and ends in the last cell where data is input on the 'Data' worksheet. The code i'm currently using is: Sub AddDropDown(Target As Range) Dim ddbox As DropDown Dim vaCompany As Variant Dim i As Integer vaCompany = Array(Sheets("data").Range("A2:A60")) With Target Set ddbox = Sheet3.DropDowns.Add (.Left, .Top, .Width, .Height) End With With ddbox .OnAction = "Sheet3.EnterCompName" For i = LBound(vaCompany) To UBound(vaCompany) .AddItem vaCompany(i) Next i End With End Sub Thanks for your help. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning an array of unique values? | Excel Worksheet Functions | |||
Array formula returning the cumulative sum | Excel Worksheet Functions | |||
Returning an address from an array | Excel Discussion (Misc queries) | |||
Returning an array from a UDF to a column range | Excel Programming | |||
returning an array from a custom function | Excel Programming |