ByVal Target Range Great Code but need Help

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

Mark,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B100")) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Or _
Target.Cells.Count 1 Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite old values
Application.EnableEvents = False
Target.Resize(1, 3).Cut Target.Offset(0, 1)
Application.EnableEvents = True

End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
Row = Target.Row
Range("c" & Row).Resize(1, 2).Copy Range("d" & Row)
'overwrite C3 with B3
Range("C" & Row) = Target
'clear B3 for tomorrow
Target.ClearContents

End If
ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

Sorry it didn't work : The code I have works acrros B3

It did not work in any cell between B4:b100

Your assistance is hugely appreciated

Mark

"Bernie Deitrick" wrote:

Mark,

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("B1:B100")) Is Nothing Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Or _
Target.Cells.Count 1 Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite old values
Application.EnableEvents = False
Target.Resize(1, 3).Cut Target.Offset(0, 1)
Application.EnableEvents = True

End Sub


HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

Topper formula starts working down the line then quits working. When I go
back up to B# it doesn't ask "Yes" just stops.

Very close. I thought you had it...something stopped


"Toppers" wrote:

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
Row = Target.Row
Range("c" & Row).Resize(1, 2).Copy Range("d" & Row)
'overwrite C3 with B3
Range("C" & Row) = Target
'clear B3 for tomorrow
Target.ClearContents

End If
ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

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

Try:

Incoroprated some of Bernie's better code!

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "B1:B100"


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).Cut Target.Offset(0, 1)



ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

Topper formula starts working down the line then quits working. When I go
back up to B# it doesn't ask "Yes" just stops.

Very close. I thought you had it...something stopped


"Toppers" wrote:

try:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B100"


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
Row = Target.Row
Range("c" & Row).Resize(1, 2).Copy Range("d" & Row)
'overwrite C3 with B3
Range("C" & Row) = Target
'clear B3 for tomorrow
Target.ClearContents

End If
ws_exit:
Application.EnableEvents = True
End Sub

"Mark" wrote:

I am getting help, yet I need further help. The formula I post below, works
for B3 and to the right. I need it to work exactly the same from B3 thru B100
and have numbers in the entire row work as B3 does . That is B49 moves to
C49, C49 moves to D49 etc.

Do I need a code for each line? That would seem horribly wrong.
Thank you: Check this. Works great for one line B3. It's a great code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address "$B$3" Or _
IsEmpty(Target) Or _
Not IsNumeric(Target) Then
Exit Sub
End If
If MsgBox("Use the new value " & Target & _
" as new Daily Entry?", vbYesNo + vbDefaultButton1 _
+ vbInformation, "Verify Entry") vbYes Then
Target.ClearContents
Exit Sub
End If
'overwrite E3 with D3
Range("E3") = Range("D3")
'overwrite D3 with C3
Range("D3") = Range("C3")
'overwrite C3 with B3
Range("C3") = Target
'clear B3 for tomorrow
Target.ClearContents
End Sub

Need B1:B100 to work this way

I enter a number in B3 sometimes down to B100

I need that # to go to C3, I need C3 to overide D3 and D3 ro overide e3,
basically continue to track the last 3 entries.

My formula only works for B3

Additional info F3 is =average(C3:E3)

"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

If you mean that whatever is entered in a cell in B1:B100 get put onto row 3 only, then you could
replace

Target.Resize(1, 3).Cut Target.Offset(0, 1)

with

Target.Copy
Range("C3").Insert Shift:=xlToRight
Range("F3").Clear 'This is optional
Target.Clear

HTH,
Bernie
MS Excel MVP


"Mark" wrote in message
...
I enter a number in B3 sometimes down to B100

I need that # to go to C3, I need C3 to overide D3 and D3 ro overide e3,
basically continue to track the last 3 entries.

My formula only works for B3

Additional info F3 is =average(C3:E3)

"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

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