PDA

View Full Version : Help with variable cell range



NRG
09-16-2010, 10:33 AM
Hi Guys,

Long time Reader first time poster,

Currently studying my first IT bachelors degree and having some trouble on what sort of function i should be using to achieve what i want.

Basiclly i am trying to populate a combo box with a range of items, however that range is a variable and i am having trouble wraping my head around the format of the code i need to use.

hopefully this gives people an idea of what im trying to do

Private Sub UserForm_Initialize()

Dim varWorksheet As Worksheet
Dim intNo As Integer

Set varWorksheet = Application.Workbooks("Pets.xlsx").Worksheets("Clients")

varWorksheet.Range("b2").Select
intNo = 1

Do Until ActiveCell.Offset(intNo) Is Empty
intNo = intNo + 1
Loop

Me.comClientID.List = Worksheets("Clients").Range("B3:???").Value


End Sub
??? = being the first cell that is empty, but how do i actually code this? :think:

Also im currently getting "Runtime Error 424, Object Required" at the second line of the loop. But i dont know why this is :banghead:

Any help much appreciated!

someboddy
09-16-2010, 02:25 PM
First, lets fix the errors in your code:
Private Sub UserForm_Initialize()

Dim varWorksheet As Worksheet
Dim intNo As Integer

Set varWorksheet = Application.Workbooks("Pets.xlsx").Worksheets("Clients")

varWorksheet.Range("b2").Select
intNo = 1

Do Until isEmpty(ActiveCell.Offset(intNo)) OR ActiveCell.Offset(intNo)=""
intNo = intNo + 1
Loop

Me.comClientID.List = varWorksheet.Range("B3:B"&(1+intNo)).Value


End Sub
I changed loop condition - you get your error because IS Empty doesn't work - you have to use the isEmpty function. Why is that? No friggin idea.

Also, I added ActiveCell.Offset(intNo)="" because blank cells don't register as empty, so it's a good practice to use it with your isEmpty checks unless you're 188% certain the range you want to use spans continuously to the border of the worksheet and that your used range is the actual range you use - and even then it doesn't hurt to use it.

& is VB's string connecting operator, so we use it to create the address of your range. Your range is on the B column and it on the third row, so the beginning of the address is constant: "B3:B" and after that you append the rownumber. Since your loop uses an offset from B2 in the condition the cell it checks every cycle is 2+intNo. You exit the loop on the first empty cell, so that cell is empty and you need the cell above it - which is placed in the row 1+intNo, so we append it to "B3:B".

I also changed the way you call the worksheet on the last command from Worksheets("Clients") to varWorksheet. It would still work(Even though Worksheets("Clients") always refers to a worksheet in the active workbook while varWorksheet is set to a worksheet in Pets.xlsx, if you were activating that code while Clients[and therefore, Pets.xlsx as well] isn't active you would get an error when trying to select a range in that worksheet), but it looks ugly.



Now, after we fixed your code, lets look at a better way to do it:
Private Sub UserForm_Initialize()

Dim varWorksheet As Worksheet

Set varWorksheet = Application.Workbooks("Pets.xlsx").Worksheets("Clients")

Me.comClientID.List = varWorksheet.Range(varWorksheet.Cells(3, 2), varWorksheet.Cells(3, 2).End(xlDown)).Value

End Sub
Instead of an address string, we specify the range here with two cells. The first is varWorksheet.Cells(3, 2) - the cell on the first row of the second column, which is B3. The second is varWorksheet.Cells(3, 2).End(xlDown) - a function that begins at B3 and goes down till it reach a blank or empty cell(and stops before it). It will be a problem if your list only have one item, since it will go all the way down to the limit of the worksheet(the first code would have a different problem in that case - trying to refer to the range B3:B3 throws an error), so if that's a possibility, you'll have to check for it and populate the list with a code if it's true:
Private Sub UserForm_Initialize()

Dim varWorksheet As Worksheet

Set varWorksheet = Application.Workbooks("Pets.xlsx").Worksheets("Clients")

if isEmpty(varWorksheet.cells(4,2)) OR varWorksheet.cells(4,2)="" then
Me.comClientID.Clear
Me.comClientID.AddItem varWorksheet.Cells(3, 2).Value
else
Me.comClientID.List = varWorksheet.Range(varWorksheet.Cells(3, 2), varWorksheet.Cells(3, 2).End(xlDown)).Value
end if

End Sub


Hope that helped!

NRG
09-19-2010, 01:14 AM
Hi Someboddy,

Thank you so much for your help. Im ashamed to admit i should have known about the "&" symbol being the connector in vba. i guess that will teach me to sleep during lectures :rotlaugh:

I quite like your logic in terms of populating the cells based on a 2 cell reference, the logic makes sense but i seem the lack the functions that i actually need to use. it makes sense when i see the finished code but coming up with it myself seems to be a challenge. :(

a perfect example is how i am now trying to populate listboxes based on a combo box selection. ive managed to figure out that the following code populates the combox based on what i have already in the spreadsheet.

Me.comboClientID.List = varWorksheet.Range("B3:B" & (intNo + 1)).Value
but i am having trouble populating listboxes just by itself. So i cant even work on the logic of populating them after a combo box is selected. This is what ive got so far. i know its wrong but im hoping at least ive got the function correct.

Me.listFirstName.RowSource = varWorksheet.Range("C3")
just incase anyone needs it here is the full code

Private Sub UserForm_Initialize()

Dim varWorksheet As Worksheet
Dim intNo As Integer
Dim varString As String

Set varWorksheet = Application.Workbooks("PetsFirst_21159165.xlsm").Worksheets("Clients")

varWorksheet.Range("b2").Select
intNo = 1

Do Until IsEmpty(ActiveCell.Offset(intNo))
intNo = intNo + 1
Loop

Me.comboClientID.List = varWorksheet.Range("B3:B" & (intNo + 1)).Value
Me.listFirstName.RowSource = varWorksheet.Range("C3")

End Sub

someboddy
09-19-2010, 01:10 PM
It's hard to understand what you really want here. Using RowSource does change when the values chance - but the range itself stays constant until you change it via code(not sure what happens if you insert/delete cells in the middle of that range, and I don't have Excel right now so I can't check it out).

If you want to keep the source range dynamic, you can either name it(and then, ofcourse, you need to make sure that all ranges would be included in that named range) or you can listen to the Worksheet_Change event and rerun your code to redetermine the source range of your ComboBox each and every time there is a change in the worksheet.