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.

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" wrote in message
...
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.

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

"Mishell" wrote:

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" wrote in message
...
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.

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
---------------
Jacob Skaria


"Ayo" wrote:

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

"Mishell" wrote:

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" wrote in message
...
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.

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



--
jb


"Ayo" wrote:

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.

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


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

"Mishell" wrote:

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" wrote in message
...
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.