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

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

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

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

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"rhay999" wrote in message
...

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 -

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

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

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

Thanks again.

Richard

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 wrote:

Dave -

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

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

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

Thanks again.

Richard

Dave Peterson Wrote:
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,

Dave Peterson

--
rhay999

--

Dave Peterson

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

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

rhay999 wrote:

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 Wrote:
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 wrote:

Dave -

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

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

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

Thanks again.

Richard

Dave Peterson Wrote:
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,

Dave Peterson

--
rhay999

--

Dave Peterson

--
rhay999

--

Dave Peterson