ByVal Target Range Great Code but need Help
Bernie, Thank you. I screwed this all up. Let me see if I can un-FOG my brain.
In you guys MVP's formula ..I will repost I would like this:
In my formula I would like:
I messed this up, mostly because I have made a change.. Please let me restate:
I would like M Column M4:M100 to be the +/- the numbers down I4:I100 "I"
column is driven by what I type in H.
L is the target L4:100
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
AS I4:I100 gets a new #, L changes. (it's and average)
Can you help me make (M4:100) be +/-) I column)
I would like the old M4:M100 to move to N4:N100,
Then the old N4:N100, to move to O4:O100,
and Old O4:O100 drops off.
My formula is this presently:
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
If Target.Column = 12 Then
Cells(Target.Row, 17).Value = _
Cells(Target.Row, 17).Value + _
Cells(Target.Row, 13).Value
End If
ws_exit:
Application.EnableEvents = True
End Sub
Thank you Mark
I see no action in it may be because I have compressed the sheet in to fit
on on portrait page and I did not correct where you need to hit the target.
May I please repeat.
"Bernie Deitrick" wrote:
Mark,
After this line
Target.Clear
Add this:
If Target.Column = 12 Then
Cells(Target.Row,17).Value = _
Cells(Target.Row,17).Value + _
Cells(Target.Row,13).Value
End if
--
HTH,
Bernie
MS Excel MVP
"Mark" wrote in message
...
Topper, I am modified to 4:100 (across the board) had to put some titles in!!
Just friggin Brilliant! It may appear that I am freeloading, but, I am
passing along what you are helping me with:
QUESTION: to the code you sent!!!!: can we add.... In Column Q4:Q100 the +/-
amount. (Which is the +/- of the figure that ends up in M4:M100) ?
To clarify, when I enter a figure in L4:L100 and hit enter it all jumps
right. My last of the 3 entries in that area are in M column.
Q4:Q100 column is needed to show a +/- of (M4:M100)
Thanks for everything.
mark
"Toppers" wrote:
Use this code:
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100, L1:L100"
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
ws_exit:
Application.EnableEvents = True
End Sub
"Mark" wrote:
I entered Const WS_RANGE As String = "B1:B100, L1:L100"
When I write to L3 it copies to C4
I sorta want the L3 group to write to L4 as B3 writes to B4
Almost there.. What wonderful help..
Thank you.
Mark
"Toppers" wrote:
To execute another range:
change to:
Const WS_RANGE As String = "B1:B100, L1:L100"
Date must be ENTERED into L1:L100 as copy/paste doesn't invoke the code.
"Mark" wrote:
DANG! BERNIE!
If I could give you more than one Perfect rating I would Thank you, Thank
You, Thank You
Question. I have a duplicate of this on sheet 2:
Is there anyway to merger the 2 sheets. That is ,as if I would copy (sheet2)
From A1 to J100 and paste it into sheet 1 around L1? Can you run both on one
formula or must the entire thing be redone?
Thanks anyway fanfriggin tastic!
Mark
"Bernie Deitrick" wrote:
Sorry it didn't work : The code I have works acrros B3
It did not work in any cell between B4:b100
It does work, on every cell in the range B1:B100, as long as
1) you enter a value into a single cell
2) you enter a number
If you want to lift the single cell restriction, then remove
Or _
Target.Cells.Count 1
HTH,
Bernie
MS Excel MVP
|