nicosdj
02-23-2010, 07:31 AM
Hi
I'm trying to search through a sheet for persons on another sheet. I search for their last names, and then I check, if their first names correspond to the first name of the person, the search has found.
My code is as follows:
Sub find_person()
'
' find_person Makro
'
'
Dim LastRow As Long
Dim avarSplit As Variant
Dim fornavn As String
Dim efternavn As String
Dim counter_navn As Integer
Dim counter As Integer
Dim samme_person As Variant
Dim findes_i_array As Integer
Dim i As Integer
LastRow = Sheets("Oversigt med lønbånd").Cells(Sheets("Oversigt med lønbånd").Rows.Count, 1) _
.End(xlUp).Row
counter = 4
i = 0
Do
ReDim samme_person(i)
findes_i_array = 0
Sheets("Oversigt med lønbånd").Activate
Sheets("Oversigt med lønbånd").Range("A" & counter).Select
avarSplit = split(Range("A" & counter).Value, " ")
efternavn = avarSplit(UBound(avarSplit))
'MsgBox "It does get here 1..."
Sheets("Hjemme").Activate
Set soegning = Cells.Find(What:=efternavn, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not soegning Is Nothing Then
'MsgBox "Så langt så godt..." = Empty
counter_navn = 0
Do
If counter_navn = 0 Then
fornavn = avarSplit(LBound(avarSplit))
Else
fornavn = fornavn & " " & avarSplit(LBound(avarSplit) + counter_navn)
End If
counter_navn = counter_navn + 1
Loop Until counter_navn = UBound(avarSplit)
Cells.Find(What:=efternavn, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
samme_person(i) = ActiveCell.Address
ActiveCell.Offset(0, 1).Activate
If ActiveCell.Value = fornavn Then
'MsgBox "Den kommer herind..."
Sheets("Oversigt med lønbånd").Range("T" & counter).Value = ActiveCell.Offset(0, 4).Value
Sheets("Oversigt med lønbånd").Range("U" & counter).Value = ActiveCell.Offset(0, 5).Value
counter = counter + 1
End If
For array_counter = 0 To i 'UBound(samme_person)
If samme_person(array_counter) = ActiveCell.Address Then
findes_i_array = 1
Exit For
End If
Next 'array_counter
For array_counter = 0 To i
msg = msg & ", " & samme_person(i) & " " & findes_i_array
Next array_counter
Sheets("Oversigt med lønbånd").Range("V4").Value = msg
i = i + 1
ElseIf findes_i_array = 1 Then
MsgBox "It does get here 2..."
Sheets("Oversigt med lønbånd").Range("T" & counter).Value = "Personen blev ikke fundet"
Sheets("Oversigt med lønbånd").Range("U" & counter).Value = "Personen blev ikke fundet"
counter = counter + 1
Else
Sheets("Oversigt med lønbånd").Range("T" & counter).Value = "Personen blev ikke fundet"
Sheets("Oversigt med lønbånd").Range("U" & counter).Value = "Personen blev ikke fundet"
counter = counter + 1
End If
Loop Until counter = LastRow + 1
End Sub
Now, if a person is named Potter, and there are a hundred Potters, I want it to proceed to the next person, when it has searched through all the Potters. Therefore I'm saving every cell address in an array, and then I search through the array to check, if that person has already been found in a previous search.
I created this code to search thourgh the array:
For array_counter = 0 To i 'UBound(samme_person)
If samme_person(array_counter) = ActiveCell.Address Then
findes_i_array = 1
Exit For
End If
Next 'array_counter
But it never sets the variable findes_i_array to one - it remains zero. This means that it starts over and over again searching through the Potters. Now why is that?
I'm trying to search through a sheet for persons on another sheet. I search for their last names, and then I check, if their first names correspond to the first name of the person, the search has found.
My code is as follows:
Sub find_person()
'
' find_person Makro
'
'
Dim LastRow As Long
Dim avarSplit As Variant
Dim fornavn As String
Dim efternavn As String
Dim counter_navn As Integer
Dim counter As Integer
Dim samme_person As Variant
Dim findes_i_array As Integer
Dim i As Integer
LastRow = Sheets("Oversigt med lønbånd").Cells(Sheets("Oversigt med lønbånd").Rows.Count, 1) _
.End(xlUp).Row
counter = 4
i = 0
Do
ReDim samme_person(i)
findes_i_array = 0
Sheets("Oversigt med lønbånd").Activate
Sheets("Oversigt med lønbånd").Range("A" & counter).Select
avarSplit = split(Range("A" & counter).Value, " ")
efternavn = avarSplit(UBound(avarSplit))
'MsgBox "It does get here 1..."
Sheets("Hjemme").Activate
Set soegning = Cells.Find(What:=efternavn, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not soegning Is Nothing Then
'MsgBox "Så langt så godt..." = Empty
counter_navn = 0
Do
If counter_navn = 0 Then
fornavn = avarSplit(LBound(avarSplit))
Else
fornavn = fornavn & " " & avarSplit(LBound(avarSplit) + counter_navn)
End If
counter_navn = counter_navn + 1
Loop Until counter_navn = UBound(avarSplit)
Cells.Find(What:=efternavn, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
samme_person(i) = ActiveCell.Address
ActiveCell.Offset(0, 1).Activate
If ActiveCell.Value = fornavn Then
'MsgBox "Den kommer herind..."
Sheets("Oversigt med lønbånd").Range("T" & counter).Value = ActiveCell.Offset(0, 4).Value
Sheets("Oversigt med lønbånd").Range("U" & counter).Value = ActiveCell.Offset(0, 5).Value
counter = counter + 1
End If
For array_counter = 0 To i 'UBound(samme_person)
If samme_person(array_counter) = ActiveCell.Address Then
findes_i_array = 1
Exit For
End If
Next 'array_counter
For array_counter = 0 To i
msg = msg & ", " & samme_person(i) & " " & findes_i_array
Next array_counter
Sheets("Oversigt med lønbånd").Range("V4").Value = msg
i = i + 1
ElseIf findes_i_array = 1 Then
MsgBox "It does get here 2..."
Sheets("Oversigt med lønbånd").Range("T" & counter).Value = "Personen blev ikke fundet"
Sheets("Oversigt med lønbånd").Range("U" & counter).Value = "Personen blev ikke fundet"
counter = counter + 1
Else
Sheets("Oversigt med lønbånd").Range("T" & counter).Value = "Personen blev ikke fundet"
Sheets("Oversigt med lønbånd").Range("U" & counter).Value = "Personen blev ikke fundet"
counter = counter + 1
End If
Loop Until counter = LastRow + 1
End Sub
Now, if a person is named Potter, and there are a hundred Potters, I want it to proceed to the next person, when it has searched through all the Potters. Therefore I'm saving every cell address in an array, and then I search through the array to check, if that person has already been found in a previous search.
I created this code to search thourgh the array:
For array_counter = 0 To i 'UBound(samme_person)
If samme_person(array_counter) = ActiveCell.Address Then
findes_i_array = 1
Exit For
End If
Next 'array_counter
But it never sets the variable findes_i_array to one - it remains zero. This means that it starts over and over again searching through the Potters. Now why is that?