excel custom data valadation with countifs

[Alwinaz]

Hi there

I posted a thread earlier it looks lik it was wrorking but it now appears not working.

Can you use countifs in custom data valadation my formula that i want to use is =COUNTIFS($B$4:$B$50000,"="&B4,$AO$4:$AO$50000,"="&AO4)=1 Can any body help me?

 

[Peter_SSs]

in column ao i have the value of AN as i had problems with the value as inserted in AN in other formulas.

I'm not really sure what that means but as Andrew says, the code is writing to AN, so I am using that column for my check. My understanding is that you want no duplicates for a date. That is you ignore time. So if "abc" has been entered in column B with a date/time stamp of "6 October 2014 15:05" in column AN then "abc" cannot be added to column B again on 6 October 2014 at any time.
My code is based on the assumptions above.

Note also my two EnableEvents lines. Whenever a Worksheet_Change code is altering the sheet, 'Events' should be disabled so that the code doesn't keep re-calling itself.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim c As Range, Changed As Range
  Dim Chk As Long
  
  Set Changed = Intersect(Target, Columns("B").Rows("4:50000"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
        If c.Value <> "" Then
            Chk = Evaluate("Sumproduct(--(B4:B50000=" & c.Address & "),--(AN4:AN50000<>""""),--(INT(AN4:AN50000)=" & CLng(Date) & "))")
            If Chk = 0 Then
              c.Offset(0, 38).Value = Format(Now, "dd-mmmm-yyyy h:mm")
            Else
              MsgBox c.Value & " already used for " & Format(Date, "dd-mmmm-yyyy") & vbLf & "It wll be cleared."
              c.ClearContents
            End If
        Else
            c.Offset(0, 38).ClearContents
        End If
    Next c
    Application.EnableEvents = True
  End If

End Sub

 

[Alwinaz]

Peter_SSs it works perfectly. How can i just change the end sot that if goes to the cell that has been deleted. I.e if i enter in B5 the same vale as in B4 it deletes the value, but move to b6.

 

[Peter_SSs]

Peter_SSs it works perfectly. How can i just change the end sot that if goes to the cell that has been deleted. I.e if i enter in B5 the same vale as in B4 it deletes the value, but move to b6.

Try adding this line where indicated.

Else
  c.Select
  MsgBox c.Value & " already used for " & Format(Date, "dd-mmmm-yyyy") & vbLf & "It wll be cleared."

 

[Alwinaz]

perfect. Thanks soooooo much for your help.

 

[Peter_SSs]

perfect. Thanks soooooo much for your help.

No problem - glad to help.

 

[Alwinaz]

sorry to bother again. I am pasting this code in all my work books I came across a runtime error 13 type mismatch at this line

Chk = Evaluate("Sumproduct(--(B4:B50000=" & c.Address & "),--(AN4:AN50000<>""""),--(INT(AN4:AN50000)=" & CLng(Date) & "))")

Any idea why?

The sheet is exactly the same as the other once, just with different data.

 

[Alwinaz]

VBA Validating and timestamp - Runtime error 13 - Type mismatch

Hi

One of the Moderators helped me with a vba to validate before inserting a timpstamp.

It works perfectly one all my sheets, however on one of the sheets i got this runtime error 13 - Type mismatch. The sheets are all in the same format.

My code is:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  Dim c As Range, Changed As Range
  Dim Chk As Long
  
  Set Changed = Intersect(Target, Columns("B").Rows("4:50000"))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
        If c.Value <> "" Then
           [COLOR=#ff0000] Chk = Evaluate("Sumproduct(--(B4:B50000=" & c.Address & "),--(AN4:AN50000<>""""),--(INT(AN4:AN50000)=" & CLng(Date) & "))")
[/COLOR]            If Chk = 0 Then
              c.Offset(0, 38).Value = Format(Now, "dd-mmmm-yyyy h:mm")
            Else
              c.Select
             MsgBox "Claim Number: " & c.Value & " already used for " & Format(Date, "dd-mmmm-yyyy") & vbLf & "It wll be cleared. Amend activity previously captured for this claim. "
              c.ClearContents
            End If
        Else
            c.Offset(0, 38).ClearContents
        End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

The red part is where i get the error.

Any ideas?

 

[steve the fish]

Re: VBA Validating and timestamp - Runtime error 13 - Type mismatch

Your sumproduct wont be finding a value that can be dimmed as long.

 

[Peter_SSs]

sorry to bother again. I am pasting this code in all my work books I came across a runtime error 13 type mismatch at this line

Chk = Evaluate("Sumproduct(--(B4:B50000=" & c.Address & "),--(AN4:AN50000<>""""),--(INT(AN4:AN50000)=" & CLng(Date) & "))")

Any idea why?

Most likely you have one or more cells in AN4:AN50000 that contains a non-numeric value.

 

[Alwinaz]

Re: VBA Validating and timestamp - Runtime error 13 - Type mismatch

i dont understand??? must i change the date here CLng(Date)