S
Spencer.Sadkin
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
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