Allow duplicate "NA"'s but no other dupes?

[Lostguy]

Hello!
D24 through D26 contain FYs for a school. If the school duration goes
into the next FY, you pick that from the dropdown. If it continues
into a 3rd FY, you pick that. There is also an "NA"

So for most short schools, D24 = "2010", D25="NA", and D26 ="NA"
Longer schools will have D24 = "2010", D25="2011", and D26 ="NA", etc.

For error-checking, two "2010"s would be bad and redundant. So I pop
up a messagebox for that. But it is also counting two "NA"s as bad
when that is OK.

So, it should be msgbox if they are the same FY except if they are NA
which is OK.
How can I fix this for the "NA is OK" condition?

VR/Lost



Code:
Dim rng As Range
Set rng = Range("D2426")
With Application
If .CountIf(rng, Range("D24")) > 1 Or .CountIf(rng, Range("D25")) > 1
Or .CountIf(rng, Range("D26")) > 1 Then
MsgBox "There are duplicate overlap FYs. Please correct, then press
the Print button.", vbExclamation, "Duplicate overlap FYs!"
GoTo enditall
Else
End If
End With

 

[Per Jessen]

Hi

Here's a way to do it, which will also allow larger ranges to be tested,
simply by changing the range:

Dim rng As Range
Set rng = Range("D2426")
For Each cell In rng
If cell.Value = "NA" Then Exit For
If Application.CountIf(rng, cell.Value) > 1 Then
MsgBox "There are duplicate overlap FYs." & vbLf & vbLf & _
"Please correct, then press the Print button.", _
vbExclamation, "Duplicate overlap FYs!"
GoTo EndItAll
End If
Next

Regards,
Per

 

[Per Jessen]

I just realized that if there can be a year value after a NA, this version
will be better:

Dim rng As Range
Set rng = Range("D2426")
For Each cell In rng
If cell.Value <> "NA" Then
If Application.CountIf(rng, cell.Value) > 1 Then
MsgBox "There are duplicate overlap FYs." & vbLf & vbLf & _
"Please correct, then press the Print button.", _
vbExclamation, "Duplicate overlap FYs!"
GoTo EndItAll
End If
End If
Next

Regards,
Per

 

[Lostguy]

Per,

Absolutely what I was looking for! And I learned something new about
"Exit For" and the line feeds for the message box. Because of you, I
was able to get my project out on time.

Thanks for all your help!

VR/Lost