Once only calculation - help please

[rhay999]

Hi -

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

I want to perform a "once only" calculation on rows of cells. When
reference cell changes from blank to a value it will trigger
calculation in another cell. When the reference cell change
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.

Richar

 

[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]

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]

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,

 

[Ragdyer]

Don't know squat about code, but just a thought:

If I were doing this manually, I'd remove the formulas after the initial
calculation and just leave the data behind with a <Copy> <PasteSpecial>
<Values>

No formulas ... No re-calculation possible.

 

[rhay999]

Dave -

Thanks very much for the code - very concise and works beautifully.

One small problem - after couple of hundred rows being entrered, Exce
starts to slow down as each event is re-calculating the code for al
the previous ones - or that is the way is seems. Unfortuantely, ther
could be low thousands of rows being loaded.

Is there any way that the code could be triggered just on the curren
row/current cell changing rather than a global change to th
worksheet?

Thanks again.

Richard

 

[Dave Peterson]

That's the bad news. Excel doesn't keep track of the cell that changed with
worksheet_calculate.

And I don't know anything about DDE to know if there's a way around this.

Maybe someone who knows DDE stuff can post a better solution--or at least
commiserate with you.

 

[rhay999]

Thanks anyway Dave. What you have works very well - I'll just have to
think of some other way to get around the volume of records.

I've left another post about using the RECALL() command from the free
addin Morefunc.xll. It displays the previous contents of a cell which
can be compared against the latest contents of the cell. Unfortunately,
I haven't been able to make it work in the "Once only" set up - maybe
someone else will.

I don't know if you know morefunc but is has several very useful
commands - two of which I have used a lot - LASTROW() which finds the
last row in a column and GETV(), SETV() which sets variables within a
command.

http://xcell05.free.fr/ in case you don't know it.

Richard

 

[Dave Peterson]

I've never used this addin. But I did read that Laurent Longre just updated it
(although, for a different reason).