Cells.find works but found info is not copied

[nicosdj]

Hi


I'm making a script that first searches for a person in a sheet and then copies some information about the person to another sheet.

Some of the people are listed, on the sheet that is searched, three times, which is why I search for information about the persons three times. If the person is listed more than once I want to copy all of the information from different cells to only one cell on the destination sheet (the "Forhandlingsenhed U+H sorteret" sheet).

Now, the first search works fine and the information is copied. But it seems that the script does the two other searches and then "forgets" to copy the information found in the second and third search. Why is that?
I don't understand why the string "reason" only contains information from the first search when it is stored.

My code is as follows:
[vba]
Sub indsaet_begrundelser_og_moduler()
'
' indsaet_begrundelser_og_moduler Makro
'
Dim search1 As range
Dim search2 As range
Dim search3 As range
Dim int_person As Integer
Dim name As String
Dim reason As String

'Turns off screen updating so that the Macro runs faster
Application.ScreenUpdating = False
'Ensures that the statusbar is viewable
Application.DisplayStatusBar = True
Application.StatusBar = _
"Begrundelserne kopieres... For at få alle begrundelserne med søges der efter begrundelser for hver person tre gange..."


int_person = 2

Do
Sheets("Forhandlingsenhed U+H sorteret").Select
name = range("A" & int_person) & " " & range("B" & int_person)

Sheets("Begrundelser").Select

