VBA Find keeps finding 7th occurrence of string, not first - Why?

[ransomedbyfire]

I have a sheet populated with VLookups that has been resorted alphabetically on column K.

I have written some code to try to find the first occurrence of the string "LB" in column K of this sheet. But, instead, the code keeps finding the seventh occurrence of this string, ignoring the first six, and thus defeating the purpose of my search. What's going wrong here, and how can I fix it?

Sub rearrangeissues()

firstlb = Sheet3.Range("K:K").Find(what:="LB", after:=[k1], MatchCase:=True, LookIn:=xlValues).Row 'This line returns the number 25 when it should return 19.
Cells(firstlb, 1).Select

Call Sheet6.alphasort1

End Sub

Thanks!

 

[sykes]

Just a couple of thoughts........

1. Is it searching bottom to top, by any chance? It may be worth specifying the XLSearchDirection to 1, in the arguments - just to be sure.
2. You've specified that the Find is case sensitive - are all your "LBs" in the search range in upper case?

 

[ransomedbyfire]

Thanks. But, yeah, all my LB's are uppercase. And, even if it were searching the wrong way, it's finding not the first LB and not the last but one in the middle.

Out of curiosity, I ran the macro recorder and did this manually. The recorder's code added "Cells" to make this code:

FirstLB = Cells.Find(what:="LB", after:=[k12]).Row

It is doing a little better, but now it's getting snagged on trying to find "LV" in cell K262. Instead, it is pointing to "LG" in cell 80. So, once again, maybe I will pull out the good ol' macro recorder and hope for the best.

 

[sykes]

Mmmm - it was always a bit of a long-shot, but with the post at the top of the pile again, hopefully someone will step in and help. There WILL be a reason for this, and one or more of the Top Folk on this forum will know the best way to fix it.....

Good luck.!!

 

[Michael M]

I've made a couple of changes and it selects the first occurance and only checks the used cells not the whole column

Sub rearrangeissues()
Dim lr As Long
lr = Cells(Rows.Count, "K").End(xlUp).Row
firstlb = Sheet1.Range("K1:K" & lr).Find(what:="LB", after:=[k1], MatchCase:=True, LookIn:=xlValues).Row 'This line returns the number 25 when it should return 19.
Cells(firstlb, 11).Select
End Sub

 

[ransomedbyfire]

Thanks Michael (and the rest of you!). I think I got it before you posted; but I appreciate your help!

By the way, how do I go about marking a thread here as solved?

 

[Michael M]

You don't have to mark threads as solved here.
A simple thank you response, lets others know that it has been done.

 

[ransomedbyfire]

Okay. I suppose I've been looking at too many Excel boards these days (via Google)... It must be Ozgrid that marks threads as solved.

Thanks again to all of you!