+ Reply to Thread
Results 1 to 4 of 4

need Macro code for validation of account number

  1. #1
    Registered User
    Join Date
    05-17-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    6

    Post need Macro code for validation of account number

    Hi

    I am amateur coder in the field of macro and VB . Need a code for validating a column containing account numbers. scenario is whenever i add new account number into that column it should check with all other accounts whether its new or already exits. if its new then i should get a msg saying validation completed. if its exits i should get error msg. account already exits.

  2. #2
    Forum Contributor
    Join Date
    06-09-2011
    Location
    Germany
    MS-Off Ver
    Excel 2016
    Posts
    193

    Re: need Macro code for validation of account number

    Hi tmprao27,

    I would use the worksheet change event for this task. The following macro needs to be pasted in "thisworkbook" in the Visual basic editor. It will check every new entry in column A (you can adjust this where I indicated it in the code) in the worksheet called "Accounts".

    Please Login or Register  to view this content.
    Regards
    Theo
    Remember To Do the Following....
    1. Upload sample files
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.

  3. #3
    Valued Forum Contributor
    Join Date
    02-09-2012
    Location
    Mauritius
    MS-Off Ver
    Excel 2007
    Posts
    1,055

    Re: need Macro code for validation of account number

    Hi

    Try this:

    tmprao27.xlsm
    Click *, if my suggestion helps you. Have a good day!!

  4. #4
    Registered User
    Join Date
    05-17-2012
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: need Macro code for validation of account number

    Thank you so much guys

    I did something like this. let me make use of your codes too

    my code:

    Private Sub CommandButton1_Click()
    Dim RowCount As Integer
    Dim RowNum As Integer
    Dim ChkVal As String
    Dim ChkCount As Integer


    'Determine how many rows to test
    RowCount = (ActiveSheet.UsedRange.Rows.Count) - 1

    'just to check flow
    MsgBox (RowCount)
    ' is the row in my work book where account number starts
    For RowNum = 4 To RowCount
    '2 is the column number
    ChkVal = ActiveSheet.Cells(RowNum, 2)

    If ChkVal <> "" Then
    ChkCount = Application.WorksheetFunction.CountIf(ActiveSheet.Columns(2), ChkVal)

    Else
    ChkCount = 1
    End If

    If ChkCount > 1 Then

    MsgBox ("Account already exits")
    Exit For
    End If

    Next RowNum

    MsgBox ("Validation completed")

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1