Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

Double Entry

Posted by Adam Warner on April 30, 2001 12:16 PM
Hi, I know nothing about programming macros but need to ensure that no two numbers are the same within one given column (ex. Purchase Orders). How can i ensure this?

Thanks

Adam Warner


Check out our Excel Resources

Re: Double Entry

Posted by Kevin James on April 30, 2001 12:51 PM
15815.html


Re: Double Entry

Posted by Dave Hawley on April 30, 2001 1:11 PM


Hi Adam

As prevention is better than cure, you may like to look at the link "Handy Hints" on my Website.
You will see this can be handled without macros using Data>Validation. However should you require a Macro, just let me know.


Dave

OzGrid Business Applications


Re: Double Entry

Posted by Aladin Akyurek on April 30, 2001 1:48 PM
Suppose that the Purchase Orders are to be entered in A from A2 on. And you already entered a few. Suppose that A4 is currently empty.
Activate A4.
Activate Data|Validation.
On Settings tab, choose Custom for "Allow", and type the following formula for "Formula":

=NOT(ISNUMBER(MATCH(A4,$A$2:A3,0)))

Copy the empty A4 as far as needed.

Start entering the purchase orders from A4 on. You will be warned when you attempt to enter an already entered order.

Aladin

=======


Re: Double Entry

Posted by IML on April 30, 2001 1:57 PM

Adam,
Just a quick caveat to be aware of on data valadation - this does not prevent someone from pasting a duplicate number in. It only works for typed in entries. Hopefully this won't be an issue for you.

Good luck,

Ian


Re: Double Entry

Posted by Dave Hawley on April 30, 2001 2:13 PM


Adam, as Ivan and my Website say "Validation" will not prevent users pasting in duplicates. You can use this code, that will prevent typing and Pasting.

To use it right click on the Sheet name Tab and select "View Code" and Paste in this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Written by OzGrid Business Applications
'www.ozgrid.com

''''''''''''''''''''''''''''''''''''''''''
'Prevents duplicate entries in Column A
''''''''''''''''''''''''''''''''''''''''''

If Target.Cells.Count > 1 Then Exit Sub

If Target.Column = 1 And Target <> "" Then 'Column A
If WorksheetFunction.CountIf(Columns(1), Target) > 1 Then
MsgBox "Number " & Target & " already exists!", _
vbCritical, "OzGrid Business Applications"
Target = ""
Target.Select
End If
End If

End Sub

Change "Column=1" and "Columns(1)" if needed. Then push Alt+Q (to return to Excel). Now Save. try typing a duplicate entry in Column A.

Dave

OzGrid Business Applications


Re: Double Entry

Posted by IML on April 30, 2001 2:54 PM
End Sub

Dave,
I think I probably pirated that pasting tip from your site (in my opinion a must read). Because I know nothing about macros or vba, how would this macro work. Don't read much into this question, I'm talking very basic. Do you run it once, and then it prevents anyone from entering duplicates, or do you have to run it from time to time and it then tests the data? Just curious.

Thanks,
Ian


Re: Double Entry

Posted by : on April 30, 2001 3:07 PM
> I think I probably pirated that... tip from your site.

Can't "pirate" something that was given away.


Re: Double Entry

Posted by Dave Hawley on April 30, 2001 3:26 PM
Adam, as Ivan and my Website say "Validation" will not prevent users pasting in duplicates. You can use this code, that will prevent typing and Pasting. : To use it right click on the Sheet name Tab and select "View Code" and Paste in this code

Hi Ian
>probably pirated that pasting tip from your site.

No problem, I probably acquired it from the MS Excel help :o)

The code will run fully each time any data is typed or pasted in a cell within Column A. That is why the code MUST be placed within the Worksheet module. ie;
right click on the Sheet name Tab and select "View Code"


Dave
OzGrid Business Applications


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.