Limit occurrences using VBA

[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

 

[isabelle]

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

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:

 

[jamasm2010]

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

Thanks, Isabelle. It worked like a charm!

 

[jamasm2010]

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

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

 

[isabelle]

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 :

 

[jamasm2010]

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

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

 

[GS]

Another way..., use Conditional formatting to 'flag' offending cells so
no VBA is required!

--
Garry

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

 

[isabelle]

Garry, this is a brilliant idea,

isabelle

Le 2014-04-14 20:49, GS a écrit :

 

[GS]

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 :

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

 

[jamasm2010]

Another way..., use Conditional formatting to 'flag' offending cells so

no VBA is required!



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion

Thanks, Garry. I do believe that will work.
James

 

[GS]

Thanks, Garry. I do believe that will work.
James

You're welcome! I appreciate the feedback...

--
Garry

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