Need help with "Find Method"
 

I am using the find method to search cells in a predefined Range I have set up.
The problem is that the find method never includes the first row in the
predefined range for example...

Sub Test()
Dim MyRange as range
Dim c as range
Set MyRange = Worksheets("Sheet1").Range("A2:A10")
With MyRange
Set c = .Find(123, LookIn:=xlValues)
If c Not Nothing Then
.Rows(c.Row).Interior.colorindex = 4
end if
End With
End Sub

If the only cell in Sheet1 That has the value 123 in it is A2 then my macro
does not find it ???

Is there a way to include the first cel in MyRange as part of the search ??

Dan.

Need help with "Find Method"
 

Dan here I got some of the syntax wrong on the example code with my post the
sub should look like this

Sub Test()
Dim MyRange As Range
Dim c As Range
Set MyRange = Worksheets("Sheet1").Range("A2:A10")
With MyRange
Set c = .Find(123, LookIn:=xlValues)
If Not c Is Nothing Then
.Rows(c.Row).Interior.ColorIndex = 4
End If
End With
End Sub

also...
If I put value 123 in cell A3 I need to also put (c.row - 1) or else it
highlights the cell right after the cell it found with value 123 not sure why
I need the -1 ?

"Dan Thompson" wrote:

I am using the find method to search cells in a predefined Range I have set up.
The problem is that the find method never includes the first row in the
predefined range for example...

Sub Test()
Dim MyRange as range
Dim c as range
Set MyRange = Worksheets("Sheet1").Range("A2:A10")
With MyRange
Set c = .Find(123, LookIn:=xlValues)
If c Not Nothing Then
.Rows(c.Row).Interior.colorindex = 4
end if
End With
End Sub

If the only cell in Sheet1 That has the value 123 in it is A2 then my macro
does not find it ???

Is there a way to include the first cel in MyRange as part of the search ??

Dan.

Need help with "Find Method"
 

I couldn't reproduce you problem of not finding 123 in cell A2 (make sure
A2 is not formatted as text and 123 is not stored as Text in A2).

I fixed the highlight problem:

Sub TestDan()
Dim MyRange As Range
Dim c As Range
Set MyRange = Worksheets("Sheet1").Range("A2:A10")
With MyRange
Set c = .Find(123, LookIn:=xlValues)
If Not c Is Nothing Then
ActiveSheet.Rows(c.Row).Interior.ColorIndex = 4
End If
End With
End Sub

--
Regards,
Tom Ogilvy

"Dan Thompson" wrote in message
...
Dan here I got some of the syntax wrong on the example code with my post
the
sub should look like this

Sub Test()
Dim MyRange As Range
Dim c As Range
Set MyRange = Worksheets("Sheet1").Range("A2:A10")
With MyRange
Set c = .Find(123, LookIn:=xlValues)
If Not c Is Nothing Then
.Rows(c.Row).Interior.ColorIndex = 4
End If
End With
End Sub

also...
If I put value 123 in cell A3 I need to also put (c.row - 1) or else it
highlights the cell right after the cell it found with value 123 not sure
why
I need the -1 ?

"Dan Thompson" wrote:

I am using the find method to search cells in a predefined Range I have
set up.
The problem is that the find method never includes the first row in the
predefined range for example...

Sub Test()
Dim MyRange as range
Dim c as range
Set MyRange = Worksheets("Sheet1").Range("A2:A10")
With MyRange
Set c = .Find(123, LookIn:=xlValues)
If c Not Nothing Then
.Rows(c.Row).Interior.colorindex = 4
end if
End With
End Sub

If the only cell in Sheet1 That has the value 123 in it is A2 then my
macro
does not find it ???

Is there a way to include the first cel in MyRange as part of the search
??

Dan.