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?

 

[steve the fish]

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

Are there any words in column AN?

 

[Alwinaz]

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

No. It should validate today's date before inserting dd/mm/yyyy hh:ss.

 

[steve the fish]

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

The problem is that the sumproduct will not be returning a number. Because you have stated with 'Dim Chk as Long' that Chk is a number the code is failing. The only part that could fail I can see if the INT(AN4:AN50000). If words are placed here it will produce a #VALUE error for the INT part and thus the SUMPRODUCT will also equal #VALUE and as that isn't a number you have a type mismatch error.

 

[Alwinaz]

i checked. They all contains dates (dd/mm/yyyy hh:mm)

 

[steve the fish]

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

Check cell AN4:AN50000 by putting this in a helper cell eg AO4.

=IF(ISBLANK(AN4),"",ISNUMBER(AN4))

Copy down.

Check if any are false.

 

[Alwinaz]

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

i checked. (AN4:AN50000) only contains a blank or (dd/mm/yyyy hh:mm) i even checked with =isnumber() all was true

 

[Peter_SSs]

i checked. They all contains dates (dd/mm/yyyy hh:mm)

50,000 cells is a lot to check.
In a vacant cell on that sheet and NOT in column AN, what does this formula return?

=SUMPRODUCT(--ISTEXT(AN4:AN50000))

 

[Peter_SSs]

I have merged your two threads asking the same question.
Please do not post the same question multiple times. Questions of a duplicate nature will be locked or deleted, per #9 of the Forum Rules.
The reason is so that we don't have multiple members basically going over the same ground, as has happened here.

 

[Alwinaz]

My Apologize<o></o>. I am experiecing another proble with this vba. It sometime give a the error message that i have already used this claim number on this date when clearly i did not. is there a way that i can cheque where it is picking it up from? I made 100% sure that it was not used for this date. Here is my code for easy referernce.

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
              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

 

[Peter_SSs]

Firstly, what happened about the previous issue (refer post #27)?
Did you find that there was actually one or more text entries in column AN?
If not, can you tell us what the problem was so that we can know for next time an issue like that arises?

It sometime give a the error message that i have already used this claim number on this date when clearly i did not. is there a way that i can cheque where it is picking it up from? I made 100% sure that it was not used for this date.

That could happen if you had originally entered a valid value in column B then subsequently ...

a) Tried to replace that valid entry with another valid entry on the same day, or

b) Tried to enter an invalid value in that same cell on the same day then tried to enter a valid entry on the same day.

See if making these two highlighted changes in the middle part of the code help.

If Chk = 0 [COLOR="#0000CD"][B]Or (Chk = 1 And Int(c.Offset(0, 38)) = Date)[/B][/COLOR] 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
  [COLOR="#0000CD"][B]c.Offset(0, 38).ClearContents[/B][/COLOR]
End If