I already know that the standard tool that comes with Excel only works with
data entry cells, but because of the context of more complex spreadsheets,
there has to be a way to perform simple validations (which render a similar
dialog to the user) when the results of his/her work are invalid in
calculated cells?!

My task is simple: Cell C1 renders a simple sum Fx from cells A1 and B1.
Cell C2 = A2 + B2, etc. All I need to have Excel do is tell the user right
away whenever C1=C2, or in batches in the event of Copy and Paste.

Any help would be greatly appreciated!

Greg

Greg,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. You may want to play with the various Exit Sub conditions....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
If Range("C1").Value = "" Then Exit Sub
If Range("C2").Value = "" Then Exit Sub
If Range("C1").Value = 0 Then Exit Sub
If Range("C2").Value = 0 Then Exit Sub

If Range("C1").Value = Range("C2").Value Then
MsgBox "Hey! C1 is the same as C2!"
End If
End Sub


"Gregory Winters" wrote in message
...
I already know that the standard tool that comes with Excel only works with
data entry cells, but because of the context of more complex spreadsheets,
there has to be a way to perform simple validations (which render a similar
dialog to the user) when the results of his/her work are invalid in
calculated cells?!

My task is simple: Cell C1 renders a simple sum Fx from cells A1 and B1.
Cell C2 = A2 + B2, etc. All I need to have Excel do is tell the user right
away whenever C1=C2, or in batches in the event of Copy and Paste.

Any help would be greatly appreciated!

Greg

Thanks, Bernie! I have only one other criterion: the sum column could
contain as many as 45,000 cells. Is there a way to alter cell reference
piece of the If Range statement to accomodate this? Thanks!

Greg

"Bernie Deitrick" wrote:

Greg,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. You may want to play with the various Exit Sub conditions....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
If Range("C1").Value = "" Then Exit Sub
If Range("C2").Value = "" Then Exit Sub
If Range("C1").Value = 0 Then Exit Sub
If Range("C2").Value = 0 Then Exit Sub

If Range("C1").Value = Range("C2").Value Then
MsgBox "Hey! C1 is the same as C2!"
End If
End Sub


"Gregory Winters" wrote in message
...
I already know that the standard tool that comes with Excel only works with
data entry cells, but because of the context of more complex spreadsheets,
there has to be a way to perform simple validations (which render a similar
dialog to the user) when the results of his/her work are invalid in
calculated cells?!

My task is simple: Cell C1 renders a simple sum Fx from cells A1 and B1.
Cell C2 = A2 + B2, etc. All I need to have Excel do is tell the user right
away whenever C1=C2, or in batches in the event of Copy and Paste.

Any help would be greatly appreciated!

Greg

Greg,

Do you only require that consecutive cells not have the same value, or do you not want to have any
value in column C repeated (Does C2 need to be compared to C45000)?

HTH,
Bernie
MS Excel MVP


"Gregory Winters" wrote in message
...
Thanks, Bernie! I have only one other criterion: the sum column could
contain as many as 45,000 cells. Is there a way to alter cell reference
piece of the If Range statement to accomodate this? Thanks!

Greg

"Bernie Deitrick" wrote:

Greg,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. You may want to play with the various Exit Sub conditions....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
If Range("C1").Value = "" Then Exit Sub
If Range("C2").Value = "" Then Exit Sub
If Range("C1").Value = 0 Then Exit Sub
If Range("C2").Value = 0 Then Exit Sub

If Range("C1").Value = Range("C2").Value Then
MsgBox "Hey! C1 is the same as C2!"
End If
End Sub


"Gregory Winters" wrote in message
...
I already know that the standard tool that comes with Excel only works with
data entry cells, but because of the context of more complex spreadsheets,
there has to be a way to perform simple validations (which render a similar
dialog to the user) when the results of his/her work are invalid in
calculated cells?!

My task is simple: Cell C1 renders a simple sum Fx from cells A1 and B1.
Cell C2 = A2 + B2, etc. All I need to have Excel do is tell the user right
away whenever C1=C2, or in batches in the event of Copy and Paste.

Any help would be greatly appreciated!

Greg

There can be absolutely no duplicates in the entire column - 2 thru 45000,
thanks.

"Bernie Deitrick" wrote:

Greg,

