Basic Stuff to some

[Corey]

Being a lerner at Vb, why do i get an error from the below ?

If the Combobox2.value = a value in Range(C159:C210) then i want a message to say so !!

Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim rng As Range
With Sheets("Scheduled In")
Set rng = Range("C159:C210")
If ComboBox2.Value = rng.Cells.Value Then '<== Error here
MsgBox "Already Used"
Else
MsgBox "Done"
End If
End With
End Sub


How do i write that line then?

CTM

 

[Tom Ogilvy]

Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim rng As Range, res as variant
With Sheets("Scheduled In")
Set rng = .Range("C159:C210") ' <= include period
res = Application.Match(Combobox2.Value, rng,0)
If not iserror(res) Then '<== Error here
MsgBox "Already Used"
Else
MsgBox "Done"
End If
End With
End Sub

if Combobox2 contains a number then

res = Application.Match(cdbl(Combobox2.Value), rng,0)

the original causes a type mismatch error I suspect because you can't
compare a string to an array.