VBA .Find - not returning the first value in the searched range

[simora]

I'm trying to search the comments in a range for a value, located in cell J2
Somehow, I cant get the code to find the first value which is located in Cell A2.
It's finding all of the other comments.
Need to figure out how to accomplish this.

Thanks



Set M = ActiveSheet.Range("A1:D20")
Set DCell = ActiveSheet.Range("A1")

ActiveSheet.Range("A1").Select

With M

Set c = .Find(Range("J2").Value, LookIn:=xlComments, After:=DCell, SearchDirection:=xlNext)
If Not c Is Nothing Then
FirstAddress = c.Address
'locate first instance
Do
Set c = .FindNext(c)
RsRow = c.Row
v = c.Value
'keep finding new matches until it loops back to the first
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(1, 0).Value = c.Address
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 1).Value = v
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 3).Value = Worksheets("Sheet1").Cells(RsRow, 7).Value
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 4).Value = Worksheets("Sheet1").Cells(RsRow, 8).Value

Loop While Not c Is Nothing And c.Address <> FirstAddress
End If

[p45cal]

I'm trying to search the comments in a range for a value, located in cell J2
Somehow, I cant get the code to find the first value which is located in Cell A2.
I think it's finding it but finding it later than you expect.
If it's the first find, you do nothing with it!
Move the

Set c = .FindNext(c)
from just after Do, to just before Loop While…

However, if you're expecting to find the value in cell A1, this will still come last because your searching after the first cell, so instead of:

Set DCell = ActiveSheet.Range("A1")
have:

Set DCell = M.Cells(M.Cells.Count)


Set M = ActiveSheet.Range("A1:D20")
Set DCell = M.Cells(M.Cells.Count)
ActiveSheet.Range("A1").Select
With M
Set c = .Find(Range("J2").Value, LookIn:=xlComments, After:=DCell, SearchDirection:=xlNext)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
RsRow = c.Row
v = c.Value
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(1, 0).Value = c.Address
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 1).Value = v
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 3).Value = Worksheets("Sheet1").Cells(RsRow, 7).Value
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 4).Value = Worksheets("Sheet1").Cells(RsRow, 8).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

[simora]

Thanks p45cal;

The problem was that the first value had a PERIOD , and was NOT EXACTLY the value I was expecting. 1160 VS 11.60
Appreciate your efforts. It was driving me crazy.

THANKS



I think it's finding it but finding it later than you expect.
If it's the first find, you do nothing with it!
Move the

Set c = .FindNext(c)
from just after Do, to just before Loop While…

However, if you're expecting to find the value in cell A1, this will still come last because your searching after the first cell, so instead of:

Set DCell = ActiveSheet.Range("A1")
have:

Set DCell = M.Cells(M.Cells.Count)


Set M = ActiveSheet.Range("A1:D20")
Set DCell = M.Cells(M.Cells.Count)
ActiveSheet.Range("A1").Select
With M
Set c = .Find(Range("J2").Value, LookIn:=xlComments, After:=DCell, SearchDirection:=xlNext)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
RsRow = c.Row
v = c.Value
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(1, 0).Value = c.Address
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 1).Value = v
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 3).Value = Worksheets("Sheet1").Cells(RsRow, 7).Value
Worksheets("Sheet2").Range("A656").End(xlUp).Offset(, 4).Value = Worksheets("Sheet1").Cells(RsRow, 8).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

[Aflatoon]

Also note that this is wrong:


Loop While Not c Is Nothing And c.Address <> FirstAddress

if c is Nothing then the attempt to read its Address will cause an error. Since you aren't removing the values, you don't need to test if it's nothing, so you just want:


Loop While c.Address <> FirstAddress

[simora]

Aflatoon (http://www.vbaexpress.com/forum/member.php?24778-Aflatoon)

Noted.

Thanks