Single calculation

J

Juan C

Board Regular
Joined
Mar 6, 2009
Messages
131
Help me here...
Is there a way for me to have a formula perform its calculation one time only... meaning that if the precedent data changes it (the formula) won't compute again, thus leaving the previous number it calculated unchange...
 
Sort by date Sort by votes
Thanks for your reply. I added this code to my spread sheet but nothing seems to happen.

The remote data is coming into A1 and I want to populate column B with each stream of data coming into A1. Can I do that?

Thanks again...

JC

Alexander Barnes said:
Assuming that Cell A2 has the formula =A1 and A1 is the cell being remotely updated,

maybe:
Code: 
Private Sub Worksheet_Calculate()
Cells(Rows.Count,"B").end(xlUp).Offset(1,0).Value = Range("A1").value
End Sub


EDIT:
Note, I may be too cautious here. I don't trust programmatic changes because I don't have experience with them, so having some other cell depend on the programmatically changed cell is another triggering event.
Click to expand...
 
Upvote 0

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Maybe the data coming into A1 will trigger a change event. Try

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
    Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Thanks 10000000. It works like a charm!!!!!

JC

VoG said:
Maybe the data coming into A1 will trigger a change event. Try

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
    Application.EnableEvents = False
    Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
    Application.EnableEvents = True
End If
End Sub
Click to expand...
 
Upvote 0
Good morning....

It seems that this code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
Application.EnableEvents = False
Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value
Application.EnableEvents = True
End If
End Sub

worked fine when I input the data manually, but this morning when I tried to collect this data from the incoming data stream nothing happens, column B simply wont fill.

Any ideas???

Thanks

JC

Juan C said:
Thanks 10000000. It works like a charm!!!!!

JC
Click to expand...
 
Upvote 0
Try Alexander's suggestion:

In any spare cell enter the formula

=A1

then use the code

Code: 
Private Sub Worksheet_Calculate()
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Range("A1").Value
End Sub
 
Upvote 0
Almost there.... if filling column B but repeating the data in A1 without it changing. Remember that I need to get each data change into column B

Could u help?? Thanks

JC

VoG said:
Try Alexander's suggestion:

In any spare cell enter the formula

=A1

then use the code

Code: 
Private Sub Worksheet_Calculate()
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Range("A1").Value
End Sub
Click to expand...
 
Upvote 0
If you mean that it is adding values to column B when A1 is not changing try

Code: 
Private Sub Worksheet_Calculate()
If Cells(Rows.Count, "B").End(xlUp).Value <> Range("A1").Value Then
    Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Range("A1").Value
End If
End Sub
 
Upvote 0
Or we could set it up to read the data in A1 every second.... This is even better for me...


Juan C said:
Almost there.... if filling column B but repeating the data in A1 without it changing. Remember that I need to get each data change into column B

Could u help?? Thanks

JC
Click to expand...
 
Upvote 0
OK... great!!! Now it's working. Many many thanks....

JC

VoG said:
If you mean that it is adding values to column B when A1 is not changing try

Code: 
Private Sub Worksheet_Calculate()
If Cells(Rows.Count, "B").End(xlUp).Value <> Range("A1").Value Then
    Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Range("A1").Value
End If
End Sub
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Subtracting all calendar days, but end date cannot fall on a weekend or holiday. If it does, the end date shifts to the preceding work day.
Replies
8
Views
229
Romy
R
M
  • Solved
Calculating 1 / 3 / 5 year Returns on a table that varies in length.
Replies
3
Views
293
Anthony47
A
J
  • Solved
Sum FTEs based on start/end date
Replies
3
Views
175
etaf
etaf
J
  • Question
Simple Excel Formulas Not Showing Correct Answer (shows Dash or 0 depending on formatting) NOT a "Numbers Stored As Text" issue!
Replies
6
Views
607
Alex Blakenburg
A
X
  • Question
Formula bar displaying SUM formula, but not its arguments
Replies
4
Views
263
KRice
K

Forum statistics

Threads
1,214,893
Messages
6,122,121
Members
449,066
Latest member
Andyg666

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top