Check if a value is in an array

[nicosdj]

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?

[nicosdj]

For some reason the rest of my code is not displayed.

Here it is:
Loop Until counter = LastRow + 1

End Sub

[Bob Phillips]

For some reason the rest of my code is not displayed.

Here it is:
Loop Until counter = LastRow + 1

End Sub

It is displayed, you need to use the scrolbar at the side.

[nicosdj]

Aaah... Thanks for the tip :) (DOH!)

[nicosdj]

Wow... Has nobody got any suggestions?