creating a macro

[DDD]

Say i have 2 worksheets renamed "A" & "B"
in worksheet A i type in cell A1 "=30-(COUNTIF(B!A1:A40,C1))"
in cell C1 i type in "J"

If i type in "J" once in each cell from A1:A30 (in worksheet B)

i will get an answer of 0 in A1 (in worksheet A)

if i type in "J" again in cell A31 (worksheet B)

i will get an answer of -1. i dont want this

what code do i need for a macro that displays a message saying "impossible"
when i enter "J" in cell A31 & does not accept it within the system, &
deletes the contents of the cell i typed it in (A31).


so the lowest value that can be displayed within cell A1 (worksheet A) is 0


sorry if this doest make sense, thought this was easier to understand rather
than explaining my real system

 

[Mike H]

Hi,

You could do it with the formula

=MAX(0,30-(COUNTIF(B!A1:A40,C1)))

but if you want a mcro then here's one way. Right click sheet B sheet tab
and view code and psate this in. It will reject the 31st J

Private Sub Worksheet_Change(ByVal Target As Range)
If WorksheetFunction.CountIf(Range("A1:A40"), Sheets("A").Range("C1")) > 30
Then
MsgBox "No more " & Sheets("A").Range("C1").Value & " Allowed"
Target.Value = ""
End If
End Sub

Mike