Do you only require that consecutive cells not have the same value, or do you not want to have any
value in column C repeated (Does C2 need to be compared to C45000)?

HTH,
Bernie
MS Excel MVP


"Gregory Winters" wrote in message
...
Thanks, Bernie! I have only one other criterion: the sum column could
contain as many as 45,000 cells. Is there a way to alter cell reference
piece of the If Range statement to accomodate this? Thanks!

Greg

"Bernie Deitrick" wrote:

Greg,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. You may want to play with the various Exit Sub conditions....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
If Range("C1").Value = "" Then Exit Sub
If Range("C2").Value = "" Then Exit Sub
If Range("C1").Value = 0 Then Exit Sub
If Range("C2").Value = 0 Then Exit Sub

If Range("C1").Value = Range("C2").Value Then
MsgBox "Hey! C1 is the same as C2!"
End If
End Sub


"Gregory Winters" wrote in message
...
I already know that the standard tool that comes with Excel only works with
data entry cells, but because of the context of more complex spreadsheets,
there has to be a way to perform simple validations (which render a similar
dialog to the user) when the results of his/her work are invalid in
calculated cells?!

My task is simple: Cell C1 renders a simple sum Fx from cells A1 and B1.
Cell C2 = A2 + B2, etc. All I need to have Excel do is tell the user right
away whenever C1=C2, or in batches in the event of Copy and Paste.

Any help would be greatly appreciated!

Greg

I guess I should also mention that since this is coming from a Copy & Paste
function, that the code should be able to continue 'working' until all
duplicates have been removed, thanks.

"Bernie Deitrick" wrote:

Greg,

Do you only require that consecutive cells not have the same value, or do you not want to have any
value in column C repeated (Does C2 need to be compared to C45000)?

HTH,
Bernie
MS Excel MVP


"Gregory Winters" wrote in message
...
Thanks, Bernie! I have only one other criterion: the sum column could
contain as many as 45,000 cells. Is there a way to alter cell reference
piece of the If Range statement to accomodate this? Thanks!

Greg

"Bernie Deitrick" wrote:

Greg,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. You may want to play with the various Exit Sub conditions....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
If Range("C1").Value = "" Then Exit Sub
If Range("C2").Value = "" Then Exit Sub
If Range("C1").Value = 0 Then Exit Sub
If Range("C2").Value = 0 Then Exit Sub

If Range("C1").Value = Range("C2").Value Then
MsgBox "Hey! C1 is the same as C2!"
End If
End Sub


"Gregory Winters" wrote in message
...
I already know that the standard tool that comes with Excel only works with
data entry cells, but because of the context of more complex spreadsheets,
there has to be a way to perform simple validations (which render a similar
dialog to the user) when the results of his/her work are invalid in
calculated cells?!

My task is simple: Cell C1 renders a simple sum Fx from cells A1 and B1.
Cell C2 = A2 + B2, etc. All I need to have Excel do is tell the user right
away whenever C1=C2, or in batches in the event of Copy and Paste.

Any help would be greatly appreciated!

Greg

Greg,

This will completely undo the entry:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1 Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do that."
Application.EnableEvents = False 'Optional undo
Application.Undo 'Optional undo
Application.EnableEvents = True 'Optional undo
Exit Sub 'Optional undo
End If
Next myCell
End Sub

This will just flag which entries were bad:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1 Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do that."
End If
Next myCell
End Sub

and this will just remove the offending entries and notify, one by one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1 Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do that."
Application.EnableEvents = False
Intersect(myCell.EntireRow, Target).ClearContents
Application.EnableEvents = True
End If
Next myCell
End Sub



HTH,
Bernie
MS Excel MVP


"Gregory Winters" wrote in message
...
I guess I should also mention that since this is coming from a Copy & Paste
function, that the code should be able to continue 'working' until all
duplicates have been removed, thanks.

"Bernie Deitrick" wrote:

Greg,

Do you only require that consecutive cells not have the same value, or do you not want to have
any
value in column C repeated (Does C2 need to be compared to C45000)?

HTH,
Bernie
MS Excel MVP


"Gregory Winters" wrote in message
...
Thanks, Bernie! I have only one other criterion: the sum column could
contain as many as 45,000 cells. Is there a way to alter cell reference
piece of the If Range statement to accomodate this? Thanks!

