Limit occurrences using VBA

  • Thread starter Thread starter jamasm2010
  • Start date Start date
J

jamasm2010

Hi,
I have a list of numbers in column A that are grouped together. What I needto do is count the number of each numeric occurrence and alert the user ifthe number is greater than 2. So if I have the number 5 show up 3 times. The user will be notified that there is a problem. I believe the code would be similar to:

If Application.Countif(ColumnA:A, rowname) > 2 then
MsgBox "Problem on line" & rowname & "."
End if

Thanks,
James
 
hi,

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.CountIf(Range("A:A"), Target.Value) > 2 Then
MsgBox "Problem on line " & Target.Row & "."
End If
End Sub

isabelle

Le 2014-04-14 16:01, (e-mail address removed) a écrit :
Hi,
I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence
Click to expand...
and alert the user if the number is greater than 2. So if I have the number 5
show up 3 times. The user will be notified that there is a problem. I believe
the code would be similar to:
 
Hi,

I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times.The user will be notified that there is a problem. I believe the code would be similar to:



If Application.Countif(ColumnA:A, rowname) > 2 then

MsgBox "Problem on line" & rowname & "."

End if



Thanks,

James
Click to expand...

Thanks, Isabelle. It worked like a charm!
 
Hi,

I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times.The user will be notified that there is a problem. I believe the code would be similar to:



If Application.Countif(ColumnA:A, rowname) > 2 then

MsgBox "Problem on line" & rowname & "."

End if



Thanks,

James
Click to expand...

I've been told that using a change event is not the preferred method of doing this. Is there a way to run it another way, such as by a command button?
Thanks,
James
 
it is possible but it involves doing a click on the button to confirm each entry,
but you could also limited the event to a range of cells
order to avoid a recursive method you can add:
Application.EnableEvents = False
resets to "True" after

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Set isect = Application.Intersect(Range("A1:A1000"), Target)
If Not isect Is Nothing Then
If Application.CountIf(Range("A:A"), Target.Value) > 2 Then
MsgBox "Problem on line " & Target.Row & "."
End If
End If
Application.EnableEvents = True
End Sub

isabelle

Le 2014-04-14 19:20, (e-mail address removed) a écrit :
 
Hi,

I have a list of numbers in column A that are grouped together. What I need to do is count the number of each numeric occurrence and alert the user if the number is greater than 2. So if I have the number 5 show up 3 times.The user will be notified that there is a problem. I believe the code would be similar to:



If Application.Countif(ColumnA:A, rowname) > 2 then

MsgBox "Problem on line" & rowname & "."

End if



Thanks,

James
Click to expand...

Thank you, Isabelle. I really appreciate your help. I'm not sure that will make everyone happy, but it really close. I may have to use a loop and counter, and run it from a button.
James
 
Garry, this is a brilliant idea,

Not sure about that assessment, but it's how I often hint erroneous
input OR next required input for progressive data entry. The latter
shows where the next input is required after current input.
isabelle

Le 2014-04-14 20:49, GS a écrit :
Click to expand...


Nice to see you back here! I've missed your contributions...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top