Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
Urgent-Urgent VBA LOOP | Excel Discussion (Misc queries) | |||
HELP URGENT PLEASE !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! | Excel Programming | |||
Macro help urgent urgent | Excel Programming | |||
Macro help urgent urgent | Excel Programming |