Greg

"Bernie Deitrick" wrote:

Greg,

Copy the code below, right-click the sheet tab, select "View Code" and paste the code into the
window that appears. You may want to play with the various Exit Sub conditions....

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Calculate()
If Range("C1").Value = "" Then Exit Sub
If Range("C2").Value = "" Then Exit Sub
If Range("C1").Value = 0 Then Exit Sub
If Range("C2").Value = 0 Then Exit Sub

If Range("C1").Value = Range("C2").Value Then
MsgBox "Hey! C1 is the same as C2!"
End If
End Sub


"Gregory Winters" wrote in message
...
I already know that the standard tool that comes with Excel only works with
data entry cells, but because of the context of more complex spreadsheets,
there has to be a way to perform simple validations (which render a similar
dialog to the user) when the results of his/her work are invalid in
calculated cells?!

My task is simple: Cell C1 renders a simple sum Fx from cells A1 and B1.
Cell C2 = A2 + B2, etc. All I need to have Excel do is tell the user right
away whenever C1=C2, or in batches in the event of Copy and Paste.

Any help would be greatly appreciated!

Greg

"Bernie Deitrick" <deitbe @ consumer dot org wrote...
Greg,

This will completely undo the entry:

Private Sub Worksheet_Change(ByVal Target As Range)
....
Application.Undo 'Optional undo
....

The cleverer users may figure out something like

C2:
=IF(foo,A2+B2,ROW())

Enter this formula with the defined name foo equal to FALSE, and the
formulas would be accepted. Then change foo to TRUE. Recalculation
doesn't trigger Change event handlers. To make this robust, you'd need
to use Calculate handlers too.

But that begs the question how to ensure users don't turn off macros
entirely or the cleverer ones disable event handlers.

If the OP just wants to flag duplicate values arising from sums of
cols A and B in col C (e.g., C2: =A2+B2), then conditional formatting
would be most appropriate - AND it works whether macros are enabled or
disabled. Make the conditions formulas like

C1:
=COUNTIF(C2:C$45000,C1)0

C2:
=COUNTIF(C$1:C1,C2)+COUNTIF(C3:C$45000,C2)0

Fill C2 down into C3:C44999

C45000:
=COUNTIF(C$1:C44999,C45000)0

and set an appropriate format, like red cell background color. Add a
formula in another cell to check that the count of distinct values in
col C equals the count of all values in col C, and use that to
determine whether the formulas are valid or not.

