Increase current cell ONLY when different than target cell

[Kemosabe]

I am looking for a way to avoid the circular reference issue. I would like to monitor a cell that is being incremented and decremented so that I can automatically retain the highest and the lowest values that were entered into that specific cell over time. Since the data is only entered into that specific cell and not retained in for example a column, the MAX/MIN option is not usable in this case.

More simply stated, is there a way to put a formula in a specific cell ( A1 ) that will equal the target cell ( B1 ) only when that target cell ( B1 ) is greater than A1?

Thank you.

 

[Boller]

HOTPEPPER

Since the data is only entered into that specific cell
Given the above, it should work fine.

Your faith in users of Excel is remarkable.

In any event, the "Intersect" code takes care of it, so why not use it?

you don't have to check if there are more than 1 cells in Target, if you are checking for the address of a single cell, as it won't return a single cell address if more than one cell has been changed.

That's exactly why just checking for a single cell is inadequate - it means that the cell can be changed without the macro being triggered.


I note that you quite often use "If Not Intersect(Target,[A1]) Is Nothing Then".
So in what circumstances would you not use it?

 

[Scott Huish]

Your faith in users of Excel is remarkable.

Excuse me? Why do you think the op is lying? What would the benefit to the op be to lie?

I note that you quite often use "If Not Intersect(Target,[A1]) Is Nothing Then".

I hardly ever write code like that. First I prefer the Range("A1") format to the bracket format. I also don't like an IF then a lot of code with an End If at the end. I like to just get out of the code if it's not necessary to continue.

Also the disadvantage to that format is that you would have to forever refer to the range you are working with as Intersect(Target,[A1])

I would do it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = Intersect(Target, Range("B1"))
If c Is Nothing Then Exit Sub
Application.EnableEvents = False
    If c > Range("A1") Then Range("A1") = c
Application.EnableEvents = True
End Sub

I note that you quite often use "If Not Intersect(Target,[A1]) Is Nothing Then".
So in what circumstances would you not use it?

When I only want the code to execute if a specific cell and ONLY that specific cell changes.

 

[Boller]

"Your faith in users of Excel is remarkable."

Excuse me? Why do you think the op is lying? What would the benefit to the op be to lie?

The OP may or may not change the cell via a multi-cell input at some time in the future - but that is not the issue.

The fact that he might do so (even inadvertly) can be covered by appropriate code.
The OP, or anyone else who might use the code, may not be aware that the code you and others posted does not handle multi cell changes.

I hardly ever write code like that

I can only assume that you are deliberately choosing to ignore the point.
The point was re the use of "Intersect". It was not about how you normally construct your code.

When I only want the code to execute if a specific cell and ONLY that specific cell changes.

Would it not be advisable to spell this out when posting code that does this?
There must be many readers of this board that are not aware of the narrow criteria of such code.

 

[lenze]

I agree with HOTPEPPER!! I use to work for the Governer's office, designing and fixing SS for instructionally challenged politcal appoimtees. I found quickly that the simpler the better.

If Target.Address <> "$A$1" Then Exit Sub

reads like plain english while

If Not Intersect)Target,[A1] is Nothing then

would mean nothing to a OP with little or no VBA knowledge.
Which of these lines would the OP best be able to explain to co-workers or the boss?
I too, also like to avoid the If--End If format if possible.

lenze

BTW: I have never seen an instance where I wished the code to fire if multi cells were changed

 

[Boller]

I agree with HOTPEPPER!! I use to work for the Governer's office, designing and fixing SS for instructionally challenged politcal appoimtees. I found quickly that the simpler the better.

If Target.Address <> "$A$1" Then Exit Sub

reads like plain english while

If Not Intersect)Target,[A1] is Nothing then

would mean nothing to a OP with little or no VBA knowledge.
Which of these lines would the OP best be able to explain to co-workers or the boss?
I too, also like to avoid the If--End If format if possible.

lenze

BTW: I have never seen an instance where I wished the code to fire if multi cells were changed

Your whole post is beyond belief.

It is not a matter of If Target.Address <> "$A$1" Then being simpler or better than If Not Intersect(Target,[A1]) Is Nothing Then.

They do not always do the same thing.

I have never seen an instance where I wished the code to fire if multi cells were changed

Oh really?
Here's a fairly common requirement :-
http://www.mrexcel.com/board2/viewtopic.php?t=285028&highlight=upper

 

[Boller]

lenze

Here's another example that may look familiar :-

Occasionally, there is a need to limit entries to only one cell in a group of cells. This technique demonstrates how to make a group of 3 cells mutually exclusive.

