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
Quote:
|
Originally Posted by Dave Peterson
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
|