Find second entry for a search

[excelcandy]

This code finds the first entry for what is in Sheet1 A5, but how do I find the second entry for this search, not the first?

Please Login or Register  to view this content.

Thanks guys!

[nilem]

Hi Excelcand,
maybe so

Please Login or Register  to view this content.

[ragulduy]

[CODE]
Sub INeedToSearchForCellValue()
Dim rngFound As Range
Set rngFound = Sheets("Sheet2").Cells.Find(What:=Sheets("Sheet1").Range("A5").Value, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
set rngFound = Findnext(rngFound)
If rngFound Is Nothing Then
MsgBox "No match found. ", , "No Match"
Else
Application.Goto rngFound
End If
End Sub

[excelcandy]

Hi Excelcand,
maybe so

Please Login or Register  to view this content.

This isn't working, error on

Please Login or Register  to view this content.

[excelcandy]

[CODE]
Sub INeedToSearchForCellValue()
Dim rngFound As Range
Set rngFound = Sheets("Sheet2").Cells.Find(What:=Sheets("Sheet1").Range("A5").Value, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
set rngFound = Findnext(rngFound)
If rngFound Is Nothing Then
MsgBox "No match found. ", , "No Match"
Else
Application.Goto rngFound
End If
End Sub

"Sub or function not defined"

[ragulduy]

Sorry yeah, forgot to reference the findnext method to a sheet. Nilem's code works fine for me though and is better than mine anyway.

[nilem]

This isn't working, error on

Please Login or Register  to view this content.

Please show an example of the file where the error occurs

[excelcandy]

Please Login or Register  to view this content.

This goes yellow

[nilem]

I meant to attach a file in which the error occurs
Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[excelcandy]

sorry, can't because of work. what could it be?

[ragulduy]

Is "66B" a named range?

[excelcandy]

Ok,

Please Login or Register  to view this content.

works now. How do you search a certain text instead of Cell A5 ?

thanks


Nilem, pm me your paypal for a small reward

[ragulduy]

Please Login or Register  to view this content.

[nilem]

maybe LookIn:=xlValues instead of LookIn:=xlFormulas?
or just

Please Login or Register  to view this content.

[excelcandy]

How do I find the third and fourth entry?

[excelcandy]

So, if this finds the first occurence and that finds the second time the value occurs,,, what code is needed to find the third and fourth ?????????????????????????????????


First value found:

Please Login or Register  to view this content.

Second value found:

Please Login or Register  to view this content.

[nilem]

try

Please Login or Register  to view this content.

[excelcandy]

Hey, that's cool, unfortunately I don't want to count the occurences, but just select the 3rd occurence ( or 4th or 5th or ... occurence), like in the code before

[ragulduy]

Please Login or Register  to view this content.

change loop until cnt = 3 as appropriate for the one you want to select.

[nilem]

or

Please Login or Register  to view this content.

@yudlugar I hope together we will (shall?) defeat this thread

[excelcandy]

Hey, I just experimented with ma skillz and found out that I just have to double the following line to find the 3rd occurence

Please Login or Register  to view this content.

[excelcandy]

There is a problem with this, guys. If I try to get the occurence of the first, second, third and fourth search, It finds the first occurence every single time if there is only one occurence, not 2,3,4 different ones. I want to return nothing or zero in that case. (otherwise it copies the cell to the right). Pretty confusing, but logical. I want to sum up all results.