'First search
Set search1 = Cells.Find(What:=name, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If search1 Is Nothing Then 'Or Not Err.Number = 0
reason = "Ingen begrundelse"

GoTo NextPerson:

Else
reason = _
Sheets("Begrundelser").range("G" & search1.Row).Value

MsgBox "Reason one = " & reason

End If

'Second search
Set search2 = Cells.FindNext(After:=ActiveCell)

If search2.Address = search1.Address Then

GoTo NextPerson:

Else
'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = "Begrundelse et: " & _
Sheets("Begrundelser").range("G" & search1.Row).Value & vbNewLine & "Begrundelse to: " & _
Sheets("Begrundelser").range("G" & search2.Row).Value

MsgBox "Reason two = " & reason
End If

'Third search
Set search3 = Cells.FindNext(After:=ActiveCell)

If search3.Address = search1.Address Or search3.Address = search2.Address Then

GoTo NextPerson:

Else
'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = _
"Begrundelse et: " & Sheets("Begrundelser").range("G" & search1.Row).Value & vbNewLine & _
"Begrundelse to: " & Sheets("Begrundelser").range("G" & search2.Row).Value & vbNewLine & _
"Begrundelse tre: " & Sheets("Begrundelser").range("G" & search3.Row).Value

MsgBox "Reason three = " & reason
End If

NextPerson:
Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value = reason
int_person = int_person + 1

Loop Until int_person = Sheets("Forhandlingsenhed U+H sorteret").UsedRange.Rows.Count + 1

'Gives Excel control of the statusbar again
Application.StatusBar = False

'Turns screen updating back on
Application.ScreenUpdating = True
Sheets("Forhandlingsenhed U+H sorteret").Select
End Sub
[/vba]

The script does not show the messageboxes with reason two or three, but it shows the messagebox containing reason one.

Now, I hope you can figure it out because I can't see what I'm doing wrong.

[p45cal]

At first glance, try the following (I think it's because you never change the location of the active cell and you're always beginning the search after the active cell so you're always finding the same instance .. I think):
Add the first line below to the code, as well as replacing the commented-out code with the subsequent line
[vba] Set search1 = Nothing: Set search2 = Nothing: Set search3 = Nothing
'First search
' Set search1 = Cells.Find(What:=name, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
Set search1 = Cells.Find(What:=name, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
[/vba]and[vba] 'Second search
' Set search2 = Cells.FindNext(After:=ActiveCell)
Set search2 = Cells.FindNext(search1)
[/vba]and[vba] 'Third search
' Set search3 = Cells.FindNext(After:=ActiveCell)
Set search3 = Cells.FindNext(search2)
[/vba]

[Bob Phillips]

[vba]

Sub indsaet_begrundelser_og_moduler()
'
' indsaet_begrundelser_og_moduler Makro
'
Dim search1 As Range
Dim search2 As Range
Dim search3 As Range
Dim int_person As Integer
Dim name As String
Dim reason As String

'Turns off screen updating so that the Macro runs faster
Application.ScreenUpdating = False
'Ensures that the statusbar is viewable
Application.DisplayStatusBar = True
Application.StatusBar = _
"Begrundelserne kopieres... For at få alle begrundelserne med søges der efter begrundelser for hver person tre gange..."

int_person = 2

Do
With Sheets("Forhandlingsenhed U+H sorteret")

name = .Range("A" & int_person) & " " & .Range("B" & int_person)
End With

With Sheets("Begrundelser")

'First search
Set search1 = .Cells.Find(What:=name, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If search1 Is Nothing Then 'Or Not Err.Number = 0
reason = "Ingen begrundelse - pass #1"

Else
reason = Sheets("Begrundelser").Range("G" & search1.Row).Value
MsgBox "Reason one = " & reason

'Second search
Set search2 = .Cells.FindNext(After:=search1)
If Not search2 Is Nothing Then

If search2.Address <> search1.Address Then

'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = "Begrundelse et: " & .Range("G" & search1.Row).Value & vbNewLine & _
"Begrundelse to: " & _
.Range("G" & search2.Row).Value
MsgBox "Reason two = " & reason
End If
End If

'Third search
Set search3 = .Cells.FindNext(After:=search2)
If Not search3 Is Nothing Then

If search3.Address <> search1.Address And search3.Address <> search2.Address Then

'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = "Begrundelse et: " & .Range("G" & search1.Row).Value & vbNewLine & _
"Begrundelse to: " & .Range("G" & search2.Row).Value & vbNewLine & _
"Begrundelse tre: " & .Range("G" & search3.Row).Value
MsgBox "Reason three = " & reason
End If
End If
End If
End With

Sheets("Forhandlingsenhed U+H sorteret").Range("Z" & int_person).Value = reason
int_person = int_person + 1
Loop Until int_person = Sheets("Forhandlingsenhed U+H sorteret").UsedRange.Rows.Count + 1

'Gives Excel control of the statusbar again
Application.StatusBar = False

'Turns screen updating back on
Application.ScreenUpdating = True
Sheets("Forhandlingsenhed U+H sorteret").Select
End Sub
[/vba]

[nicosdj]

Thank you very much both of you!

Your suggestion, p45cal, worked for the first and the second search but not for the third.

Your suggestion xld worked with all three searches but I cannot say that I understand all the code and the logic behind it.

I will return tomorrow where I hope you will be so kind as to answer my questions.

Thanks again!

[nicosdj]

Okay so to start out from the top. You use a with clause because I apply two ranges to the name variable? Is that really necessary or is it just the proper way to do it? The reason that I'm asking is that I have never done that.
Seeing that I have never used a with clause before I'm also a little puzzled that you use the [vba] With Sheets("Begrundelser") [/vba]
Does the code run faster this way?

The way you have structured the code (without a label) it will do the third search even though it is not necessary if (search1.Address = search2.Address). So is it possible to jump out of the [vba]With Sheets("Begrundelser")[/vba] if search1.Address = search2.Address and just continue with the rest of the loop?

I could of course structure it this way instead:
[vba] If search1 Is Nothing Then 'Or Not Err.Number = 0
reason = "Ingen begrundelse"

Else
reason = Sheets("Begrundelser").range("G" & search1.Row).Value
'MsgBox "Reason one = " & reason

'Second search
Set search2 = .Cells.FindNext(After:=search1)
If Not search2 Is Nothing Then

If search2.Address <> search1.Address Then

'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = "Begrundelse et: " & .range("G" & search1.Row).Value & vbNewLine & _
"Begrundelse to: " & _
.range("G" & search2.Row).Value
'MsgBox "Reason two = " & reason
'Third search
Set search3 = .Cells.FindNext(After:=search2)
If Not search3 Is Nothing Then
If search3.Address <> search1.Address And search3.Address <> search2.Address Then
'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = "Begrundelse et: " & .range("G" & search1.Row).Value & vbNewLine & _
"Begrundelse to: " & .range("G" & search2.Row).Value & vbNewLine & _
"Begrundelse tre: " & .range("G" & search3.Row).Value
'MsgBox "Reason three = " & reason
End If
End If
End If
End If
End If[/vba]
but is it possible to exit a with clause as in Exit Loop/Exit Sub?

[Bob Phillips]

Okay so to start out from the top. You use a with clause because I apply two ranges to the name variable? Is that really necessary or is it just the proper way to do it? The reason that I'm asking is that I have never done that.
Seeing that I have never used a with clause before I'm also a little puzzled that you use the [vba] With Sheets("Begrundelser") [/vba]
Does the code run faster this way?

It is a better way, because it is more readable and it focuses on the actual objects rather than all of its parent hierarchy.

It is a more efficient way, because VBA only has to look up the location of that object once, and retains the pointer until it is released by the code.

The way you have structured the code (without a label) it will do the third search even though it is not necessary if (search1.Address = search2.Address). So is it possible to jump out of the [vba]With Sheets("Begrundelser")[/vba] if search1.Address = search2.Address and just continue with the rest of the loop?

No it won't, because I test for success each time and carry on only if found, otherwise it drops out. Try it with just one value, stepping through.

but is it possible to exit a with clause as in Exit Loop/Exit Sub?

No it is not, but it is not necessary as I explained above.

[nicosdj]

I don't understand why you say it will not do the third search.
The way I see it it checks whether the first search was succesful and then continues to search two if it was. If search two was unsuccesful it skips the rest of the code for search two. But then it does search three and skips the code for search three if search three was unsuccesful.

I've inserted some messageboxes so that my code now looks like this:
[VBA] With Sheets("Begrundelser")

'First search
Set search1 = .Cells.Find(What:=name, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
MsgBox "Første søgning er overstået..."
If search1 Is Nothing Then 'Or Not Err.Number = 0
reason = "Ingen begrundelse"

Else
reason = Sheets("Begrundelser").range("G" & search1.Row).Value
'MsgBox "Reason one = " & reason

'Second search
Set search2 = .Cells.FindNext(After:=search1)
MsgBox "Second search is done..."
If Not search2 Is Nothing Then

If search2.Address <> search1.Address Then

'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = "Begrundelse et: " & .range("G" & search1.Row).Value & vbNewLine & _
"Begrundelse to: " & _
.range("G" & search2.Row).Value
'MsgBox "Reason two = " & reason
End If
End If

'Third search
Set search3 = .Cells.FindNext(After:=search2)
MsgBox "Third search is done..."
If Not search3 Is Nothing Then

If search3.Address <> search1.Address And search3.Address <> search2.Address Then

'Sheets("Forhandlingsenhed U+H sorteret").range("Z" & int_person).Value
reason = "Begrundelse et: " & .range("G" & search1.Row).Value & vbNewLine & _
"Begrundelse to: " & .range("G" & search2.Row).Value & vbNewLine & _
"Begrundelse tre: " & .range("G" & search3.Row).Value
'MsgBox "Reason three = " & reason
End If
End If
End If
End With
[/VBA]

If search one was succesful both the messageboxes ("Second search is done..." and "Third search is done...") are shown. When the search variables are set doesn't the script do the searches?

So that these lines for instance:
[VBA]Set search3 = .Cells.FindNext(After:=search2)
MsgBox "Third search is done..."[/VBA]
would mean that the script first searches for the name again searching the range search2 lastly and then it shows the messagebox?
So doesn't it do searches two and three and then check if they were succesful?

I'm sorry if I'm being annoying - I'm just trying to understand it which I simply don't

[p45cal]

In Excel's Help on FindNext there's a code snippet which I've adapted below. It stops there having to be three separate blocks of code for each search and allows the number of reasons given to be altered easily.
I've used vblf instead of vbNewline which, at least on my machine, don't show as little boxes in the cell:[vba]Sub indsaet_begrundelser_og_moduler2()
Dim search1 As Range
Dim firstaddress As String
Dim i As Single
Dim int_person As Integer
Dim name As String
Dim reason As String

'Turns off screen updating so that the Macro runs faster
Application.ScreenUpdating = False
'Ensures that the statusbar is viewable
Application.DisplayStatusBar = True
Application.StatusBar = _
"Begrundelserne kopieres... For at få alle begrundelserne med søges der efter begrundelser for hver person tre gange..."

int_person = 2

Do
With Sheets("Forhandlingsenhed U+H sorteret")
name = .Range("A" & int_person) & " " & .Range("B" & int_person)
End With
reason = "": Set search1 = Nothing
With Sheets("Begrundelser").Cells
Set search1 = .Find(name, LookIn:=xlValues)
If search1 Is Nothing Then
reason = "Ingen begrundelse - pass #1"
Else
firstAddress = search1.Address
Do
i = i + 1 'successful searches count
reason = reason & IIf(i > 1, vbLf, "") & "Begrundelse " & Choose(i, "et: ", "to: ", "tre: ", "fire: ", "fem: ", "seks: ", "syv: ") & Sheets("Begrundelser").Range("G" & search1.Row).Value
MsgBox reason
Set search1 = .FindNext(search1)
Loop While Not search1 Is Nothing And search1.Address <> firstAddress And i < 3 'can change this if you want more or fewer reasons
If i = 1 Then reason = Replace(reason, "Begrundelse et: ", "") 'only to mimic original code, it can be left out.
i = 0
End If
End With

Sheets("Forhandlingsenhed U+H sorteret").Range("Z" & int_person).Value = reason
int_person = int_person + 1
Loop Until int_person = Sheets("Forhandlingsenhed U+H sorteret").UsedRange.Rows.Count + 1

'Gives Excel control of the statusbar again
Application.StatusBar = False

'Turns screen updating back on
Application.ScreenUpdating = True
Sheets("Forhandlingsenhed U+H sorteret").Select
End Sub
[/vba]

[nicosdj]

It works perfect - thank you so much!

Why didn't I think of that?!