ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Looping Through Combobox (https://www.excelbanter.com/excel-programming/406389-looping-through-combobox.html)

[email protected]
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

JLGWhiz
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

Rick Rothstein \(MVP - VB\)[_1310_]
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



All times are GMT +1. The time now is 07:03 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com