How to count cells with a particular data and change the cell data

[shahabm]

How to count cells with a particular data and change the cell data when a predefined limit is reached
I am in the process of making an attendance sheet for work . for which i take mobile number and the date as inputs and mark "P" for presence. if all is well the presence is marked as "P" else pop is shown

Question1: how can i show name of the person checked in, in the msg box,(just says checked in for now) the names are in column "B"

Question2: the number of times the person can come is restricted and the value for that is in column AP
for eg: the value is 5 so on the 5th time that he appears the cell should be marked as "PE"
also a pop up with an error should be seen

FYI... also in the field may appear Value "PU"
P's and pu's are marked in the columns E:AI, each for 1 day of the week

Sub attendance()
Application.EnableCancelKey = xlDisabled
Dim FindString As String
Dim FindString1 As String
Dim Rng As Range
Do
FindString = InputBox("Enter Your Mobile Number")
FindString1 = InputBox("Enter todays Date - e.g 21  for 21/03/2015")
If FindString = "" Then Exit Sub
If Trim(FindString) <> "" And Trim(FindString1) <> "" Then
    
    With Sheets("Attendance").Range("D:D")
        Set Rng = .Find(What:=FindString, LookAt:=xlWhole)
        If Not Rng Is Nothing Then
        Rng.Offset(0, FindString1).Select
         Rng.Offset(0, FindString1).Value = "P"
                   
          
           MsgBox "Checked In"
            
            
    Else
                    MsgBox "Not Registered"
                               End If
   End With
   
End If


Loop


End Sub

any help will be appreciated..

thnx

 

[JLGWhiz]

Question1: how can i show name of the person checked in, in the msg box,(just says checked in for now) the names are in column "B"

MsgBox "Checked In: " & ActiveSheet.Range("B" & Rng.Row).Value)

Question2: the number of times the person can come is restricted and the value for that is in column AP
for eg: the value is 5 so on the 5th time that he appears the cell should be marked as "PE"
also a pop up with an error should be seen

Immediately after the above message box, insert this:

If ActiveSheet.Range("AP" & Rng.Row).Value = 5 Then
	MsgBox "This is appearance number " ActiveSheet.Range("AP" & Rng.Row).Value &  " for this person.",vbCritical, "MAXED OUT"
End if

 

[shahabm]

Thanks JLGWhiz

the first part of the code works perfectly.

the second part which is

[/COLOR][COLOR=#333333]If ActiveSheet.Range("AP" & Rng.Row).Value = 5 Then[/COLOR]
	MsgBox "This is appearance number " ActiveSheet.Range("AP" & Rng.Row).Value &  " for this person.",vbCritical, "MAXED OUT" [COLOR=#333333]End if[/COLOR][COLOR=#574123]

show an error
compile error
syntax error

please tell me what i did incorrectly

thanks

 

[JLGWhiz]

Had a missing ampersand. Also note that I changed the = to >= in case the numbers in column AP increment more than one at a time.

If ActiveSheet.Range("AP" & Rng.Row).Value [COLOR="#B22222"]>=[/COLOR] 5 Then
    MsgBox "This is appearance number "[COLOR="#B22222"][/COLOR][COLOR="#B22222"] &[/COLOR] ActiveSheet.Range("AP" & Rng.Row).Value & " for this person.", vbCritical, "MAXED OUT"
End If