| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping Through Combobox
I want to loop through every item in a combobox. I have tried
For each item in combobox21 and For i=4 to 100 combobox.value=sheets("io").cells(i,21) ' where sheets("io").cells(i, 21) from 4 to 100 is the listrange i get a typemismatch with the for each loop and a object 424 with the for i=4 to 100 the debugger says the combobox is empty but i can physically see values in it any ideas? thanks in advance here is the code: For i = 4 To 100 ComboBox21.Value = Sheets("io").Cells(i, 21) If IsEmpty(Sheets("io").Range("u101")) = True Then Exit For With Sheets("quote") .Cells(3 + step * Sheets("io").Range("u101"), 1) = Sheets("IO").Range("f20") .Cells(3 + step * Sheets("io").Range("u101"), 2) = Sheets("io").Range("u101") .Cells(4 + step * Sheets("io").Range("u101"), 3) = "Building" .Cells(5 + step * Sheets("io").Range("u101"), 3) = "Personal Property" .Cells(6 + step * Sheets("io").Range("u101"), 3) = "Business Income" If Sheets("quick rate").Range("b9") < 0 Then 'building .Cells(4 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("b9") .Cells(4 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t9") .Cells(4 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t19") .Cells(4 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t27") + Sheets("quick rate").Range("t33") End If If Sheets("quick rate").Range("a7") < 0 Then 'Personal Property .Cells(5 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("a9") .Cells(5 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t13") .Cells(5 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t23") .Cells(5 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t29") + Sheets("quick rate").Range("t37") _ + Sheets("quick rate").Range("t39") End If If Sheets("quick rate").Range("e9") < 0 Then 'business income .Cells(6 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("e9") .Cells(6 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t43") .Cells(6 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t47") .Cells(6 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t51") End If .Columns("D").NumberFormat = "###,###,###" .Columns("e").NumberFormat = "###,###,###" .Columns("f").NumberFormat = "###,###,###" .Columns("g").NumberFormat = "###,###,###" End With Next i |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping Through Combobox
this might work:
For each item in combobox21.List " wrote: I want to loop through every item in a combobox. I have tried For each item in combobox21 and For i=4 to 100 combobox.value=sheets("io").cells(i,21) ' where sheets("io").cells(i, 21) from 4 to 100 is the listrange i get a typemismatch with the for each loop and a object 424 with the for i=4 to 100 the debugger says the combobox is empty but i can physically see values in it any ideas? thanks in advance here is the code: For i = 4 To 100 ComboBox21.Value = Sheets("io").Cells(i, 21) If IsEmpty(Sheets("io").Range("u101")) = True Then Exit For With Sheets("quote") .Cells(3 + step * Sheets("io").Range("u101"), 1) = Sheets("IO").Range("f20") .Cells(3 + step * Sheets("io").Range("u101"), 2) = Sheets("io").Range("u101") .Cells(4 + step * Sheets("io").Range("u101"), 3) = "Building" .Cells(5 + step * Sheets("io").Range("u101"), 3) = "Personal Property" .Cells(6 + step * Sheets("io").Range("u101"), 3) = "Business Income" If Sheets("quick rate").Range("b9") < 0 Then 'building .Cells(4 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("b9") .Cells(4 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t9") .Cells(4 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t19") .Cells(4 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t27") + Sheets("quick rate").Range("t33") End If If Sheets("quick rate").Range("a7") < 0 Then 'Personal Property .Cells(5 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("a9") .Cells(5 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t13") .Cells(5 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t23") .Cells(5 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t29") + Sheets("quick rate").Range("t37") _ + Sheets("quick rate").Range("t39") End If If Sheets("quick rate").Range("e9") < 0 Then 'business income .Cells(6 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("e9") .Cells(6 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t43") .Cells(6 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t47") .Cells(6 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t51") End If .Columns("D").NumberFormat = "###,###,###" .Columns("e").NumberFormat = "###,###,###" .Columns("f").NumberFormat = "###,###,###" .Columns("g").NumberFormat = "###,###,###" End With Next i |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looping Through Combobox
What about this...
For X = 0 To combobox21.ListCount - 1 and in your code, refer to the item currently being processed this way... combobox21.List(X) Rick wrote in message ... I want to loop through every item in a combobox. I have tried For each item in combobox21 and For i=4 to 100 combobox.value=sheets("io").cells(i,21) ' where sheets("io").cells(i, 21) from 4 to 100 is the listrange i get a typemismatch with the for each loop and a object 424 with the for i=4 to 100 the debugger says the combobox is empty but i can physically see values in it any ideas? thanks in advance here is the code: For i = 4 To 100 ComboBox21.Value = Sheets("io").Cells(i, 21) If IsEmpty(Sheets("io").Range("u101")) = True Then Exit For With Sheets("quote") .Cells(3 + step * Sheets("io").Range("u101"), 1) = Sheets("IO").Range("f20") .Cells(3 + step * Sheets("io").Range("u101"), 2) = Sheets("io").Range("u101") .Cells(4 + step * Sheets("io").Range("u101"), 3) = "Building" .Cells(5 + step * Sheets("io").Range("u101"), 3) = "Personal Property" .Cells(6 + step * Sheets("io").Range("u101"), 3) = "Business Income" If Sheets("quick rate").Range("b9") < 0 Then 'building .Cells(4 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("b9") .Cells(4 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t9") .Cells(4 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t19") .Cells(4 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t27") + Sheets("quick rate").Range("t33") End If If Sheets("quick rate").Range("a7") < 0 Then 'Personal Property .Cells(5 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("a9") .Cells(5 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t13") .Cells(5 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t23") .Cells(5 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t29") + Sheets("quick rate").Range("t37") _ + Sheets("quick rate").Range("t39") End If If Sheets("quick rate").Range("e9") < 0 Then 'business income .Cells(6 + step * Sheets("io").Range("u101"), 4) = Sheets("quick rate").Range("e9") .Cells(6 + step * Sheets("io").Range("u101"), 5) = Sheets("quick rate").Range("t43") .Cells(6 + step * Sheets("io").Range("u101"), 6) = Sheets("quick rate").Range("t47") .Cells(6 + step * Sheets("io").Range("u101"), 7) = Sheets("quick rate").Range("t51") End If .Columns("D").NumberFormat = "###,###,###" .Columns("e").NumberFormat = "###,###,###" .Columns("f").NumberFormat = "###,###,###" .Columns("g").NumberFormat = "###,###,###" End With Next i |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combobox items determined by the selection in another combobox | Excel Programming | |||
| Combobox options based on the input of another combobox | Excel Programming | |||
| ComboBox list reliant on the entry from a different ComboBox | Excel Programming | |||
| Problem looping through combobox | Excel Programming | |||
| How Do I Load A ComboBox RowSource From The Results Of Another ComboBox | Excel Programming | |||
Hybrid Mode
