0

I have the following code:

Dim RR As Range RR = Sheet90.Range("AA25:AA46") For i = 1 To ComboBox1.ListCount 'Remove an item from the ListBox. ComboBox1.RemoveItem 0 Next i Dim R As Range For Each R In RR.Cells If Len(R.Value) > 0 Then Sheet90.ComboBox1.AddItem (R.Value) End If Next R

Obviously, it does not work.

I'm trying to populate a combobox with the non-blank values of a defined array.

Excel VBA does not approve of my methodology, and its error messages are less than helpful.

It says,

Run-time error '91' Object variable or With block variable not set

I assume this is some sort of lexical scoping issue, but for the life of me, I can't understand it. Everything is enclosed in a single private SUB, and oddly enough when I just run:

RR = Sheet90.Range("AA25:AA46") For i = 1 To ComboBox1.ListCount 'Remove an item from the ListBox. ComboBox1.RemoveItem 0 Next i

it clears the box.

If I Dim RR As Range, everything falls to pieces.

I haven't written vba in ages, so I'm probably making some kind of simple mistake, and I'd really appreciate any guidance.

Thanks!

Improve this question

2 Answers 2

Reset to default
4

The problem is you need to use the Set keyword when assigning object variables:

'Change this line: RR = Sheet90.Range("AA25:AA46") 'To be this instead: Set RR = Sheet90.Range("AA25:AA46")
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

tiger's solution is definitely the fix for your error.

Since you were looking for some assistance/guidance, I wanted to give you some additional tips on working with your comboboxes, that were too much to put in a comment, so here goes:

clear out the combobox (instead of using a loop) with one statement: ComboBox1.Clear

Also, assuming that your range is not filled with formulas, etc., you may be able to use the SpecialCells method to return the non-blank cells in that range, thus avoiding the extra step where you check for blank value (Len(r.Value) > 0).

Dim RR As Range Dim r As Range Set RR = Range("AA25:AA46").SpecialCells(xlCellTypeConstants) ComboBox1.Clear For Each r In RR.Cells ComboBox1.AddItem r Next
Improve this answer

5 Comments

sadly, the range is filled with formulas... I'm trying to emulate a simple array from multiple sources, and doing it in JavaScript would be so easy, but the refresher in vba has been helpful if frustrating. Basically I just need to check multiple non-contiguous areas to see if a value is used and remove it from a list. My horrific work around hack is using five cells for every name on the list, a macro, and an onClick to populate a combo box.
If range is full of formulas then try using specialcells (xlcelltypeformulas)
Specifically I need the results that are not blank, The formula takes the value from the cell to it's left IFF that value is not used anywhere on the sheet. Otherwise it returns blank.
OK then the SpecialCells method probably won't work. But the ComboBox1.Clear statement should work.
implemented in newest iteration of my code mess... Things are starting to clear up, and most of the extraneous cells are getting moved into the vba. it was the big Set keyword problem because I'm used to just defining and declaring variables, not having to explicitly "set" them.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.