Need help with "Find Method"

[Guest]

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.

 

[Guest]

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 ?

 

[Tom Ogilvy]

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