need Macro code for validation of account number

[tmprao27]

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.

[Fettertiger]

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

[jraj1106]

Hi

Try this:

tmprao27.xlsm

[tmprao27]

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