Help with .Find Function in code

[Ayo]

I am trying to use the Find in my code but I am getting a "Object variable or
With block variable not set" error message on the code line below.
dataCorrWS.Range("C2:C" & dataCorrWSlastRow).Find(c.Value,
LookIn:=xlValues).Activate

but another variation of the code:
Worksheets("InSite Milestones").Range("C2:C3000").Find("NY09337C",
LookIn:=xlValues).Activate

works fine. Any ideas.

 

[Mishell]

If the searched value was not found, you will get that error message:

Try like this to check that Not result Is Nothing :
Set result = dataCorrWS.Range("C2:C" & dataCorrWSlastRow).Find(c.Value, _
LookIn:=xlValues)

If Not result Is Nothing Then
result.Activate
End If

Mishell

 

[Ayo]

Thanks for the responds but I am still getting the same error message. Is
result a range or a string variable?

 

[Jacob Skaria]

Hi Ayo

Try the below.

Sub Macro()

Dim varRange As Range
Dim varFound As Variant
Dim varSearch As Variant
varSearch = "NY"
Set varRange = ActiveSheet.Range("C:C")

Set varFound = varRange.Find(varSearch)
If Not varFound Is Nothing Then
'Found
MsgBox "Cell is " & varFound.Address
MsgBox "Text found is " & varFound.Text
End If
End Sub

If this post helps click Yes

 

[john]

Have you used Set to assign an object reference to your variable?

something like:

Dim dataCorrWS As WorkSheet

Set dataCorrWS = Worksheets(“Data Corrâ€)

following example finds first instance of search criteria in Col C - change
sheet name as required.

Sub SearchData()

Dim Foundcell As Range
Dim Search As String
Dim InsiteWS As Worksheet

Set InsiteWS = ThisWorkbook.Worksheets("InSite Milestones")


Search = "NY09337C"

Set Foundcell = InsiteWS.Columns(3).Find(Search, _
LookIn:=xlValues, _
LookAt:=xlWhole)


If Foundcell Is Nothing = False Then

msg = MsgBox("Search Value: " & Search & Chr(10) & _
"Found At: " & Foundcell.Address, 64, "Search")

Else

msg = MsgBox("Search Value: " & Search & Chr(10) & _
"Not Found", 16, "Search")

End If

End Sub

 

[Mishell]

The variable Result is not a string variable, but an object as it is defined
with the Keyword SET.( Here it is an object of the type Range).

Mishell