Having some trouble with the find function

[Regouin]

I was snooping around the boards to find an answer to this because I can hardly believe that noone else has ever run into this problem before.

Here's the situation:

I have column A which has text every now and then, in column B I "label" the texts which I want found with a certain string of text "this" (i.e.).
Right now I have 4 occurences of "this" in Column B, which starts with 1 occurence at B1. I want the find to return B1 as first search result, but instead it returns the second occurence as the first search result (i.e. B510). Now after browsing through the help files I figured out that excel starts it's search after the cell in the top left corner of the range, and takes the first cell (topleft) as the last (now what kind of logic is that). How on earth can I get it to start with B1 and not with B2.

TIA
frank

[Zack Barresse]

Hello Frank,

Would you mind posting an example file? I'm confused as to whether you are talking about the FIND function, or the Find method (VBA).

[Regouin]

With Worksheets("onderhoud")
i = 1
Do
c = .Range("B" & i & ":B63000").Find("Wasstraat", LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False).row
Set RangeAI = .Range("A" & c)
Me.CBox1.AddItem RangeAI.Text
Me.CBox4.AddItem c
i = c + 1
MsgBox (RangeAI)
Loop Until c = "nothing"
End With



now I have 4 items first located in B1, and it finds only 3 of them, not the one in B1 plus when it has found all three it gives me an error.

TIA
frank

[Jacob Hilderbrand]

Try this macro:


Option Explicit

Sub Macro1()
Dim Cel As Range
Dim c As Long
Dim RangeAI As Range
Dim FirstAddress As String
With Worksheets("onderhoud")
i = 1
Set Cel = .Range("B" & i & ":B63000").Find(What:="Wasstraat", _
LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
c = Cel.Row
Set RangeAI = .Range("A" & c)
Me.CBox1.AddItem RangeAI.Text
Me.CBox4.AddItem c
i = c + 1
MsgBox (RangeAI)
Set Cel = .Range("B" & i & ":B63000").FindNext(Cel)
Loop While Not Cel Is Nothing And Cel.Address <> FirstAddress
End If
End With
End Sub

[Zack Barresse]

Maybe you could use this Frank ...


Option Explicit

Sub MSFindIt()
Dim ws As Worksheet, cel As Range, rng As Range, firstAddy As String
Set ws = Worksheets("onderhoud")
Set rng = ws.Range("B1:B" & ws.Range("B65536").End(xlUp).Row)
With rng
Set cel = .Find("*Wasstraat*", LookIn:=xlValues, _
SearchDirection:=xlNext, LookAt:=xlPart, MatchCase:=True)
If Not cel Is Nothing Then
firstAddy = cel.Address
Do
Me.CBox1.AddItem cel.Offset(, -1).Value
Me.CBox4.AddItem cel.Value
Set cel = .FindNext(cel)
Loop Until cel Is Nothing Or cel.Address = firstAddy
End If
End With
End Sub

You may also want to have a look at Johnske's article on the Find method here: http://www.vbaexpress.com/forum/articles.php?action=viewarticle&artid=5

[Regouin]

ok Jake, for some reason your VBA gives an error on this line


Set Cel = .Range("B" & i & ":B63000").FindNext(Cel)


and fire, yours works beautifully, I only had to change value to row to get the row numbers, the only problem is that it returns the text in row 1 as last. I still find it rather weird that it doesnt start in the first cell of the area but rather in the second cell.

[johnske]

..........I still find it rather weird that it doesnt start in the first cell of the area but rather in the second cell.

Hi Regouin,

Yes, it does that, it selects the first cell and bookmarks it as the start of the search area without actually searching it....

I had a project that required the found items in a row be displayed in the order "first to last". I had to start the search in the cell BEFORE the first cell in the row that I wanted to search, if the items were to appear in the correct order. :rotlaugh: Probably a simpler way to do it, but that method worked out right for that case.

Regards,
John :)


EDIT: I just tried this mod of Zacks code using message boxes to get the cell addresses and it appears to work - give it a try...


Option Explicit

Sub MSFindIt()
Dim ws As Worksheet, cel As Range, rng As Range, firstAddy As String
Set ws = Worksheets("onderhoud")
Set rng = ws.Range("B1:B" & ws.Range("B65536").End(xlUp).Row)
With Range("A1", rng) '< doesn't need to be A1
Set cel = .Find("*Wasstraat*", LookIn:=xlValues, _
SearchDirection:=xlNext, LookAt:=xlPart, MatchCase:=True)
If Not cel Is Nothing Then
firstAddy = cel.Address
Do
Me.CBox1.AddItem cel.Offset(, -1).Value
Me.CBox4.AddItem cel.Value
Set cel = .FindNext(cel)
Loop Until cel Is Nothing Or cel.Address = firstAddy
End If
End With
End Sub

[Regouin]

Yes, after endlessly experimenting with the different parameters i finally got to the following not quite satisfying but working solution.

Since the first thing to be found is in row 1 and the search can only start as early row 1 i decided to move the whole worksheet down one notch, so now the worksheet starts on row 2 and row 1 is just there for finding purposes. Since the sheet is going to be invisible most of the time I dont think it is going to matter that much that row 1 is an empty row. I'll mark the thread solved although I am not quite satisfied with the solution, but that is the way it has to be. I always thought excel was able to do anything as long as it comes to calculations, but I cant see why there should be a stupid glitch like this in such a program :). I'll write a hate note to Microsoft saying I am banning the VBA find command :rotlaugh: .
thanks again everyone for your effort and support.

frank
:beerchug:

[johnske]

Hi, I just modified my earlier post, check it again now :)