Dim x As Long, y As Long, z As Long, searchstring As String
If ActiveCell = "" Then Exit Sub
searchstring = "DS45"
x = Range("I65536").End(xlUp).Row
y = Application.WorksheetFunction.CountIf(Range("I:I") , searchstring)
If y 3 Then
MsgBox "This Customer has reached their limit. Only 3 DVD's allowe
per customer!" & vbCrLf & vbCrLf & "Mark Smith - Manager"
vbInformation + vbOKOnly, "Disc Emporium - DVD Limit Check"
ActiveCell = ""
ActiveCell.Offset(, 5) = "On shelf"

I have the above formula which checks the search string for a value o
more than 3 entries, then brings up a message box...But, this work
fine, however, I have other strings that also needed to be checked..Ho
can I get the code to check more than one besides the "DS45"...I hav
20 or so more numbers in my datebase that I would like to check fo
greater than 3...Any help would be great

Thanks
Mar

--
Message posted from http://www.ExcelForum.com

Here's an idea for you
Sub morethan3()
For Each c In Range("k2,L1,m5")
If c 3 Then MsgBox c.Address
Next
End Sub
--
Don Guillett
SalesAid Software

"Mark1ace1 " wrote in message
...
Dim x As Long, y As Long, z As Long, searchstring As String
If ActiveCell = "" Then Exit Sub
searchstring = "DS45"
x = Range("I65536").End(xlUp).Row
y = Application.WorksheetFunction.CountIf(Range("I:I") , searchstring)
If y 3 Then
MsgBox "This Customer has reached their limit. Only 3 DVD's allowed
per customer!" & vbCrLf & vbCrLf & "Mark Smith - Manager",
vbInformation + vbOKOnly, "Disc Emporium - DVD Limit Check"
ActiveCell = ""
ActiveCell.Offset(, 5) = "On shelf"

I have the above formula which checks the search string for a value of
more than 3 entries, then brings up a message box...But, this works
fine, however, I have other strings that also needed to be checked..How
can I get the code to check more than one besides the "DS45"...I have
20 or so more numbers in my datebase that I would like to check for
greater than 3...Any help would be great

Thanks
Mark


---
Message posted from http://www.ExcelForum.com/

thanks for quick reply...But not quite sure where to add that bit o
code :( Not the best using VB..Just been putting snippits togethe
really...

Mar

--
Message posted from http://www.ExcelForum.com

Dim x As Long, y As Long, z As Long, searchstring As String
Dim varr(1 to 3), msg1(1 to 3), msg2(1 to 3)
varr(1) = "DS45"
varr(2) = "AB11"
varr(3) = "RB"
msg = "This Customer has reached their limit. " & _
"Only 3 DVD's allowed per customer!"
msg1(1) = "Mark Smith - Manager"
msg1(2) = "Jimbo Hunt - Employee"
msg1(3) = "Mortimer Snerd - Musician"
msg2(1) = "Disc Emporium - DVD Limit Check"
msg2(2) = "Dicks Glock Shop"
msg2(3) = "Inconsequential Tunes"
If ActiveCell = "" Then Exit Sub
for i = 1 to 3
searchstring = varr(i)
x = Range("I65536").End(xlUp).Row
y = Application.WorksheetFunction.CountIf(Range("I:I") , searchstring)
If y 3 Then
MsgBox msg & vbCrLf & vbCrLf & msg1(i),
vbInformation + vbOKOnly, msg2(i)
ActiveCell = ""
ActiveCell.Offset(, 5) = "On shelf"
End If
Loop

or if your values are in cells

Dim x As Long, y As Long, z As Long, searchstring As String
for each cell in Worksheets("List").Range("A1:A20")
searchstring = cell.Value
y = Application.WorksheetFunction.CountIf(Range("I:I") , searchstring)
If y 3 Then
MsgBox cell.Value & " has reached their limit. Only 3 DVD's allowed
per customer!" & vbCrLf & vbCrLf & "Mark Smith - Manager",
vbInformation + vbOKOnly, "Disc Emporium - DVD Limit Check"
Cell = ""
Cell.Offset(, 5) = "On shelf"
End if
Next

--
Regards,
Tom Ogilvy





"Mark1ace1 " wrote in message
...
Dim x As Long, y As Long, z As Long, searchstring As String
If ActiveCell = "" Then Exit Sub
searchstring = "DS45"
x = Range("I65536").End(xlUp).Row
y = Application.WorksheetFunction.CountIf(Range("I:I") , searchstring)
If y 3 Then
MsgBox "This Customer has reached their limit. Only 3 DVD's allowed
per customer!" & vbCrLf & vbCrLf & "Mark Smith - Manager",
vbInformation + vbOKOnly, "Disc Emporium - DVD Limit Check"
ActiveCell = ""
ActiveCell.Offset(, 5) = "On shelf"

I have the above formula which checks the search string for a value of
more than 3 entries, then brings up a message box...But, this works
fine, however, I have other strings that also needed to be checked..How
can I get the code to check more than one besides the "DS45"...I have
20 or so more numbers in my datebase that I would like to check for
greater than 3...Any help would be great

Thanks
Mark


---
Message posted from http://www.ExcelForum.com/

It is the second code I want..I have tried it but it only allow
customers to have one and message box does not kill itself afte
clicking ok...

Mark

Thanks for the help ....:

--
Message posted from http://www.ExcelForum.com

I didn't alter your logic. I suspect your msgbox is being regenerated by
the next cell checked.

I doubt the code reflects what your business rules are. You probably need
to state in explicit terms what type of test you are trying to perform -
what the rules are.

--
Regards,
Tom Ogilvy

"Mark1ace1 " wrote in message
...
It is the second code I want..I have tried it but it only allows
customers to have one and message box does not kill itself after
clicking ok...

Mark

Thanks for the help ....:)


---
Message posted from http://www.ExcelForum.com/