ByVal Target Range Great Code but need Help

  • Thread starter Thread starter Guest
  • Start date Start date
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
Click to expand...


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
 
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
 
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
 
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
 
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
 
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
 
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.
 
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
 
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?
 
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
 
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
 
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
 
Back
Top