Results 1 to 7 of 7

Thread: stepping (2000)

  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    stepping (2000)

    Hello, have a calculator on sheet 1 that then populates the outputs on a row on sheet 2. Is there a way when I redo the calculation that the outputs then populate the next row on the second sheet and so forth. So in short each time I calculate on sheet 1 the output steps down a row on sheet 2, without altering the previous rows.

    Thanks Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stepping (2000)

    Does your "calculator" involve VBA code? If so, what code?

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stepping (2000)

    Thanks Hans, no code, just cell formula.

    Darren.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stepping (2000)

    Cell formulas only work in the cell that contains them, they don't magically migrate to other cells. You'll need VBA code to do what you want. One possibility is a macro that is run by the user when needed. Another possibility is to write code in the Worksheet_Change event procedure in the worksheet module.
    If you'd like help with either of these options, we'd need to have more information.

  5. #5
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stepping (2000)

    Thanks Hans, help would be greatly appreciated, not done any VB with Excel. Here is a crude example of what I have. The user inouts figures in D8 and D9 on sheet one with the total in D10 copied through to sheet 2 D8. What I would like is for the user then to re input figures in sheet 1 D8 and D9 for the D10 reult then to be shown in sheet 2 D9, and so on without the sheet 2 D8, D9 , D10 and son on figures changing each time there is a step down to the next cell.

    Many thanks Darren.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: stepping (2000)

    You shouldn't have a formula in Sheet2.
    Right-click the sheet tab of Sheet1, and enter or paste the following code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lngRow As Long
    ' Has one of the cells that contribute to the total changed?
    If Not Intersect(Target, Range("D89")) Is Nothing Then
    ' Turn off event handling temporarily
    Application.EnableEvents = False
    ' Determine next available row
    lngRow = Sheets("Sheet2").Range("D65536").End(xlUp).Row + 1
    ' Copy total
    Sheets("Sheet2").Range("D" & lngRow) = Range("D10")
    ' Turn on event handling again
    Application.EnableEvents = True
    End If
    End Sub

    This will enter the total in the next available cell in column D on Sheet2 each time the user changes the value of D8 or D9 on Sheet1.

    See attached version.

  7. #7
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stepping (2000)

    Thanks Hans.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •