Debug error

[hopr37]

My spreadsheet takes totals and subtracts totals.

I have about 6 columns and rows that are filled with data.
If I try to insert a new row I get a debug error.
The debug highlights "Application.Undo"
Not sure why
any suggestions?



Private Sub Worksheet_Change(ByVal Target As Range)
'copies the value of colum c into new cell as "old value"
Dim nextRow As Integer
Dim oldValues As Variant
Dim newValues As Variant
Dim oldRev, newRev, diffRev As Double
Dim NumRows, NumCols As Integer
Dim lRow, lCol As Integer
If Not Intersect(Target, Range("C:C")) Is Nothing Then
NumRows = Target.Cells.Rows.Count
NumCols = Target.Cells.Columns.Count
newValues = Target.Value2
Application.EnableEvents = False
Application.Undo
oldValues = Target.Value2
Target.Offset(0, 1).Value = Target.Value
Target = newValues
Application.EnableEvents = True
End If

 

[hopr37]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Starting Number(A)[/TD]
[TD]Used Number(B)[/TD]
[TD]Previous Number(C)[/TD]
[TD]Starting Number(D)[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]95[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD]85[/TD]
[/TR]
</tbody>[/TABLE]


This is how it works.
starting number in column A doesn't change.
starting number on last column changes depending on the number input in the "used number" (column B)

placing the number 5 in the "used number" (column B) and hitting enter changes the starting number( Column D) from 100 to 95
If I were to change the "used column" number (column B) from a 5 to a 10 it would take the old value ( 5) and place it into column "previous number"( column C) AND would now subtract that amount from column D.

The way the table looks is not the actual way it appears. I would overwrite the number 5 with the number 10 in the "used number" (column B). I would not create a different row. I would just continue to overwrite any number in the "used number" ( column B)

 

[Joe4]

OK, rather than messing with that messy code, if you truly just want to copy any entries in column C to column D, and be able to handle populating cells by dragging text down, and inserting/deleting rows, try this code instead:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
        
    Set rng = Intersect(Target, Range("C:C"))
    
'   If no cells updated in column C, exit
    If rng Is Nothing Then Exit Sub
    
'   Loop through all updated cells in column C
    Application.EnableEvents = False
    For Each cell In rng
'       Copy value to column D
        cell.Offset(0, 1) = cell.Value2
    Next cell
    Application.EnableEvents = True

End Sub

 

[Joe4]

This is how it works.
starting number in column A doesn't change.
starting number on last column changes depending on the number input in the "used number" (column B)

placing the number 5 in the "used number" (column B) and hitting enter changes the starting number( Column D) from 100 to 95
If I were to change the "used column" number (column B) from a 5 to a 10 it would take the old value ( 5) and place it into column "previous number"( column C) AND would now subtract that amount from column D.

The way the table looks is not the actual way it appears. I would overwrite the number 5 with the number 10 in the "used number" (column B). I would not create a different row. I would just continue to overwrite any number in the "used number" ( column B)

OK, that is totally not the impression I get from your description up in post 8.
I will need to take another look at it.

How does the "dragging down" come into play here?
You mentioned that you really wouldn't have multiple rows, as shown in the previous example (that you just did that for illustration purposes).
So, do you have just one row of data, or multiple rows of data acting independently, using the behavior you described above?

I am guessing that they may have been trying to use "Application.Undo" to get the previous value, as that gets lost as you update that cell.
If you are allowing users to update multiple cells simultaneously (i.e. by dragging down), and want to get the previous values for ll those cells, that could get kind of messy. I don't think I would even attempt something like that. If I wanted to capture the previous value, I think I would look at forcing them to enter data through Forms or Prompts, and not update the data directly on the sheet. Then you control the whole flow of things and can capture values before they disappear.

 

[hopr37]

The dragging down was just an example of when the debug issue came up. It really started when I tried to add new rows. I was just trying different things to see when the debug error popped up.

 

[hopr37]

adding new rows is a possibility.
If I add more inventory I would need to add more rows.
I wish I could get you my spreadsheet.

 

[Joe4]

Reiterating the last paragraph in my last post:

I am guessing that they may have been trying to use "Application.Undo" to get the previous value, as that gets lost as you update that cell.
If you are allowing users to update multiple cells simultaneously (i.e. by dragging down), and want to get the previous values for all those cells, that could get kind of messy. I don't think I would even attempt something like that. If I wanted to capture the previous value, I think I would look at forcing them to enter data through Forms or Prompts, and not update the data directly on the sheet. Then you control the whole flow of things and can capture values before they disappear.

You may want to consider approaching it from one of those different angles, unless we can ensure that they would never be updating more than one row at a time (i.e. don't use drag-down or copy/paste to multiple cells in column B).

 

[rlv01]

I would probably not approach the problem the same way, but If you just want to keep the same basic method, then a cleaned up version like this might work

Private Sub Worksheet_Change(ByVal Target As Range)
'copies the value of colum c into new cell as "old value"
    Dim ColC_Data As Range
    Dim Msg As String
    Dim Ans As Integer

    Set ColC_Data = Range("C4", Range("C" & Rows.Count).End(xlUp))

    If Not Intersect(Target, ColC_Data) Is Nothing Then
        Msg = "Used footage: " & Target.Value & " ft." & vbCr
        Msg = Msg & "Remaining footage would be: " & Target.Offset(0, 2).Value - Target.Value & " ft." & vbCr & vbCr
        Msg = Msg & "Accept this change?"
        Ans = MsgBox(Msg, vbYesNo + vbDefaultButton1)
        Application.EnableEvents = False
        If Ans = vbYes Then
            Target.Offset(0, 1).Value = Target.Value    'set col D as old value
            If Target.Value > 0 Then
                Target.Offset(0, 2).Value = Target.Offset(0, 2).Value - Target.Value    'subtract col C value from existing col E as some kind of running total
            End If
        Else
            Target.Value = Target.Offset(0, 1).Value
        End If
        Application.EnableEvents = True
    End If

It if it was me, I'd add a confirmation step like the example above since once the last column is updated, backing out because of a data entry error would be a pain.

 

[Joe4]

rlv01,

I don't think that does what they want, as expressed up in post #11 , especially the part about placing the previous value in column C:

placing the number 5 in the "used number" (column B) and hitting enter changes the starting number( Column D) from 100 to 95
If I were to change the "used column" number (column B) from a 5 to a 10 it would take the old value ( 5) and place it into column "previous number"( column C) AND would now subtract that amount from column D.

 

[rlv01]

rlv01,

I don't think that does what they want, as expressed up in post #11

You could be right, since the problem statement is maybe not as clear as I would like. I did take a look at the google drive spreadsheet and it differs a bit from the example posted in #11 . It's an application to track some kind of material like lumber, pipe, or wire that is measured in feet. The users enters how many feet is being pulled from inventory into a cell in col C, which triggers the worksheet change event. The code then stores the col C value in col D as a saved "previous" value, and then subtracts the col C amount from col E, and stores it back in col E as a "how much is left" number. Presumably col E starts out at the 100% inventory number.

 

[hopr37]

You could be right, since the problem statement is maybe not as clear as I would like. I did take a look at the google drive spreadsheet and it differs a bit from the example posted in #11 . It's an application to track some kind of material like lumber, pipe, or wire that is measured in feet. The users enters how many feet is being pulled from inventory into a cell in col C, which triggers the worksheet change event. The code then stores the col C value in col D as a saved "previous" value, and then subtracts the col C amount from col E, and stores it back in col E as a "how much is left" number. Presumably col E starts out at the 100% inventory number.

That is exactly how it works. But like I said, if I try to enter an extra row I get a debug error