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!

 

[Rick Rothstein]

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

What exactly do you mean by "exact match"? If it is what you posted (not finding Q19r1 in Q19r10, then PCG's code (and I think CircledChicken's code as well) should work fine (the key is the xlWhole argument which makes VB look at the whole cell's content, not just part of it). Or by "exact" did you mean to take letter casing into account (your examples did not show this)? Or maybe you meant something else entirely?

You could always replace the fixed text with a cell reference and then just put what you want to find in that cell before running the code.

 

[jjsauer]

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.

Circledchicken, thanks, I didn't understand. I tried to integrate it, but am getting a "Type Mismatch" error now. For this row:
Set c = .Find(rCell, c, xlValues, xlWhole)

rCell is from a different sheet ("PB-Base")...would that matter?

Oh, and to later offset, will I need to do something like this?:
f.Offset(0, 12).Value = c.(.rows.count+3,column.count+4).Value

 

[circledchicken]

Circledchicken, thanks, I didn't understand. I tried to integrate it, but am getting a "Type Mismatch" error now. For this row:
Set c = .Find(rCell, c, xlValues, xlWhole)

rCell is from a different sheet ("PB-Base")...would that matter?

Oh, and to later offset, will I need to do something like this?:
f.Offset(0, 12).Value = c.(.rows.count+3,column.count+4).Value

Can you post more of your code please - its not easy to tell from the snippet provided? Some more sample data and expected output might also be good.

 

[jjsauer]

What exactly do you mean by "exact match"? If it is what you posted (not finding Q19r1 in Q19r10, then PCG's code (and I think CircledChicken's code as well) should work fine (the key is the xlWhole argument which makes VB look at the whole cell's content, not just part of it). Or by "exact" did you mean to take letter casing into account (your examples did not show this)? Or maybe you meant something else entirely?


You could always replace the fixed text with a cell reference and then just put what you want to find in that cell before running the code.

I did not mean letter casing, sorry for not being specific. In some research I came across the xlWhole argument, but was having trouble integrating it into my code. I wasn't sure if that was wrong or something else, so I posted as general as possible. If xlWhole looks at the whole cell's content will other text mess it up? For example, the cells have lots of other text, such as the actual question for Q19r1, not just the "Q19r1" itself. The rCell.value would just be "Q19r1" though.

Thanks for your help, everyone, although I am not a total rookie, the slightest mistake just locks my up.

 

[jjsauer]

Full Code. The last WITH statement hasn't been touched in a while, but it will be almost the same as the one above it. It is weird that I am self-conscience about my code with all these pros looking at it?

Sub PB_CT_Data_Input()
Application.ScreenUpdating = False
Dim rCell, c, Range1a, Range1b As Range
Dim i As Long
Windows("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm").Activate
Sheets("PB-Base").Select
Set Range1a = Range("PB_Start")
Set Range1b = Range("PB_End")


For Each rCell In Range(Range1a, Range1b)
With Workbooks("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm")
Set cell = rCell
mySheet = cell.Address(External:=False)
End With
Set f = Workbooks("FY12-Q3 Value Tracker Key Measure Summary Tables FINAL.xlsm").Sheets("PB Awareness").Range(mySheet)
If rCell = "" Then
Else
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(rCell, c, xlValues, xlWhole)
Next i
End With
f.Offset(0, 12).Value = c.Offset(3, 4).Value
With Workbooks("FY12-Q3 Data Tables.xlsx").Sheets("PBA Crosstabs").Columns(1)
Set c = .Find(rCell, LookIn:=xlValues, LookAt:=xlWhol)
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
rCell.Offset(0, 14).Value = c.Offset(4, 4).Value
End If
Next

End Sub

 

[circledchicken]

I did not mean letter casing, sorry for not being specific. In some research I came across the xlWhole argument, but was having trouble integrating it into my code. I wasn't sure if that was wrong or something else, so I posted as general as possible. If xlWhole looks at the whole cell's content will other text mess it up? For example, the cells have lots of other text, such as the actual question for Q19r1, not just the "Q19r1" itself. The rCell.value would just be "Q19r1" though.

That will be a problem, because you then have to use a partial match and it would pick up the ones you don't want e.g. Q19r10 etc.

Can you seperate the question identifier from the text? That would be a much better structure? If not, I think we would need to see a sample of your questions? Is there a consistent delimiter between the question number and the rest of the text?

I think the easiest way to get help would be - design a small sample without all your sheet names etc, just with some generic data. Post the sample here along with what you expect the macro to do with the sample, the outcomes etc. It will then be much easier for someone to give you a solution that you can replicate on your actual dataset.

 

[Rick Rothstein]

That will be a problem, because you then have to use a partial match and it would pick up the ones you don't want e.g. Q19r10 etc.

I don't have the time right now to work out a solution for this question, but I believe this function in this mini-blog article of mine would be useful...

InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word

 

[jjsauer]

Thanks for your help Rick, I will check it out!

 

[jjsauer]

I think the easiest way to get help would be - design a small sample without all your sheet names etc, just with some generic data. Post the sample here along with what you expect the macro to do with the sample, the outcomes etc. It will then be much easier for someone to give you a solution that you can replicate on your actual dataset.

...how do I post a spreadsheet? The FAQ wasn't helping.

 

[jjsauer]

Circlechicken, Rick

I was able to utilize both of your comments to get everything working.

Thank you very much!