0

I'm currently the trying to now if a value exist or not in the "B:B" range. for this i've using the following code:

code=range("B:B").find("the value I look for")

This is supposed to give me the answer "Nothing" if the value is not found, but instead it gives me the error 91 "error 91 object variable or with block not set".

this was my idea when I started:

if code = Nothing then msgbox "the value does not exist" exit sub else end if

I would really apreciate your help!

Juan Camilo Rojas Caldas
  • 1
  • 1
    You forgot the `Set`: `Set code = ...` – BigBen Jan 06 '21 at 15:19
  • waht do you mean? set code = range("B:B") code.find("value") – Juan Camilo Rojas Caldas Jan 06 '21 at 15:23
  • `Range.Find` returns a `Range` object. So `Dim foundCell As Range`, `Set foundCell = range("B:B").Find("the value I look for")`, then `If foundCell Is Nothing Then`.... – BigBen Jan 06 '21 at 15:25
  • You can probably use `Application.CountIf` or `Application.Match` for this too... perhaps easier. – BigBen Jan 06 '21 at 15:26
  • i tried Application.Match, but it had the same 91 error problem, thats why i tried with the find function: ``Dim foundcell As Range Set foundcell = Range("B:B").Application.Match("my value")`` – Juan Camilo Rojas Caldas Jan 06 '21 at 15:39
  • `Dim CurrentMatch As Variant` `CurrentMatch = Application.Match("my value", Range("B:B"), 0)` `If IsNumeric(CurrentMatch) Then` `MsgBox "Value found."` `Else` `MsgBox "Value not found."` `End If` – VBasic2008 Jan 06 '21 at 18:58

0 Answers0