checking an entry exists in a list

[andyd74]

i want to write some VBA code that checks an entry in a form against a list of previous entries. If this entry is already in the list, i want a text box saying that. If there is no entries in the list that are the same, then the form entry should be put at the bottom of the list.

I have tried to get it working but not quite there... my code is below....

Thanks

Else

blnvalidCode2 = False
blnvalidCode = False

For j = 5 To 500

If staff_name = Worksheets("Project Codes List").Cells(j, 6).Value Then

For i = 5 To 500
blnvalidCode2 = True
If proj_code = Worksheets("Project Codes List").Cells(i, 4).Value Then
Call run_search
blnvalidCode = True

Exit For
End If

Next i

If blnvalidCode = False Then
MsgBox "Please enter a valid Project Code", vbExclamation + vbOKOnly, "Invalid Project Code Error"
End If

Exit For
End If

Next j

If blnvalidCode2 = False Then
MsgBox "Please enter a valid team member's name!", vbExclamation + vbOKOnly, "Invalid Project Code Error"
End If

End If

 

[firefytr]

Hi there,

Not really sure what your code is supposed to be doing with all those loops, but you could append it with a few lines like so ...

Sub YourTestSubName()
    Dim rngFind As Range, rngLook As Range
    Dim Staff_Name As String
    Dim blnValidCode As Boolean, blnValidCode2 As Boolean
    If YourTestHere Then
        'something..
    Else
        blnValidCode2 = False
        blnValidCode = False
        Set rngLook = Worksheets("Project Codes List").Range("F:F") 'set to desired
        For j = 5 To 500
            If Staff_Name = Worksheets("Project Codes List").Cells(j, 6).Value Then
                For i = 5 To 500
                    blnValidCode2 = True
                    If proj_code = Worksheets("Project Codes List").Cells(i, 4).Value Then
                    
                        Set rngFind = rngLook.Find("find value", MatchCase:=True)
                        If rngFind Is Nothing Then
                            'enter value here, not found
                        Else
                            MsgBox "Value already exists!", vbInformation, "ERROR!"
                        End If
                        
                        blnValidCode = True
                        Set rngFind = Nothing
                        Exit For
                    End If
                Next i
                If blnValidCode = False Then
                    MsgBox "Please enter a valid Project Code", vbExclamation + vbOKOnly, "Invalid Project Code Error"
                End If
                Exit For
            End If
        Next j
        If blnValidCode2 = False Then
            MsgBox "Please enter a valid team member's name!", vbExclamation + vbOKOnly, "Invalid Project Code Error"
        End If
    End If
End Sub

Does this get you what you need?

-----------
Regards,
Zack Barresse