Previous Topic Next Topic

Data validation formula

Solved/Closed
Stevie - May 7, 2010 at 09:08 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Aug 10, 2010 at 02:34 PM
Hello,

I have a spread sheet that tracks attendance. What i want to do is to alert the user whenever 3 OR MORE CELLS IN SEQUENCE CONTAIN THE SAME DATA. eg. If someone calls in sick, a S is placed in the cell, if this person is sick Mon , Tues, Wed, I would like to alert the user. ( 3 days in sequence with the same data ). If the person is sick on Thur aswell, I want to alert the user again.

Need help with the formula for this.

Thanks

3 responses

Answer 1 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 09:47 AM
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
0
Stevie
May 7, 2010 at 10:38 AM
https://authentification.site/files/22317515/ATTENDANCE.xls

the only time i want the alert to show is if the letter S is repeated 3 or more times in sequence.
0
Answer 2 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 04:20 PM
1. Press ALT + F11 to open VBE
2. Press CTRL + R to open Project Explorer
3. Double click on the sheet in which you want this message box
4. Paste the code 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim vPos As Variant
Dim iCol As Integer
Dim CellValue As Variant


    If ((Target.Columns.Count = 1) And (Target.Rows.Count = 1)) Then
        If Target = "" Then Exit Sub
    End If
    
    vPos = ""
    
    Application.EnableEvents = False
        
    For Each Cell In Target
        
        If UCase(Cell) <> "S" Then GoTo Next_Cell
            
        vPos = ""
        
        iCol = Cell.Column
        
        If iCol >= 3 Then
            
            If ((Cell = Cell.Offset(0, -2)) And (Cell.Offset(0, -1) = Cell)) Then
                vPos = -1
            End If
            
        End If
        
        If ((vPos = "") And (iCol >= 2) And (iCol < Columns.Count)) Then
            
            If ((Cell = Cell.Offset(0, -1)) And (Cell.Offset(0, 1) = Cell)) Then
                vPos = 0
            End If
        
        End If
        
        If ((vPos = "") And (iCol < Columns.Count - 1)) Then
        
            If ((Cell = Cell.Offset(0, 1)) And (Cell.Offset(0, 2) = Cell)) Then
                vPos = 1
            End If
                
        End If
        
        
        If (vPos <> "") Then
        
            GoTo End_Sub
        
        End If
    
Next_Cell:
  
    Next

End_Sub:

    Application.EnableEvents = True
    If (vPos <> "") Then
    
        MsgBox "Three in a row"
        
    End If
End Sub
0
Stevie
May 10, 2010 at 10:50 AM
thanks, exactly what i wanted.

just one concern.

sometimes i get the type mismatch error, it gives me the option to end, debug, help. Is it possible to not get this error. I do not want the users to mess with the macro,
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 10, 2010 at 11:04 AM
You would need to get more specific about the error like what caused issue, what line blows up, what is the data when that error occurred etc
0
Stevie
May 10, 2010 at 11:27 AM
I get a " runtime 13 error, type mismatch "
I get it when i select 3 or more cells with the S and then try to delete the data.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 10, 2010 at 11:50 AM
Could you please upload a sample file with sample data and macro on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
0
Answer 3 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 13, 2010 at 08:35 AM
try this 


Private Sub Worksheet_Change(ByVal Target As Range)
Dim vPos As Variant
Dim iCol As Integer
Dim CellValue As Variant
Dim iOffsetL2 As Integer
Dim iOffsetL1 As Integer
Dim iOffsetR1 As Integer
Dim iOffset2 As Integer

Dim CellL2 As Variant
Dim CellL1 As Variant
Dim Cell0 As Variant
Dim CellR1 As Variant
Dim CellR2 As Variant

    If ((Target.Columns.Count = 1) And (Target.Rows.Count = 1)) Then
        If Target = "" Then Exit Sub
    End If
    
    vPos = ""
    
  '  Exit Sub
    
    On Error GoTo End_Sub
    
    Application.EnableEvents = False
        
    For Each Cell In Target
        
        Cell0 = UCase(Cell.Value)
        'If Cell0 <> "S" Then GoTo Next_Cell
                
        vPos = ""
        iOffsetL2 = 0
        iOffsetL1 = 0
        iOffsetR1 = 0
        iOffsetR2 = 0
        
        iCol = Cell.Column
        
        If (IsDate(Cells(1, iCol))) Then
                       
            CellL2 = "Garbage Value"
            CellL1 = "Garbage Value"
            CellR1 = "Garbage Value"
            CellR2 = "Garbage Value"

            Select Case (Weekday(Cells(1, iCol), vbMonday))
            
                Case Is = 1
                    iOffsetL2 = -2
                    iOffsetL1 = -2
                    iOffsetR1 = 0
                    iOffsetR2 = 0
                    
                Case Is = 2
                    iOffsetL2 = -2
                    iOffsetL1 = 0
                    iOffsetR1 = 0
                    iOffsetR2 = 0
                
                Case Is = 4
                    iOffsetL2 = 0
                    iOffsetL1 = 0
                    iOffsetR1 = 0
                    iOffsetR2 = 2
                
                Case Is = 5
                    iOffsetL2 = 0
                    iOffsetL1 = 0
                    iOffsetR1 = 2
                    iOffsetR2 = 2
            End Select
        End If
        
        On Error Resume Next
            CellL2 = Cell.Offset(0, (-2 + iOffsetL2)).Value
            CellL1 = Cell.Offset(0, (-1 + iOffsetL1)).Value
            CellR1 = Cell.Offset(0, (1 + iOffsetR1)).Value
            CellR2 = Cell.Offset(0, (2 + iOffsetR2)).Value
        On Error GoTo End_Sub
        
        CellL2 = UCase(CellL2)
        CellL1 = UCase(CellL1)
        CellR1 = UCase(CellR1)
        CellR2 = UCase(CellR2)
        
        If (iCol + iOffsetL2 > 2) Then
            
            ' ? ? X
            If ((CellL2 = Cell0) And (CellL1 = Cell0)) Then
                vPos = -1
                GoTo End_Sub
            End If
            
        End If
        
       
        If ((iCol + iOffsetL1 > 0) And ((iCol - iOffsetR1) < Columns.Count)) Then
            
            ' ? X ?
            If ((CellL1 = Cell0) And (Cell0 = CellR1)) Then
                vPos = 0
                GoTo End_Sub
            End If
        
        End If
        
        
        If (iCol < Columns.Count - 1) Then
        
            ' X ? ?
            If ((Cell0 = CellR1) And (Cell0 = CellR2)) Then
                vPos = 1
                GoTo End_Sub
            End If
                
        End If
    
Next_Cell:
  
    Next

End_Sub:

    Application.EnableEvents = True
    If (vPos <> "") Then
    
        MsgBox "Three in a row"
        
    End If
End Sub
0
stevie
May 13, 2010 at 10:05 AM
perfect. thanks a million.
0
stevie
May 14, 2010 at 10:55 AM
I inserted the calendar control object into my spreadsheet, doing this makes the message box appear after the S is entered 4 times e.g. Friday ,Monday, Tuesday, Wedneday. It still works correctly If the weekend is not used.eg. Mon,Tue, Wed.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 14, 2010 at 05:34 PM
You have to post the sheet with sample data
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 17, 2010 at 10:02 AM
Change this line

If (IsDate(Cells(1, iCol))) Then

1 here refers to the row where the dates are, On this sample it seems they are on 15
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses
formulas for 1st and 2nd position in class
jack -
suff954 -

1 response