ByVal Target Range Great Code but need Help

[Bernie Deitrick]

Mark,

ie: If L20 is 25 and I4 is 18 then M4 is +7 or 7
If L4 is 20 and I4 is 14 then M4 is -6

Where are you getting the +7 / - 6 ?

I'm still not sure what you want.

L is column 12.
M is column 13.
Q is column 17.
Target is the cell that was changed.

This code means:

' only do this is the cell that changed is in column L
If Target.Column = 12 Then
' Change the value in column Q
' by taking the original value in column Q
' and adding the value in column M
' all in the same row as the changed cell
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If

give an example of values in one row, and what you would type, with before and after values....

HTH,
Bernie
MS Excel MVP

 

[Mark]

I'm truly sorry Bernie, Please don't give up on me., I would hate to aliente
such wonderful help.

In my code: L column is an average of I,J,k
=AVERAGE(I4:K4)
If L is the target, whatever # is in "I" (not the average) the actual # in
"I" minus L goes in M.

I4 = 18, L4 = 24, M changes to -6
I4 = 28, L4 = 10, M change to +18 or 18

thats the first step.

At present I am getting some sort of average in M. I think it is because of
the code. I don'y know. When I4 is 16 and L4 is 18, I'm getting 1.7

go figure.

Thank you
mark

 

[Bernie Deitrick]

Mark,

You're not going to alienate us, ever, unless you start abusing us.

What column are you actually changing? Since L has a formula, you probably aren't typing any values
in that column. Or are you?

Why not use the formula =L4-I4 in cell M4?

Still not sure what you are doing...

HTH,
Bernie
MS Excel MVP

 

[Mark]

ok fixed that..

Went in and changed decimal to none. It is rounding on its own I guess.

2nd step.

As a new number comes into I4 Assuming (I4:I100) L changes (because it is
an average of I,J,K.
M gets the +/-

All good.

When M gets a new total
I would like Old M to move to N
Old N to O
Old O move to trash.

Looks Like this:

M N O
-4 6 3 to:

M N O
2 -4 6

M N O
-3 2 -4

 

[Mark]

On my sheet:

I enter a number in H (assuming I have 100 lines) it asks me to confirm and
when I say yes it moves to "I". The Old I moves to J, J to K and "L" is the
average of I,J,K.

M is now key! =(I4-L4)

As M changes. (with an entry into H)
I would like the old or M to move to N, N to O, and Old O drop off.

In M,N,O I am trying to track, the last 3 + 0r minus's. They shift right. As
does I,J,K when I put something in H.

Did I confuse the issue more. I'll stop. and try agin later.

mark, Thank you

My code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then

GoTo ws_exit


End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear


ws_exit:
Application.EnableEvents = True
End Sub

 

[Bernie Deitrick]

Mark,

Give the version below a try.

HTH,
Bernie
MS Excel MVP


Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then
Target.ClearContents
GoTo ws_exit
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Mark]

That code works for b-L

Nothing on M,N or O

Here'a a deeper problem, one I was not smart enuff to see until now.

Example the code:
calculates for F column as C,D,E shifted.
It caculates a new L column as I,J,K shifted.

I wanted M to be plus or minus L.
The figure "THAT WAS" in L....
As I enter to make a new L
M is plus or minus the new L.
M needs to be plus or minus the # that was there, not the new number. It's a
score that must be matched. And if you do not score old L M needs to show how
much it was missed by.

It looks like now. I've been + or - the NEW L not the OLD L. Thats wrong.
You didn't know that, and neither did I till jsut now.

Further example.
the # in L was the score I had to make.
M needs to reflect if I made it or not.

When I enter a new # and L changes. M reflects the wrong # It reflects the
+/- of the score I need to make next not what I made against Old l.

Lets say my number was 20. in L ok?
I made16. M would be -4 right? ok Thats IS the problem!

Problem.. if I post the 16.. L changes,
lets say to 19 M now says -3
when it needs to" say -4" which was the last number I had to score.
Do you follow that. (I've messed this all up.).
M as I have been stupidly doing would calulate on the new L not the OLD L.

Mark

I give....
Bernie:
What on earth am I looking for?
M = + or - the number that was in L before I enter the new one.
I'm truly sorry. Maybe I need another column. One that retains the 2nd to
last L number. M calculates on that. Then as I enter numbers the 2nd to last
L is calculated by M.

Geez. Please feel free. to go to lunch and just drop me. I'm WAAAAAY over my
head and see the problems only when they pop up.

 

[Bernie Deitrick]

Mark,

Try this one:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub


HTH,
Bernie
MS Excel MVP

 

[Mark]

Bernie, Unbelievable.. Your code is perfect!

Thank you.

Now. of course ......there is always a now...isn't there?

L reads the score needed , lets say thats is 20
If I score 16 that would be -4. the -4 would history right from M,N,O.
However, if I enter 16, (L changes to average 19) and M reads -3 which is
incorrect. I scored 16 which is -4.

I love the +/- history moving in M,N,O But I guess M is sorta wrong because
M is reading L which has the new score in it.(16) -3.

Ideas?????

Mark




Obviously the question is If L was 20 and m should be -4 as soon as I enter
todays score M changes and reads todays difference -3
M reads the plus or minus. Histories shift.. Perfect.
If L reads 20 and I score 16 M should be -4.

However on entering 16 L changes to 19(average) and M reads -3 rather than
-4 which L was.

Any thoughts without loosing that fantastic shift history you have working?

 

[Bernie Deitrick]

Mark,

Certainly not perfect, but we'll get there....

Try this one, below.

HTH,
Bernie

MS Excel MVP

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"
Dim myNewVal As Variant

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then
Target.ClearContents
GoTo ws_exit
End If

myNewVal = Target.Value
With Application
.Undo
.CalculateFull
End With
If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If
Target.Value = myNewVal
Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub

 

[Mark]

Bernie.. I HAVE IT.. THEE QUESTION.

In this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B4:B100, H4:H100"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
GoTo ws_exit
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") <> vbYes Then
Target.ClearContents
GoTo ws_exit
End If

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

Target.Resize(1, 3).Copy Target.Offset(0, 1)
Target.Clear

ws_exit:
Application.EnableEvents = True
End Sub

Is it possible to create a hot key..
That inserts a column left of M, copy L4:L100 and paste special "VALUE" to
M4:M100.

Answer =No.. I insert one.
I copy L4:L100 paste special VALUE to M4:M100

If yes" ok, then
Move M,N,O to N,O,P

N4:N100 is now =I4-M4

If NO:
I can't see how code could get us that "paste special VALUE to M. So I quit
and will do the rest by hand..

Have a good night sir..

Mark

 

[Bernie Deitrick]

To create a 'hot key' with an event is difficult, but you could use a 'hot value' that you enter in
the cell to trigger some other code:

Replace this:

If Target.Column = 8 Then
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If

with this


If Target.Column = 8 Then
If Target.Value = "M" Then
Range("M:M").Insert
Range("L:L").Copy
Range("M:M").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
Else
Cells(Target.Row, 15).Value = Cells(Target.Row, 14).Value
Cells(Target.Row, 14).Value = Cells(Target.Row, 13).Value
End If
End If

Then enter M into a cell in column H to create the copy of column L....

HTH,
Bernie
MS Excel MVP