| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Capturing The First Change in a Formula
I have a spreadsheet that monitors the opening time of some stocks. In the
morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
One way:
Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
Thank you OTTO.
I am not familiar with the using "Worksheet_Change" event. Can guide me through ? "Otto Moehrbach" wrote: One way: Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
Carl
Let's make the following assumptions: The time cell that changes continuously is C1 The cell where to put the first time change is F1 Make sure that F1 is blank at the start of the day. The following macro does what you want Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Range("C1").Value End Sub Because this macro is a sheet event macro, it must be placed in the module for that sheet. To access this module, have that sheet on the screen. Right-click the sheet tab for that sheet. Select View Code and paste this macro into that module. Click on the "X" at the top right of the screen to get back to the sheet. Please post back if you need more. HTH Otto "carl" wrote in message ... Thank you OTTO. I am not familiar with the using "Worksheet_Change" event. Can guide me through ? "Otto Moehrbach" wrote: One way: Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
Thank you again. Will this work for all of column C or just c1 ? Thanks again ?
"Otto Moehrbach" wrote: Carl Let's make the following assumptions: The time cell that changes continuously is C1 The cell where to put the first time change is F1 Make sure that F1 is blank at the start of the day. The following macro does what you want Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Range("C1").Value End Sub Because this macro is a sheet event macro, it must be placed in the module for that sheet. To access this module, have that sheet on the screen. Right-click the sheet tab for that sheet. Select View Code and paste this macro into that module. Click on the "X" at the top right of the screen to get back to the sheet. Please post back if you need more. HTH Otto "carl" wrote in message ... Thank you OTTO. I am not familiar with the using "Worksheet_Change" event. Can guide me through ? "Otto Moehrbach" wrote: One way: Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
As written, it copies the value only if C1 changes. If you want this macro
to copy the cell whenever any cell in Column C changes and then change it to the following: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Target.Value End Sub HTH Otto "carl" wrote in message ... Thank you again. Will this work for all of column C or just c1 ? Thanks again ? "Otto Moehrbach" wrote: Carl Let's make the following assumptions: The time cell that changes continuously is C1 The cell where to put the first time change is F1 Make sure that F1 is blank at the start of the day. The following macro does what you want Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Range("C1").Value End Sub Because this macro is a sheet event macro, it must be placed in the module for that sheet. To access this module, have that sheet on the screen. Right-click the sheet tab for that sheet. Select View Code and paste this macro into that module. Click on the "X" at the top right of the screen to get back to the sheet. Please post back if you need more. HTH Otto "carl" wrote in message ... Thank you OTTO. I am not familiar with the using "Worksheet_Change" event. Can guide me through ? "Otto Moehrbach" wrote: One way: Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
Thank you again Otto. Sorry to keep bothering you but am trying to learn.
Here's my original table: Stock TIME QQQQ-O 2/14/06 AAPL-O 2/14/06 GOOG-O 2/14/06 CSCO-O 2/14/06 SPY-A 2/14/06 After trading starts each line gets updated with a time. So for each line (QQQQ-O, AAPL-O, GOOG-O etc) I need to capture the first change of the day. So the change is at a cell level - not at the worksheet level. Does this make a difference ? "Otto Moehrbach" wrote: As written, it copies the value only if C1 changes. If you want this macro to copy the cell whenever any cell in Column C changes and then change it to the following: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Target.Value End Sub HTH Otto "carl" wrote in message ... Thank you again. Will this work for all of column C or just c1 ? Thanks again ? "Otto Moehrbach" wrote: Carl Let's make the following assumptions: The time cell that changes continuously is C1 The cell where to put the first time change is F1 Make sure that F1 is blank at the start of the day. The following macro does what you want Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Range("C1").Value End Sub Because this macro is a sheet event macro, it must be placed in the module for that sheet. To access this module, have that sheet on the screen. Right-click the sheet tab for that sheet. Select View Code and paste this macro into that module. Click on the "X" at the top right of the screen to get back to the sheet. Please post back if you need more. HTH Otto "carl" wrote in message ... Thank you OTTO. I am not familiar with the using "Worksheet_Change" event. Can guide me through ? "Otto Moehrbach" wrote: One way: Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
That's how you learn. Keep asking. Otto
"carl" wrote in message ... Thank you again Otto. Sorry to keep bothering you but am trying to learn. Here's my original table: Stock TIME QQQQ-O 2/14/06 AAPL-O 2/14/06 GOOG-O 2/14/06 CSCO-O 2/14/06 SPY-A 2/14/06 After trading starts each line gets updated with a time. So for each line (QQQQ-O, AAPL-O, GOOG-O etc) I need to capture the first change of the day. So the change is at a cell level - not at the worksheet level. Does this make a difference ? "Otto Moehrbach" wrote: As written, it copies the value only if C1 changes. If you want this macro to copy the cell whenever any cell in Column C changes and then change it to the following: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Target.Value End Sub HTH Otto "carl" wrote in message ... Thank you again. Will this work for all of column C or just c1 ? Thanks again ? "Otto Moehrbach" wrote: Carl Let's make the following assumptions: The time cell that changes continuously is C1 The cell where to put the first time change is F1 Make sure that F1 is blank at the start of the day. The following macro does what you want Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Range("C1").Value End Sub Because this macro is a sheet event macro, it must be placed in the module for that sheet. To access this module, have that sheet on the screen. Right-click the sheet tab for that sheet. Select View Code and paste this macro into that module. Click on the "X" at the top right of the screen to get back to the sheet. Please post back if you need more. HTH Otto "carl" wrote in message ... Thank you OTTO. I am not familiar with the using "Worksheet_Change" event. Can guide me through ? "Otto Moehrbach" wrote: One way: Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
Hi Otto. The code still does not work. Any ideas ?
"Otto Moehrbach" wrote: That's how you learn. Keep asking. Otto "carl" wrote in message ... Thank you again Otto. Sorry to keep bothering you but am trying to learn. Here's my original table: Stock TIME QQQQ-O 2/14/06 AAPL-O 2/14/06 GOOG-O 2/14/06 CSCO-O 2/14/06 SPY-A 2/14/06 After trading starts each line gets updated with a time. So for each line (QQQQ-O, AAPL-O, GOOG-O etc) I need to capture the first change of the day. So the change is at a cell level - not at the worksheet level. Does this make a difference ? "Otto Moehrbach" wrote: As written, it copies the value only if C1 changes. If you want this macro to copy the cell whenever any cell in Column C changes and then change it to the following: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column < 3 Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Target.Value End Sub HTH Otto "carl" wrote in message ... Thank you again. Will this work for all of column C or just c1 ? Thanks again ? "Otto Moehrbach" wrote: Carl Let's make the following assumptions: The time cell that changes continuously is C1 The cell where to put the first time change is F1 Make sure that F1 is blank at the start of the day. The following macro does what you want Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C1")) Is Nothing Then Exit Sub If IsEmpty(Range("F1")) Then _ Range("F1").Value = Range("C1").Value End Sub Because this macro is a sheet event macro, it must be placed in the module for that sheet. To access this module, have that sheet on the screen. Right-click the sheet tab for that sheet. Select View Code and paste this macro into that module. Click on the "X" at the top right of the screen to get back to the sheet. Please post back if you need more. HTH Otto "carl" wrote in message ... Thank you OTTO. I am not familiar with the using "Worksheet_Change" event. Can guide me through ? "Otto Moehrbach" wrote: One way: Designate some cell somewhere as the place where the first time value is stored. Make sure this cell is blank when the day starts. Then use a Worksheet_Change event macro to take the value in the time cell (the cell that keeps changing) and put it in this designated "First time" cell, if that cell is empty. That cell won't be empty after the first time and so will remain static. Post back if you need more. HTH Otto "carl" wrote in message ... I have a spreadsheet that monitors the opening time of some stocks. In the morning before trading starts it looks like this: Stock TIME QQQQ-O 2/15/06 AAPL-O 2/15/06 GOOG-O 2/15/06 CSCO-O 2/15/06 SPY-A 2/15/06 When trading starts, the TIME value records the time of the last trade, thus the first time will represent the time of the first trade - I am trying to capture this time value. The problem is that if another trade occurs, the TIME value updates to the time of that last trade. The TIME value is fed by a DDE link from a data source. Is there a way to capture the "first" change in the TIME value ? Thank you in advance. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Capturing The First Change in a Formula
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| I Need a formula to evaluate a cell with + or - values | Excel Worksheet Functions | |||
| Change sheet name in formula | Excel Worksheet Functions | |||
| How to change a visible formula into a value? (Quickbooks download | Excel Worksheet Functions | |||
| Cannot change a formula | Excel Discussion (Misc queries) | |||
Linear Mode
