Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
range problem
Dear Experts,
I am trying to populate a combobox in a userform with data looked up from a worksheet. Here is the code: Private Sub Userform_Initialize() Dim rng As Range Dim End_Row As Integer Dim Begin_Row As Integer Dim i As Integer i = 0 Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) = Worksheets("Choix").Cells(4, 1) Begin_Row = i Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) < Worksheets("Choix").Cells(4, 1) End_Row = i - 1 Set rng = Range((Worksheets("Data").Range("B", Begin_Row)), Worksheets("Data").Range("B", End_Row)) Me.ComboBox1.List = rng.Value End Sub However I always get an error when I get to "set rng". Can you please help me? I am using excel 2003. Thanks! Kind regards -- Valeria |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
range problem
The syntax for your Range property call is not correct. Change the commas to
ampersands... Set rng = Range(Worksheets("Data").Range("B" & Begin_Row), _ Worksheets("Data").Range("B" & End_Row)) Or, you can use the Cells property instead of the Range property which does use the structure you attempted (but with the row value coming first and the column value last) in order to avoid the concatenations... Set rng = Range(Worksheets("Data").Cells(Begin_Row, "B"), _ Worksheets("Data").Cells(End_Row, "B")) -- Rick (MVP - Excel) "Valeria" wrote in message ... Dear Experts, I am trying to populate a combobox in a userform with data looked up from a worksheet. Here is the code: Private Sub Userform_Initialize() Dim rng As Range Dim End_Row As Integer Dim Begin_Row As Integer Dim i As Integer i = 0 Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) = Worksheets("Choix").Cells(4, 1) Begin_Row = i Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) < Worksheets("Choix").Cells(4, 1) End_Row = i - 1 Set rng = Range((Worksheets("Data").Range("B", Begin_Row)), Worksheets("Data").Range("B", End_Row)) Me.ComboBox1.List = rng.Value End Sub However I always get an error when I get to "set rng". Can you please help me? I am using excel 2003. Thanks! Kind regards -- Valeria |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
range problem
Hi,
Try this Private Sub Userform_Initialize() Dim End_Row As Long Dim Begin_Row As Long Dim i As Long i = 0 Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) _ = Worksheets("Choix").Cells(4, 1) Begin_Row = i Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) _ < Worksheets("Choix").Cells(4, 1) End_Row = i - 1 Me.ComboBox1.List = Worksheets("Data").Range( _ "B" & Begin_Row & ":B" & End_Row).Value End Sub Mike "Valeria" wrote: Dear Experts, I am trying to populate a combobox in a userform with data looked up from a worksheet. Here is the code: Private Sub Userform_Initialize() Dim rng As Range Dim End_Row As Integer Dim Begin_Row As Integer Dim i As Integer i = 0 Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) = Worksheets("Choix").Cells(4, 1) Begin_Row = i Do i = i + 1 Loop Until Worksheets("Data").Cells(i, 1) < Worksheets("Choix").Cells(4, 1) End_Row = i - 1 Set rng = Range((Worksheets("Data").Range("B", Begin_Row)), Worksheets("Data").Range("B", End_Row)) Me.ComboBox1.List = rng.Value End Sub However I always get an error when I get to "set rng". Can you please help me? I am using excel 2003. Thanks! Kind regards -- Valeria |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find function for a Range failing in excel 2003 and giving subscriptout of range error 9 problem but works fine in excel 2000 | Excel Programming | |||
Range Problem | Excel Programming | |||
Range Problem | Excel Discussion (Misc queries) | |||
Range problem | Excel Programming | |||
range problem | Excel Programming |