Private Sub Worksheet_Change(ByVal Target As Range)
'This event will make a group of three cells mutually exclusive
Dim rng As Range
Set rng = Intersect(Target, [A2,A4,A6])
Application.EnableEvents = False
If Not rng Is Nothing Then
    If rng.Count <> 1 Then
        MsgBox "For cells A2,A4 and A6, only one cell at a time can be changed."
        Application.Undo
        GoTo e
    End If
Else
    If rng.Address = "$A$2" Then [A4,A6].ClearContents
    If rng.Address = "$A$4" Then [A2,A6].ClearContents
    If rng.Address = "$A$6" Then [A2,A4].ClearContents
End If
e: Application.EnableEvents = True
End Sub

 

[Scott Huish]

In that instance above your last post, it is looking for changes in a whole column and needing to do something with every cell that changed, not just one cell. This is exactly how I would usually use the Intersect method.

It's not the suggestion though that I had issue with, it's the implication that everyone else did a poor job who suggested for just checking for the one cell, then coupled with the sarcastic remark that implied I was stupid for answering what the op asked.

Then you proceeded to critique my code with code I didn't write. If you had made a suggestion instead of picking on everyone, it would've been much better.


That being said, your code generates an error if anything is entered outside the range of A2, A4, or A6

but this should work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Set c = Intersect(Target, Range("A2,A4,A6"))
If c Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Target.Address
    Case "$A$2"
        [A4,A6].ClearContents
    Case "$A$4"
        [A2,A6].ClearContents
    Case "$A$6"
        [A2,A4].ClearContents
    Case Else
        MsgBox "For cells A2,A4 and A6, only one cell at a time can be changed."
        Application.Undo
End Select
Application.EnableEvents = True
End Sub

 

[Boller]

HOTPEPPER

It's not the suggestion though that I had issue with, it's the implication that everyone else did a poor job who suggested for just checking for the one cell, then coupled with the sarcastic remark that implied I was stupid for answering what the op asked.

Then you proceeded to critique my code with code I didn't write. If you had made a suggestion instead of picking on everyone, it would've been much better.

Sarcastic remark? Implied ....stupid? Picking on everyone?
Where does all that stuff come from?

I shall continue to draw attention to whatever I consider to be poor answers to posts, even if you see it as picking on everyone.

Also, I did indeed make a suggestion for amending the poor code - so I guess it actually was much better.

In that instance above your last post, it is looking for changes in a whole column and needing to do something with every cell that changed, not just one cell. This is exactly how I would usually use the Intersect method.

That example was for lenze who said "I have never seen an instance where I wished the code to fire if multi cells were changed"

But anyway, you are still ignoring the fact that restricting an event procedure to checking that the Target consists of one cell only or has a particular one cell address, and then exiting if it is more than one, does not deal with all the other possible ways that relevant cell/cells can be changed.

That being said, your code generates an error if anything is entered outside the range of A2, A4, or A6

Thanks for pointing out the error in my code (or are you just picking on me? ).
Your code, however, does not allow a value to be dragged/dropped to a single cell in the relevant range.
I suggest :-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Intersect(Target, [A2,A4,A6])
If rng Is Nothing Then Exit Sub
Application.EnableEvents = False
If rng.Count <> 1 Then
    MsgBox "For cells A2,A4 and A6, only one cell at a time can be changed."
    Application.Undo
    GoTo e
Else
    If rng.Address = "$A$2" Then [A4,A6].ClearContents
    If rng.Address = "$A$4" Then [A2,A6].ClearContents
    If rng.Address = "$A$6" Then [A2,A4].ClearContents
End If
e: Application.EnableEvents = True
End Sub

 

[Scott Huish]

Your code, however, does not allow a value to be dragged/dropped to a single cell in the relevant range.

Sure it does, did you try it?

Sarcastic remark? Implied ....stupid? Picking on everyone?

Well, let's see you told lenze his whole post was beyond belief. My faith in Exel users is remarkable. I really don't think you meant that in a nice way, that's what sarcasm is.

particular one cell address,

Your code does it three times.
If rng.address=

But anyway, you are still ignoring the fact that restricting an event procedure to checking that the Target consists of one cell only or has a particular one cell address, and then exiting if it is more than one, does not deal with all the other possible ways that relevant cell/cells can be changed.

No, I'm not. I never said using Intersect was wrong. I just though for this particular case, just checking the one cell would be OK.

But you do not appear to have considered the possibility that someone might want to only execute the code if only 1 cell was changed, even though it might be part of a larger range. I believe there is a time and place for both methods.

 

[Boller]

Sure it does, did you try it?

Yes, I tried.

And it does not allow drag/drop because the Target.Address will not match one of the relevant cells.

Change it to :- Select Case c.Address

Regarding the rest of your post, we've reached the stage where we're flogging it to death.