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
Posted by Kevin James on April 30, 2001 12:51 PM
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
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
=======
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
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
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
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.
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
