VBA Find exact match help and Nth Occurance

[jjsauer]

Hello,

This section of code I am working on requires me to find an exact text (I tried lookat:=xlWhole, but I can't get it to work right). The problem being is when it searches for Q1, Q10 is an acceptable answer ans so on. I need to get the 4th occurrence, but my research into Nth occurrence stuff is confusing, so any consolidation would be appreciated.

The search is in a single column.

With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("PBA Crosstabs").Columns(1)
Set c = .Find(rCell, LookIn:=xlValues, lookat:=xlWhole)
Set c = .Find(rCell, After:=c, lookat:=xlWhole)
Set c = .Find(rCell, After:=c, lookat:=xlWhole)
Set c = .Find(rCell, After:=c, lookat:=xlWhole)
End With

Thanks in advance!

 

[jjsauer]

bump

 

[JoeMo]

What is the text string you are looking for?

 

[jjsauer]

Here is an example of what is in the column:

Q19r1
Q19r2
Q19r9
Q19r10
Q19r8
Q19r5
Q19r6

This one particular data sheet is different than all the rest because we had to run it through a different software program. So I am a little lost. The output repeats the "Q19r8" 4 times before it gets to the one I was trying to use as a reference and if I cannot search for the exact text than the macro will not work since it will capture Q19r10 when it looks for Q19r1

 

[pgc01]

Hi

If you want to find the nth occurrence use .FindNext

Ex. (without processing errors):

Sub Test()
Dim r As Range
 
With Range("A1:A10")
 
    Set r = .Find(What:="abc", After:=Range("A10"), LookIn:=xlValues, LookAt:=xlPart)
    Set r = .FindNext(r)
 
End With
 
MsgBox "Found second occurrence at: " & r.Address

End Sub

 

[circledchicken]

Here is an example of what is in the column:

Q19r1
Q19r2
Q19r9
Q19r10
Q19r8
Q19r5
Q19r6

This one particular data sheet is different than all the rest because we had to run it through a different software program. So I am a little lost. The output repeats the "Q19r8" 4 times before it gets to the one I was trying to use as a reference and if I cannot search for the exact text than the macro will not work since it will capture Q19r10 when it looks for Q19r1

Hi,

Another alternative might be:

Sub example()

Dim i As Long
Dim c As Range

With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("PBA Crosstabs").Columns(1)
    Set c = .Cells(.Rows.Count, .Columns.Count)
    For i = 1 To 4 ' change 4 to the occurrence you want
        Set c = .Find("Q19r8", c, xlValues, xlWhole)
    Next i
End With

End Sub

Using xlWhole should match the whole cell, so partial matches shouldn't be a problem.

 

[Rick Rothstein]

Hi

If you want to find the nth occurrence use .FindNext

Ex. (without processing errors):

Sub Test()
Dim r As Range
 
With Range("A1:A10")
 
Set r = .Find(What:="abc", After:=Range("A10"), LookIn:=xlValues, LookAt:=[COLOR=red][B]xlPart[/B][/COLOR])
Set r = .FindNext(r)
 
End With
 
MsgBox "Found second occurrence at: " & r.Address
 
End Sub

I think you meant xlwhole and not xlPart where shown in red above.

 

[jjsauer]

@pcg01 & Rick Rothstein
Thank you for the assistance with the nth occurrence. I will integrate it when I get the exact match figured out.

@Chicken
I appreciate your response. The only issue is I would have to repeat that code nearly 200 times and one of the major tasks in this whole macro is setting up the macro to allow for some edits (additions/deletions) so I am trying to not used fixed text, but a FOR EACH rCell function.

 

[pgc01]

I think you meant xlwhole and not xlPart where shown in red above.

You're right, Rick. Thanks for pointing it out.

 

[circledchicken]

@pcg01 & Rick Rothstein
Thank you for the assistance with the nth occurrence. I will integrate it when I get the exact match figured out.

@Chicken
I appreciate your response. The only issue is I would have to repeat that code nearly 200 times and one of the major tasks in this whole macro is setting up the macro to allow for some edits (additions/deletions) so I am trying to not used fixed text, but a FOR EACH rCell function.

I don't think you need to repeat it, that was just an example. You can replace the string with your rCell variable and nest it in your For Each loop.