PDA

View Full Version : Fill combobox from column and with n rows



fadib
12-20-2007, 04:45 PM
Private Sub ComboBox1_????()
Dim a As Variant
Dim Row As Integer
Dim AN As Variant

With Worksheets("Vol_data")
Row = 2
'Check if row is occupied
Do
If .Cells(Row, "A").Text <> "" Then
Row = Row + 1
End If
Loop While .Cells(Row, "A").Text <> ""
AN = "A" & (Row - 1)
a = .Range("A2:AN")
UserForm1.ComboBox1.List = a
End With
End Sub

Hi guys,
What this code is suppose to accomplish, is:
go to sheet "Vol_data", check how many rows are occupied in column A, store this value in AN. (not sure if we need to do this step)
Then when the user form is launched the combobox is filled with only nth rows.

I hope my description makes sense.
My error is taking place at the following line
a = .Range("A2:AN")

tpoynton
12-20-2007, 07:52 PM
Private Sub ComboBox1_????()
Dim cell as range

With Worksheets("Vol_data")
for each cell in .range(.cells(2, 1), .cells(65536, 1).end(xlup)
userform1.combobox1.additem cell.value
next cell
End With
End Sub

not tested, but this should populate the combobox.

where is this code? might be good to put it in the userform_activate event

gwkenny
12-21-2007, 02:17 AM
Instead of just "a = .Range("A2:AN") "

Try putting SET in front of it:
"Set a = .Range("A2:AN")"

Does it work?

g-
gwkenny@yahoo.com
___________________________________
I need holiday money. Got any jobs, big or small, drop me a line! Thanks!

tpoynton
12-21-2007, 06:46 AM
If the OP wanted to use that, I think it would be

a = .Range("A2:A" & AN)

I like to use row and column #'s, as that makes more sense to me

a = .Range(.cells(2, 1), .cells(AN, 1))

using set seems right though!

rory
12-21-2007, 07:37 AM
You would not use Set as you are trying to assign the value, not the range object. As AN includes the column letter, it would be:
a = .Range("A2:" & AN).Value

Bob Phillips
12-21-2007, 07:49 AM
Private Sub ComboBox1_DropButtonClick()

Dim a As Variant
Dim RowNum As Long

With Worksheets("Sheet3") '"Vol_data")
RowNum = .Cells(.Rows.Count, "A").End(xlUp).Row
Me.ComboBox1.List = Application.Transpose(.Range("A2:A" & RowNum))
End With
End Sub

gwkenny
12-21-2007, 08:21 AM
Rory is spot on and with the right solution.

That's what happens when you don't read the whole post :D

I zoomed in to the line where the error occured where a range was being assigned to a variable without SET.

I should have read the whole message to understand this didn't give the OP his solution.

Egg on me :D

Norie
12-21-2007, 09:16 AM
Why not just set the RowSource?

AN = Range("A" & Rows.Count).End(xlUp).Row
ComboBox1.RowSource = "A2:A" & AN

fadib
12-21-2007, 10:41 AM
Sorry guys for responding late,
This is awesome feedback, it worked!!!
Now this is what I call VBA excell experts.
Thanks guys.
:bow: :bow: :bow: