Change cells to the right for as much as the current cell was changed

Question

I have a train arrival/departure timetable, each branch line is on a separate row. I need to make the spreadsheet automatically detect change in cell and change all cells to the right from it for the same amount of time. Cells have format of time. How do I do that?

What I've tried so far:

I found this piece of code in some other question on SE:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Target.Worksheet.Range("H5")) Is Nothing Then Macro
End Sub

But I wasn't able to understand where to put it and how to make it work automatically, apart from working for a specific range, which might be different from what was in the question where I found it.

UPD: The logic I'm looking for in VBA:

Wait until a cell is selected, if it has Time format, copy its value to Tmp.
Save the difference between old and new values to Tmp.
If a cell to the right contains something and its format is Time, add Tmp to it.

Continue until cell is empty.

'How do I do that?` -> you can either write a formula, or use the 'Worksheet_Change` event in VBA. If you would like more specific help, please post the code (or formulas) you have tried and we can help get you [up to speed!](http://www.stackoverflow.com/faq) — Scott Holtzman, Dec 17 '12 at 19:50
What have you tried? How is the cell being changed - by a calculation/formula or directly by editing? — Tim Williams, Dec 17 '12 at 19:50
@ScottHoltzman I'm not that familiar to VB as much as C#, and all I tried, I couldn't even make to work. Adding examples of what I've tried so far would be more confusing (and possibly embarassing) than helpful. — user1306322, Dec 17 '12 at 19:54
Then you can use Worksheet_change as suggested by Scott. Hard to be more specific without more details. — Tim Williams, Dec 17 '12 at 19:55
From what I know about programming, there should be a universal method to find out which cell was changed and for how much, and I'm sorry I can't be of more help finding a solution to this problem. — user1306322, Dec 17 '12 at 19:59
while what you have tried may be confusing to you ... it probably won't be for us. If you post your efforts, we can modify and then you can *learn* by what you've done. — Scott Holtzman, Dec 17 '12 at 19:59
http://stackoverflow.com/questions/4668410/how-do-i-get-the-old-value-of-a-changed-cell-in-excel-vba will get you the old value of a cell, then the worksheet change event will let you see how much a cell has changed by — SeanC, Dec 17 '12 at 20:42

score 0 · Answer 1 · answered Dec 17 '12 at 20:03

0

if you know how much difference is between times, then you can calculate the new time based on that difference.

e.g. A1 is 10:07, A2 is 10:14. Instead of having to type in each time individually, you could have A2 as =A1+TIME(0,7,0). Then when you changed A1 to 10:15, A2 would automatically change to 10:22

answered Dec 17 '12 at 20:03

SeanC

15,695
5
45
66

Problem is I already have a timetable with a **lot** of cells with times typed in manually, should I retype them all as a formula, it would take a whole lot of time, so I'd like to automate that process without manually retyping all cells. – user1306322 Dec 17 '12 at 20:04

score 0 · Answer 2 · answered Dec 17 '12 at 21:58

OK this may get you started in the right direction:

You first want to store all the original cell values. So, the following VBA code stores the values in column A for the first 200 rows into an array. You need to run this code first, perhaps when the workbook is opened:

Dim contents(200) As Variant
Public Sub StoreOriginalValues()
    ' save all existing values
    For r = 1 To 200  ' change for number of rows you have
        contents(r) = Worksheets(1).Cells(r, 1).Value
    Next
End Sub

Once the original cell values are stored, you can place code in the Worksheet_Change event so that whenever the user changes a cell in column A you can compare the original and new value and figure out the difference. Then you can apply this difference to the rest of the columns in that row:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then  ' only check for changes in column A
        originalvalue = contents(Target.Row)
        newvalue = Target.Value
        contents(Target.Row) = Target.Value

        difference = newvalue - originalvalue
        Set chgcell = Cells(Target.Row, Target.Column + 1)

        Do While Not IsEmpty(chgcell)
            chgcell.Value = chgcell.Value + difference
            Set chgcell = chgcell.Offset(0, 1)  ' move one column to right
        Loop
End If

End Sub

Now this code is by no means perfect. It does not check that they entered a valid time, for instance. It also does not check to see if the values entered in the rest of the columns in the row are times or text or whatnot. But like I said I hope it will point you in the right direction.

score 0 · Accepted Answer · answered Dec 17 '12 at 23:15

My solution:

Dim oldVal
Dim diff

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oldVal = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
ActiveCell.Offset(-1, 0).Select
Application.EnableEvents = True
diff = Target.Value - oldVal

If Not diff = 0 Then
While Not ActiveCell.Offset(0, 1) = "#"
Application.EnableEvents = False
ActiveCell.Offset(0, 1).Select
Application.EnableEvents = True

If Not ActiveCell Is Nothing _
And Not ActiveCell = "" _
And TypeName(ActiveCell) = TypeName(ActiveCell.Offset(0, -1)) Then

Application.EnableEvents = False
ActiveCell.Value = ActiveCell.Value + diff
Application.EnableEvents = True

End If
Wend
End If

End Sub

This has been my first experience with VB in a very long time, so the code is terrible, but it works.

Change cells to the right for as much as the current cell was changed

3 Answers3