Solved check to see if a name exist in a column

July 21, 2013 at 11:27:37
Specs: Windows 7
Hi Guys,
I am after a bit of VBA cade that will check a column to see if a name exists in a column

Sheet 1, Column A has a list of entered names
Sheet 2, Range B2 is where a name is entered

I need a bit of coding that will determine whether the name entered in Sh2,B2 exists in Sh1, Col A

I will be adding this to an existing bit of VBA and should hopefully be able to manipulate any response I get

Look forward to any helpful response

See More: check to see if a name exist in a column

Report •

July 22, 2013 at 07:55:13
✔ Best Answer
The following site contains information related to the .Find method in VBA.

The code used in the first example used to be included in the VBA Help files, but it was removed when they released 2007. I copy it into my code whenever I need to use the .Find method and then edit it to fit my current needs.

Note: The snippet includes the .FindNext method which allows it to loop through the Range, finding every occurrence of the what parameter. Obviously, if you are doing a single search, you won't need the code within the If-Then, but it's very handy to keep this snippet in your back pocket for future reference.

Your If-Then section will simply be whatever you want the code to do if the name is found.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 22, 2013 at 12:34:18
Sub enternewdetail()
' enters new detail to the legend, clears info added and sorts list

    If MsgBox("HAVE YOU ENTERED THE CORRECT DETAILS?", vbYesNo) = vbYes Then
   With Sheets("LEGEND").Range("A4:A28")
     Set d = .Find(Sheets("NGCCARD").Range("C4"))
      If d Is Nothing Then
'Determine last row with data in Column A
    lastDataRow = Sheets("LEGEND").Range("A" & Rows.Count).End(xlUp).Row

' Detail is Copied and Pasted into the next blank cell in column A
        With Sheets("LEGEND").Range("A" & lastDataRow + 1)
                .PasteSpecial Paste:=xlPasteValues
                .PasteSpecial Paste:=xlPasteFormats
   End With
     Application.CutCopyMode = False
' sorts scorecard list into order
    ActiveWorkbook.Worksheets("LEGEND").Sort.SortFields.Add Key:=Range("A4:A28") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("LEGEND").Sort
        .SetRange Range("A4:AN28")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
' clear data entered to give clean page for next entry
        MsgBox "ScoreCard Information already Exists. Please Try Again", vbCritical Or vbOKOnly
    End If
    End With
    End If

End Sub

Hi there, thanks for responding
The above is what I am working with, but I am missing something??????
As a test, I input a name that I know exists, and the info is still pasted, instead of the msgbox appearing that the name already exists
If the name is new, the paste takes place as required

Have I got some of the code in the wrong position or have I mis-interpretted what I should be doing

Report •

July 22, 2013 at 21:14:48
It works fine for me.

I put A - F in Legends!A4:A9

I put C on NGCCARD!C4.

The code presented the message box.

I put Q in NGCCARD!C4

The code Copied/Pasted NGCCARD!G39:AT39

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

July 23, 2013 at 01:09:16
Thanks for trying the code out.
The bits of code I didn't send were the password protection and unprotect code.
I had positioned these incorrectly.
Likewise the code works fine

Thanks very much fore you help

Report •

July 23, 2013 at 05:16:08
Do you use the debugging techniques listed in this How To?

It's amazing hw fast you can find problems when you watch your code execute one instruction at a time, while using the Watch window to monitor your variables.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

July 24, 2013 at 13:06:17
No I haven't. I've just been stepping into and then F8
Thanks for the heads up with this, and look out for my next thread

Report •

Ask Question