not urgent
 

I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student

not urgent
 

will the function be used in the worksheet like

=Dups(A1:Z26)

or just as a vba function?

--
Regards,
Tom Ogilvy

"Gary's Student" wrote in message
...
I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student

not urgent
 

simple? LOL

--
George Nicholson

Remove 'Junk' from return address.


"Gary's Student" wrote in message
...
I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student

not urgent
 

Tom, I need the function in VBA. In spreadsheets I painfully and laboriously:

1. use a macro to copy the cells in range to an unused column
2. compare the used length of the column before and after applying filter
unique!

Strangely if I "columnize" the data, I can easily mark non-unique cells with
a pivot table and eliminate non-unique cells with advance filter. In VBA I
am not so clever; I dont need to count the non-uniques or delete them, just
determine if they are there..

--
Gary's Student


"Tom Ogilvy" wrote:

will the function be used in the worksheet like

=Dups(A1:Z26)

or just as a vba function?

--
Regards,
Tom Ogilvy

"Gary's Student" wrote in message
...
I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student

not urgent
 

My attempt:

Function CheckCells(Rng As Range) As Integer
Dim cell As Range
Dim cell2 As Range
Dim RngNew As Range
Dim MyResult As Integer

Application.Volatile

For Each cell In Rng
If Not IsEmpty(cell) Then
If cell.PrefixCharacter = "" Then
If cell.NumberFormat < "@" Then
If cell.HasFormula = False Then
If RngNew Is Nothing Then
Set RngNew = cell
Else
Set RngNew = Union(RngNew, cell)
End If
End If
End If
End If
End If
Next cell

If RngNew Is Nothing Then
MyResult = -1
Else
For Each cell In RngNew
For Each cell2 In RngNew
If cell.Address < cell2.Address Then
If cell.Value = cell2.Value Then
MyResult = 1
GoTo ReportOut
End If
End If
Next cell2
Next cell
End If

ReportOut:
CheckCells = MyResult
End Function


"Gary's Student" wrote in message
...
I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student

not urgent
 

I included a third result (-1) if no valid cells were comparable, but that
was taking license... sorry, easy for you to correct.


"William Benson" wrote in message
...
My attempt:

Function CheckCells(Rng As Range) As Integer
Dim cell As Range
Dim cell2 As Range
Dim RngNew As Range
Dim MyResult As Integer

Application.Volatile

For Each cell In Rng
If Not IsEmpty(cell) Then
If cell.PrefixCharacter = "" Then
If cell.NumberFormat < "@" Then
If cell.HasFormula = False Then
If RngNew Is Nothing Then
Set RngNew = cell
Else
Set RngNew = Union(RngNew, cell)
End If
End If
End If
End If
End If
Next cell

If RngNew Is Nothing Then
MyResult = -1
Else
For Each cell In RngNew
For Each cell2 In RngNew
If cell.Address < cell2.Address Then
If cell.Value = cell2.Value Then
MyResult = 1
GoTo ReportOut
End If
End If
Next cell2
Next cell
End If

ReportOut:
CheckCells = MyResult
End Function


"Gary's Student" wrote in message
...
I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student

not urgent
 

William: Thank you very much. I'll start trying this out.

Thanks again.
--
Gary's Student


"William Benson" wrote:

My attempt:

Function CheckCells(Rng As Range) As Integer
Dim cell As Range
Dim cell2 As Range
Dim RngNew As Range
Dim MyResult As Integer

Application.Volatile

For Each cell In Rng
If Not IsEmpty(cell) Then
If cell.PrefixCharacter = "" Then
If cell.NumberFormat < "@" Then
If cell.HasFormula = False Then
If RngNew Is Nothing Then
Set RngNew = cell
Else
Set RngNew = Union(RngNew, cell)
End If
End If
End If
End If
End If
Next cell

If RngNew Is Nothing Then
MyResult = -1
Else
For Each cell In RngNew
For Each cell2 In RngNew
If cell.Address < cell2.Address Then
If cell.Value = cell2.Value Then
MyResult = 1
GoTo ReportOut
End If
End If
Next cell2
Next cell
End If

ReportOut:
CheckCells = MyResult
End Function


"Gary's Student" wrote in message
...
I need a simple Boolean function that, given a range as an argument, will:

1. ignore blanks
2. ignore text cells
3. ignore cells containing formula
4. return 1 if any two cells in the range have the same value
5. return 0 if all the cells in the range have unique values

Thanks in Advance

--
Gary's Student