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.

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.

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.


.