shifting data down a row?==SOLVED thanks TsTom

[mcleve]

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 ]

[Tom Schreiner]

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

[mcleve]

Yes Dynamic Data Exchange entry in cell A1.

[XiniX]

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

[Jay Petrulis]

Hi,

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

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 ]

[Tom Schreiner]

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

[Tom Schreiner]

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

[mcleve]

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.

[mcleve]

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.

[Tom Schreiner]

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 ]