I don't know anything about DDE--but maybe you can tie into a calculation event:

Option Explicit
Private Sub Worksheet_Calculate()

Dim myRng As Range
Dim myCell As Range

With Me
Set myRng = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

On Error Resume Next
Application.EnableEvents = False
For Each myCell In myRng.Cells
If IsEmpty(myCell.Offset(0, 1)) = False Then
'do nothing--it's already filled
Else
myCell.Offset(0, 1).Value = myCell.Value
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

Good luck,

rhay999 wrote:

Thanks very much Dave for your reply.

I'm sorry if I didn't give you all the information.

Basically, data will be "pushed" record by record (row by row) from a
charting application directly into an Excel worksheet. There will be no
DDE statements in Excel.

The whole point of this is to get a snapshot of calculations at
specific points in time (when a new record comes in). Unfortunately,
Excel keeps on re-calculating everything from scratch each time
overwriting previous results.

When a row is filled (say columns A-H) then calculations will be done
(in column I) based on calculation on this row and other data held in
other worksheets. However, this calculation in I (or a copy of it in
another column) must remain permanent after it has done this first
calculation as data in the other worksheets will change thereafter.

I've now spent ages trying to get this up and running without success.
All I know is that perhaps Worksheet_Change may not work because the
data is being "pushed" and that Worksheet_Calculate may do so. However,
can I code it with the correct commands... can I heck! I have a little
experience of programming but this is beyond me.

Would be really grateful of another reply from you or someone else out
there.

Thanks again.

Richard

Dave Peterson Wrote:
So if column B of that same row has something in it, don't do anything?

If column B is empty, then put that value entered into column A into
column B?

If yes, then maybe this worksheet event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) = False Then Exit Sub

On Error GoTo errHandler:
Application.EnableEvents = False
Target.Offset(0, 1).Value = Target.Value

errHandler:
Application.EnableEvents = True

End Sub


rightclick on the worksheet tab that should have this behavior. Select
view
code and paste this into the code window. Then back to excel and test
it out.

If you make a typing error in column A and column B is empty, then when
you
correct that column A value, column B will still be incorrect--right?



rhay999 wrote:-

Hi -

I'd be very grateful if someone can help me with a bit of macro code
that might achieve this. Really having trouble as my programming
skill
is diabolical!!

I want to perform a "once only" calculation on rows of cells. When a
reference cell changes from blank to a value it will trigger a
calculation in another cell. When the reference cell changes
subsequenlty it will not affect the value in the calculated cell.

e.g.

Cell - Formula - Value
--------------------

Step 1
A1 blank
B1 =A1 value blank

Step 2
A1 value 10
B1 =A1 value 10

Step3
A1 value 20
B1 =A1 value 10 (stays at value 10 irrespective of value in A1)

This operation would need to be replicated through hundreds of rows.

Thanks in advance.

Richard

--
rhay999-

--

Dave Peterson

--
rhay999

--

Dave Peterson