Code modification

  • Thread starter Thread starter Todd Huttenstine
  • Start date Start date
T

Todd Huttenstine

ComboBox1.Value = "Please Select"
Dim rng As Range
Dim Ndx As Long
With ComboBox1
.ColumnCount = 1
For Each rng In Workbooks("2004 Renewals New.xls")
_
.Worksheets("Renewals WOW 2004").Range("D1:BO1")
If rng.Value <> "" Then
.AddItem rng.Value
End If
Next rng
End With

The above code adds items in the specified range to
combobox1 only if the cell does not equal "". I would
also like it to not add the items that have MTD somehwere
in the value.

For example lets say say one of the cells in the range
said "Oct MTD". If this was the case I would want the
code to not add this because it contains MTD in the value.


Thank you
Todd Huttenstine
 
Hi Todd
not tested but try replacing the line
If rng.Value <> "" Then

with
If rng.Value <> "" And InStr(LCase(rng.Value), "mtd") = 0 Then
 
worked great! Thank you

Todd
-----Original Message-----
Hi Todd
not tested but try replacing the line
If rng.Value <> "" Then

with
If rng.Value <> "" And InStr(LCase(rng.Value), "mtd") = 0 Then


--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
I suggest using

If rng.Value <> "" Then
If InStr(LCase(rng.Value), "mtd") = 0 Then

as otherwise VBA evaluates both statements

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top