Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: shifting data down a row?==SOLVED thanks TsTom

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Maybe you can help me out with excel. Im using a dde topic to fill cell A1 with a number, 155 for instance. I need to send this value to cell B1 "155". When cell A1 changes "105" i need the new value to go to cell B1"105". And the old B1 value to go to B2 "155". When cell A1 changes again "215" I want the new value to go to Cell B1 "215" and the old B1 data to go to B2 the old B2 data to go to B3 "155" and so on. Any idea how I can accomplish this?

    A1 215 B1 215
    B2 105
    B3 155

    Each time A1 changes I need all the data in column B to shift down and insert the new data from A1 on top, leaving the oldest data at the bottom of the column and the newest data at the top.

    I could even put the first data in b1 the next in b2 then b3 and so on but the data has to come from A1 and move this data automatically.

    Thank you so much for your help,
    Marty Clevenger

    [ This Message was edited by: mcleve on 2002-05-19 19:26 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are you talking about Dynamic Data Exchange(DDE Linking)?
    I will wait for your reply because my answer to you will vary if you are using DDE.
    Thanks,
    Tom

  3. #3
    New Member
    Join Date
    May 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes Dynamic Data Exchange entry in cell A1.

  4. #4
    Board Regular
    Join Date
    May 2002
    Location
    Nederland
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try control-shift-=
    choose one option and you are done

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    In a sheet module (not a regular code module), enter the following

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address(False, False) = "A1" Then
            Range("B1").Insert Shift:=xlDown
            Range("B1") = Target
        End If
    End Sub
    TsTom -- I would really like to learn about the DDE linking, so please post your reply. I've never heard of it.

    Worksheet events can be messy, and if A1 changes through formula, this won't work without modification. It seems that you know a better way. That would be great to know.

    [ This Message was edited by: Jay Petrulis on 2002-05-04 08:18 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What is the frequency of your updates?

    Do you leave the workbook open and wish to have this automated? Or simply updated as
    the link is refreshed and when the workbook is initially opened?

    The reason I'm asking is this...
    The change event is not fired when a dde link is refreshed. You will need to link cell A1 to another cell and capture the update via the calculation event. If you are not famiiliar with VBA, do not be wearied by my explanation. It is a very simply problem to solve if all you are needing is what was described in your initial post.

    When these questions are answered, I or some-one else can paste the code in here or you can email the workbook, I'll code it out, and send it back...
    Tom

  7. #7
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    DDE is basically a second choice to Ole automation. Kind of a handshake between two applications. Many apps support it. Ole is much easier to use for us programmers, less demanding of resources, and faster as well. Go to MSDN and check it out if you'd like.
    There's bound to be some info there.
    Tom

  8. #8
    New Member
    Join Date
    May 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    A1 is updated when the operator of a machine presses the stop button on the machine. this data is gathered by a PLC (Programmable Logic Controller) and interfaced by RSlinx. I create a served DDE Topic in Rslinx to excel. When the operator presses stop A1 will get the current time ie. 12:21:15 AM. When the operator presses start the current time will be logged again to A1. A1 will be updated whenever the operator presses the start or stop button.

  9. #9
    New Member
    Join Date
    May 2002
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Do you leave the workbook open and wish to have this automated? Or simply updated as
    the link is refreshed and when the workbook is initially opened?

    Update when the value in A1 changes and when the workbook is initially opened.

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Insert this formula in cell A2
    =A1
    Right-click on your worksheet tab
    and choose view code.
    Paste this code in...(thanks for the code Jay)



    Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    If Range("A2") <> OldValueInA1 Then
    Range("B1").Insert Shift:=xlDown
    Range("B1") = Range("A1")
    OldValueInA1 = Range("A1")
    End If
    Application.EnableEvents = True
    End Sub



    Close the code window...

    Right-click on the Excel icon
    immediately left of the File menu item.
    Choose view code, paste this in.



    Private Sub Workbook_Open()
    OldValueInA1 = Range("A1")
    End Sub




    At the menu bar up top, choose insert,
    module.
    Paste this in this module:



    Public OldValueInA1



    That should square you up...
    Post again if any problems.
    Tom

    [ This Message was edited by: TsTom on 2002-05-04 08:31 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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