PDA

View Full Version : Solved: values in range



fgarcia90
07-16-2012, 06:21 AM
I have a combox that I want to put the values in sheet1 but if I put C30 it will appear many blanks
And it as to be dinamic so the user inputs something and vba find the cells with content and put tehm in the combo list.
If I can understand I adapt to my file :)


File attached :)

Thanks in advanced

CodeNinja
07-16-2012, 09:03 AM
fgarcia90,
Try using the following code in the UserForm_Activate()...

Private Sub UserForm_Activate()
Dim i As Integer

'UserForm1.ComboBox1 = Sheet1.Range("C2:C30").Value
For i = 2 To 30
If Sheet1.Cells(i, 3) <> "" Then UserForm1.ComboBox1.AddItem (Sheet1.Cells(i, 3))
Next i

End Sub

fgarcia90
07-16-2012, 11:39 AM
Thanks Ninja.
But do you know how to replace the value of 30, and making vba search the last row in that collum with content to add to the combo?
And another thing... doesn't should appear somewhere in the code the collum "C"?
Is it the ",3"?
I'm new in vba :-)

:banghead:

CodeNinja
07-16-2012, 12:25 PM
use sheet1.range("C65536").end(xlup).row instead of 30... so...

Private Sub UserForm_Activate()
Dim i As Integer

'UserForm1.ComboBox1 = Sheet1.Range("C2:C30").Value
For i = 2 To Sheet1.Range("C65536").End(xlUp).Row
If Sheet1.Cells(i, 3) <> "" Then UserForm1.ComboBox1.AddItem (Sheet1.Cells(i, 3))
Next i

End Sub

Rob342
07-16-2012, 12:53 PM
try this example attached

fgarcia90
07-16-2012, 01:50 PM
Rob342, Ninja code works but yours simpler, but where is "mycode"?

oh yeah, just saw, in name manager

thanks both