Well, I guess this had to be a lot harder than I made it sound, huh? :-(
The code works GREAT. Problem is: I can't use it! I create a macro, paste
the code into it, save it in the VBA editor, attempt to open the macro,
and...no macro! The name of the macro has vanished, but when I open the
Editor again and double click on the module, there is the code, right where I
had pasted it.

I have never run across an instance where I have recorded a macro, then it
has literally disappeared. Ideas?

P.S. Harlan, conditional formatting is basically out of the question since
it would force the users to scroll through ~45000 rows and 300 columns of
entries just to located those colored cells. Thanks, however, and I've saved
your notes for the future.

Greg

"Bernie Deitrick" wrote:

Greg,

This will completely undo the entry:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1 Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do that."
Application.EnableEvents = False 'Optional undo
Application.Undo 'Optional undo
Application.EnableEvents = True 'Optional undo
Exit Sub 'Optional undo
End If
Next myCell
End Sub

This will just flag which entries were bad:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1 Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do that."
End If
Next myCell
End Sub

and this will just remove the offending entries and notify, one by one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1 Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do that."
Application.EnableEvents = False
Intersect(myCell.EntireRow, Target).ClearContents
Application.EnableEvents = True
End If
Next myCell
End Sub



HTH,
Bernie
MS Excel MVP

Greg,

It isn't a macro, per se, it is an event, so it won't show up in the macro
dialog from Tools / Macros....

You can test for its presence by copying and pasting two cells of one row
onto a row beneath. That will force the cell in col C to be equal to
another cell, and should bring up the dialog.

All my code worked in all my testing, though I never used 45,000 rows. If
you can't get it to work, I can send you a working example, which you could
use for testing your system.

HTH,
Bernie
MS Excel MVP




"Gregory Winters" wrote in
message ...
Well, I guess this had to be a lot harder than I made it sound, huh? :-(
The code works GREAT. Problem is: I can't use it! I create a macro,
paste
the code into it, save it in the VBA editor, attempt to open the macro,
and...no macro! The name of the macro has vanished, but when I open the
Editor again and double click on the module, there is the code, right
where I
had pasted it.

I have never run across an instance where I have recorded a macro, then it
has literally disappeared. Ideas?

P.S. Harlan, conditional formatting is basically out of the question
since
it would force the users to scroll through ~45000 rows and 300 columns of
entries just to located those colored cells. Thanks, however, and I've
saved
your notes for the future.

Greg

"Bernie Deitrick" wrote:

Greg,

This will completely undo the entry:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False 'Optional undo
Application.Undo 'Optional undo
Application.EnableEvents = True 'Optional undo
Exit Sub 'Optional undo
End If
Next myCell
End Sub

This will just flag which entries were bad:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
End If
Next myCell
End Sub

and this will just remove the offending entries and notify, one by one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False
Intersect(myCell.EntireRow, Target).ClearContents
Application.EnableEvents = True
End If
Next myCell
End Sub



HTH,
Bernie
MS Excel MVP

Gregory Winters wrote...
....
P.S. Harlan, conditional formatting is basically out of the
question since it would force the users to scroll through ~45000
rows and 300 columns of entries just to located those colored
cells. Thanks, however, and I've saved your notes for the future.
....

I guess I need to ask what's the actual task. You have users with data
in 45000 rows of columns A and B, and they then have to enter formulas
to sum each row's column A and B values in column C, but you want some
means of stopping them from doing so when there are duplicate
resulting column C values?

So what then? Would users leave the cells blank on the rows in which
column A and B values sum to the same value as in other rows? Would
they be expected to change the column A or B values? Something else?

Depending on your answers, there may be simpler ways of dealing with
the core problem rather than dealing with the symptoms using highly
specific event handlers. For instance, using my proposed conditional
formatting formulas in a 4th column (D) would allow your users to use
autofilters to display only the rows in which the column D formulas
were TRUE. They could then either erase the column C formulas or
change the column A or B values in the filtered table. No macros
needed.

Bernie and Harlan:

Here is the job summary I am working from. It's more detailed, but I don't
believe fundamentally different that what we have been discussing. Again,
thanks for all your help.

Columns (call them 'B & C') simply represent finite lists of values which
could appear in any of the cells in their respective columns: B representing
a three-alpha abbreviation of a plant, and C representing a four-digit model
year. There can be any combination of plants and model years, but only one
value per cell. Column A represents how we need the concatenation of these
two values to appear: ((Plant=ARL) AND (Model Year=2000))

This would be an example of how the display would appear had the user really
selected ARL in B7 and 2000 in C7. (They could, of course, choose any of the
plant abbreviations and model years.)

Understanding that columns B & C will likely be populated via Copy & Paste
and there could be as many as 45,000 rows, the task at hand is to inform the
user if there are values anywhere in columns B and C which are not members of
the designated lists and allow the user to make appropriate corrections (or
simply delete the selection. Column A will then concatenate whatever is
decided upon after the error checking of B and C. As usual, blanks anywhere
should be considered deliberate and not a part of the actual error checking,
however if there is a value in either column B or C, then the checker should
alert the user that its partner cell is blank. At that point, the user would
either have to supply the missing value, or delete the one entered. Two
blanks in a same row of columns B & C are permissible as well as two valid
values, but one value and one blank and/or invalid values are not permitted.
The values in A do not have to be unique (obviously, with 45,000 entries,
this is not mathematically possible, anyway).

The piece in (call them columns F & G) is very similar, only (for now) we
simply have a single link to worry about. There is a formula in Column F,
were desiring a simple cell-to-cell reference (using CONCATENATE to avoid
the display of a zero). Again, the data in G will be the result of a large
Copy & Paste operation. The values in F must be unique, and the error
checker should cleanly call out the duplicates €“ one by one €“ and allow the
user to step into the checking process and fix the corresponding values in
column G until all the values in F are indeed unique. Again, blanks in G
(and the resulting blanks in F) would be permitted.

Hope this detail makes things clearer!

Greg

"Bernie Deitrick" wrote:

Greg,

It isn't a macro, per se, it is an event, so it won't show up in the macro
dialog from Tools / Macros....

You can test for its presence by copying and pasting two cells of one row
onto a row beneath. That will force the cell in col C to be equal to
another cell, and should bring up the dialog.

All my code worked in all my testing, though I never used 45,000 rows. If
you can't get it to work, I can send you a working example, which you could
use for testing your system.

HTH,
Bernie
MS Excel MVP




"Gregory Winters" wrote in
message ...
Well, I guess this had to be a lot harder than I made it sound, huh? :-(
The code works GREAT. Problem is: I can't use it! I create a macro,
paste
the code into it, save it in the VBA editor, attempt to open the macro,
and...no macro! The name of the macro has vanished, but when I open the
Editor again and double click on the module, there is the code, right
where I
had pasted it.

I have never run across an instance where I have recorded a macro, then it
has literally disappeared. Ideas?

P.S. Harlan, conditional formatting is basically out of the question
since
it would force the users to scroll through ~45000 rows and 300 columns of
entries just to located those colored cells. Thanks, however, and I've
saved
your notes for the future.

Greg

"Bernie Deitrick" wrote:

Greg,

This will completely undo the entry:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False 'Optional undo
Application.Undo 'Optional undo
Application.EnableEvents = True 'Optional undo
Exit Sub 'Optional undo
End If
Next myCell
End Sub

This will just flag which entries were bad:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
End If
Next myCell
End Sub

and this will just remove the offending entries and notify, one by one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False
Intersect(myCell.EntireRow, Target).ClearContents
Application.EnableEvents = True
End If
Next myCell
End Sub



HTH,
Bernie
MS Excel MVP

Gregory Winters wrote...
....
Columns (call them 'B & C') simply represent finite lists of
values which could appear in any of the cells in their respective
columns: B representing a three-alpha abbreviation of a plant,
and C representing a four-digit model year. There can be any
combination of plants and model years, but only one value per
cell. Column A represents how we need the concatenation of these
two values to appear: ((Plant=ARL) AND (Model Year=2000))

If plan

This would be an example of how the display would appear had the user really
selected ARL in B7 and 2000 in C7. (They could, of course, choose any of the
plant abbreviations and model years.)

Understanding that columns B & C will likely be populated via Copy & Paste
and there could be as many as 45,000 rows, the task at hand is to inform the
user if there are values anywhere in columns B and C which are not members of
the designated lists and allow the user to make appropriate corrections (or
simply delete the selection. Column A will then concatenate whatever is
decided upon after the error checking of B and C. As usual, blanks anywhere
should be considered deliberate and not a part of the actual error checking,
however if there is a value in either column B or C, then the checker should
alert the user that its partner cell is blank. At that point, the user would
either have to supply the missing value, or delete the one entered. Two
blanks in a same row of columns B & C are permissible as well as two valid
values, but one value and one blank and/or invalid values are not permitted.
The values in A do not have to be unique (obviously, with 45,000 entries,
this is not mathematically possible, anyway).

The piece in (call them columns F & G) is very similar, only (for now) we
simply have a single link to worry about. There is a formula in Column F,
we're desiring a simple cell-to-cell reference (using CONCATENATE to avoid
the display of a zero). Again, the data in G will be the result of a large
Copy & Paste operation. The values in F must be unique, and the error
checker should cleanly call out the duplicates - one by one - and allow the
user to step into the checking process and fix the corresponding values in
column G until all the values in F are indeed unique. Again, blanks in G
(and the resulting blanks in F) would be permitted.

Hope this detail makes things clearer!

Greg

"Bernie Deitrick" wrote:
Greg,

It isn't a macro, per se, it is an event, so it won't show up in the macro
dialog from Tools / Macros....

You can test for its presence by copying and pasting two cells of one row
onto a row beneath. That will force the cell in col C to be equal to
another cell, and should bring up the dialog.

All my code worked in all my testing, though I never used 45,000 rows. If
you can't get it to work, I can send you a working example, which you could
use for testing your system.

HTH,
Bernie
MS Excel MVP

"Gregory Winters" wrote in
...
Well, I guess this had to be a lot harder than I made it sound, huh? :-(
The code works GREAT. Problem is: I can't use it! I create a macro,
paste
the code into it, save it in the VBA editor, attempt to open the macro,
and...no macro! The name of the macro has vanished, but when I open the
Editor again and double click on the module, there is the code, right
where I
had pasted it.

I have never run across an instance where I have recorded a macro, then it
has literally disappeared. Ideas?

P.S. Harlan, conditional formatting is basically out of the question
since
it would force the users to scroll through ~45000 rows and 300 columns of
entries just to located those colored cells. Thanks, however, and I've
saved
your notes for the future.

Greg

"Bernie Deitrick" wrote:

Greg,

This will completely undo the entry:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False 'Optional undo
Application.Undo 'Optional undo
Application.EnableEvents = True 'Optional undo
Exit Sub 'Optional undo
End If
Next myCell
End Sub

This will just flag which entries were bad:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
End If
Next myCell
End Sub

and this will just remove the offending entries and notify, one by one:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
For Each myCell In Intersect(Range("C:C"), Target.EntireRow)
If Application.WorksheetFunction.CountIf(Range("C:C") , myCell.Value) 1
Then
MsgBox "Row " & myCell.Row & " now has a duplicate value. Please don't do
that."
Application.EnableEvents = False
Intersect(myCell.EntireRow, Target).ClearContents
Application.EnableEvents = True
End If
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP

Gregory Winters wrote...
....
Columns (call them 'B & C') simply represent finite lists
of values which could appear in any of the cells in their
respective columns: B representing a three-alpha abbreviation
of a plant, and C representing a four-digit model year.
There can be any combination of plants and model years,
but only one value per cell. Column A represents how we
need the concatenation of these two values to appear:
((Plant=ARL) AND (Model Year=2000))
....

If plant abbreviations were case-insensitive, there'd be 26^3
= 17576 different possible plant IDs. So the total number of
distinct combinations of plant ID and year would be 17576 times
the number of years. 3 years puts you well over 45000 possible
distinct combinations.

Understanding that columns B & C will likely be populated
via Copy & Paste and there could be as many as 45,000 rows,
the task at hand is to inform the user if there are values
anywhere in columns B and C which are not members of the
designated lists and allow the user to make appropriate
corrections (or simply delete the selection. . . .

Copy and paste from where?

What are the designated lists?

Letting users make corrections to tens of thousands of entries
is one of the ways I'd define user-hostile. Say there were
anomalies in 10% of rows, and it took users 10 seconds on
average to correct each anomaly. For 45000 rows, that's total
45000 seconds, or 12.5 hours (not including breaks). Drop the
anomaly rate to 1%, and that's still 1 hour 15 minutes.

That said, this sort of task (generating thousands of distinct
combinations) is MUCH, MUCH better done by computers than
humans. How would your users correct these anomalies? Just
randomly choose combinations not found elsewhere? If so, that'd
be MUCH SIMPLER for the computer to do.

. . . Column A will
then concatenate whatever is decided upon after the error
checking of B and C. As usual, blanks anywhere should be
considered deliberate and not a part of the actual error
checking, however if there is a value in either column B
or C, then the checker should alert the user that its partner
cell is blank. . . .

IOW, both columns B and C blank should be skipped, but if
either column isn't blank, blanks in the other SHOULD trigger
error checking.

. . . At that point, the user would either have
to supply the missing value, or delete the one entered.
Two blanks in a same row of columns B & C are permissible
as well as two valid values, but one value and one blank
and/or invalid values are not permitted. The values in
A do not have to be unique (obviously, with 45,000 entries,
this is not mathematically possible, anyway).

As shown above, just 3 years assures there are more than 45000
distinct possible combinations. As a purely mathematical
proposition, there could easily be more than a hundred
thousand distinct combinations with just 6 different years.

The piece in (call them columns F & G) is very similar,
only (for now) we simply have a single link to worry about.
There is a formula in Column F, we're desiring a simple
cell-to-cell reference (using CONCATENATE to avoid the display
of a zero). Again, the data in G will be the result of
a large Copy & Paste operation. The values in F must be
unique, and the error checker should cleanly call out the
duplicates - one by one - and allow the user to step into
the checking process and fix the corresponding values in
column G until all the values in F are indeed unique. Again,
blanks in G (and the resulting blanks in F) would be permitted.

How would your users be expected to 'fix' the nonunique
entries? If random replacement of values is OK, FAR BETTER to
let the computer do it than burden your users with the task.

"Harlan Grove" wrote:

If plant abbreviations were case-insensitive, there'd be 26^3
= 17576 different possible plant IDs. So the total number of
distinct combinations of plant ID and year would be 17576 times
the number of years. 3 years puts you well over 45000 possible
distinct combinations.

I realize that you didn't know this, but there are only 40 three-character
plant abbreviations and only 25 model years. These plant abbreviations are
not random three-letter combinations, either. Each stands for the city it is
in, such as LAX is the abbreviation for Los Angeles Airport.

Copy and paste from where?
What are the designated lists?

The Copy & Paste is coming from a pre-established data entry process (also
on an Excel spreadsheet) where the copied values are themselves summaries.
The sheets cannot be linked due to metaphysical restrictions. I realize that
Copy & Paste adds a difficult twist to the task, but it is the established
approved business process for the time being which cannot be changed. My
task is to validate the *results* of this Copy & Paste activity.

Letting users make corrections to tens of thousands of entries
is one of the ways I'd define user-hostile. Say there were
anomalies in 10% of rows, and it took users 10 seconds on
average to correct each anomaly. For 45000 rows, that's total
45000 seconds, or 12.5 hours (not including breaks). Drop the
anomaly rate to 1%, and that's still 1 hour 15 minutes.

Your math is good, but the vast majority of it has already transpired prior
to my context. The original spreadsheet has Data Validation and other
(simpler) error checking tools in it that eliminate 99% of the issues before
they ever get to my spreadsheet. The only thing I am faced with are a)simple
typos, or b) the occasional invalid calulcated value due to one or more
invalid input values. Believe me, the work isn't nearly as bad as your
original math would seem to make it. Besides, if there were indeed so many
errors to deal with, then the point about calling out the errors more
efficiently for the user would be moot.

That said, this sort of task (generating thousands of distinct
combinations) is MUCH, MUCH better done by computers than
humans. How would your users correct these anomalies? Just
randomly choose combinations not found elsewhere? If so, that'd
be MUCH SIMPLER for the computer to do.

Exactly, and that's my task at hand. Why, in heaven's name, that Microsoft
doesn't believe that results in cells need to be validated is simply beyond
me. After all, the whole purpose of a spreasheet is to provide the user with
the confidence of knowing that a 'computer' performed the task better than
s/he ever could dream of. Duplicate entries are a wholly common issue in a
vast array of human activities, ranging from holiday mailing lists to indexed
database ID's. The fact that in order to check for duplicates in calculated
cells a user is required to write programming code is, to me, ludicrous.

IOW, both columns B and C blank should be skipped, but if
either column isn't blank, blanks in the other SHOULD trigger
error checking.

The case of blanks, for me, is a non-issue. The data that is being
represented in my context is that of a database record, not so much a
numerical formula. (It's just that Excel naturally requires us to write
formulas to make it 'work.') A blank in a row - a *complete* blank, thus, is
not an error. It simply means that there were no criteria to provide the
target calculation.

How would your users be expected to 'fix' the nonunique
entries? If random replacement of values is OK, FAR BETTER to
let the computer do it than burden your users with the task.

The 'computer,' in this sense, is Microsoft Excel - which is a far cry from
a powerful relational database. Granted, this is how the customer is
attempting to use it, however, so a happy medium has to be reached. If the
Excel routine discovers a duplicate from a cell that was in a column of
values which was orignally a part of an input spreadsheet that is nearly 300
columns wide in terms of the relational attributes, only the user will be
able to determine depending upon the information s/he sees how to fix the
error.

A duplicate could very easily mean that the *original* member of the list is
incorrect and the so-called 'duplicate' is actually the authentic entry. Or,
the fact that there is even a duplicate at all could inform the user than
there is something wrong with values entered on the original sheet which
aren't even being ported to my worksheet, but are still causes of the
problem. And on and on...

Yes, I do wish that we hadn't gone so far down this path in Excel, but the
scripting tool that was created over three years ago to import this data was
written against Excel and modifying it is, unfortunately, out of the question.

Thanks,

Greg