range problem

[Valeria]

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

 

[Rick Rothstein]

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"))

 

[Mike H]

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