Realtime Graph

[Alectrical]

Hi

I have a cell with a DDE link from another program, which is sample data I
need to produce a real time graph of.

To do this I need to copy the value of the cell, A1, and paste it into B1, a
second later I need to copy the value of A1 into B2 and so on. I can then
produce a real time graph from the information in column B.

Any ideas

Thanks in advance.
Alec

 

[Alectrical]

Sorry, it should read:

I have a cell with a DDE link from another program, which is sample data I
need to produce a real time graph of.

To do this I need to copy the value of the cell, A1, and paste it into B1,
when the cell value changes I need to copy the new value from A1 into B2 and
so on. I can then produce a real time graph from the information in column B.


Sorry, it should read

 

[John Mansfield]

This VBA macro should work. Copy the following procedure to the sheet module
in which your data resides . . .

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim Rng As Range
Set Rng = Range("B65536").End(xlUp)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Rng.Offset(1, 0).Value = Range("A1").Value
End If
End Sub

 

[John Mansfield]

I apologize. Here's a slight modification to the code I just posted.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Rng As Range

Set Rng = Range("B65536").End(xlUp)

If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("B1").Value = vbNullString Then
Range("B1").Value = Range("A1").Value
Else
Rng.Offset(1, 0).Value = Range("A1").Value
End If
End If

End Sub

 

[Alectrical]

Thanks John,

However, I could not get it to work. I created a module as you suggested and
pasted the code in. My link in A1 is changing every second, but I am getting
nothing in column B.

Including "Option Explicit" also makes no difference.

The version of excel I am running is 2003.

 

[Alectrical]

I apologize. Here's a slight modification to the code I just posted.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Rng As Range

Set Rng = Range("B65536").End(xlUp)

If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("B1").Value = vbNullString Then
Range("B1").Value = Range("A1").Value
Else
Rng.Offset(1, 0).Value = Range("A1").Value
End If
End If

End Sub

 

[Alectrical]

I apologize. Here's a slight modification to the code I just posted.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim Rng As Range

Set Rng = Range("B65536").End(xlUp)

If Not Intersect(Target, Range("A1")) Is Nothing Then
If Range("B1").Value = vbNullString Then
Range("B1").Value = Range("A1").Value
Else
Rng.Offset(1, 0).Value = Range("A1").Value
End If
End If

End Sub