Record a dynamically changing value from a cell (B3-Sheet1) against a column with predefined time intervals

[Keerthan17]

Hi Folks,

I have a situation here. Need a little help on how to record a dynamically changing cell (from an external source without any formula) from 'Sheet1' cell B3 to 'Sheet2' which has a column A with a specified time interval starting from 9:15:00, 9:15:01, 9:15:02... so on till 15:30:00 and store the data in column B of 'Sheet2' which is dynamically changing corresponding to each time value specified in column A.

There is no Time column in 'Sheet1'. Can the actual current time used to save the data.

Attached is the screenshot of 'Sheet2' where the data needs to be stored.

Thanks a lot in advance. Appreciate a code or formula for the problem.

Regards,
Keerthan

 

[Keerthan17]

Hi Folks,

I have a situation here. Need a little help on how to record a dynamically changing cell (from an external source without any formula) from 'Sheet1' cell B3 to 'Sheet2' which has a column A with a specified time interval starting from 9:15:00, 9:15:01, 9:15:02... so on till 15:30:00 and store the data in column B of 'Sheet2' which is dynamically changing corresponding to each time value specified in column A.

There is no Time column in 'Sheet1'. Can the actual current time used to save the data.

Attached is the screenshot of 'Sheet2' where the data needs to be stored.

Thanks a lot in advance. Appreciate a code or formula for the problem.

Regards,
Keerthan

Pls...Anybody??

 

[Osvaldo Palmeiro]

Hello, Keerthan.

I think the only way to achieve what you're looking for is by a macro solution.

Generally, data received from an external source does not trigger any event that can be used to run a macro.
You can do some tests on this by installing the two codes below in the Sheet1 module: right click the tab of that sheet and choose "view code", then turn on the external source and check if changing the value of B3 any message is displayed.

Private Sub Worksheet_Change(ByVal Target As Range)
 MsgBox "ws change has fired"
End Sub

Private Sub Worksheet_Calculate()
 MsgBox "ws calculate has fired"
End Sub

If not, then a way around is to put a formula like = B3 in an unused cell on Sheet1, in order to force that sheet to recalculate after the external change of B3, then we can take advantage of the "calculate" event to run a code that will insert the time and value of B3 on Sheet2.

To implement this solution replace the previous ws_calculate with a copy of the code below.
To test it leave the Sheet2 empty, without the hours in column A, then turn on the external source, so the code will insert the time in column A and the last value of Sheet1 B3 in column B.

Private Sub Worksheet_Calculate()
 Sheets("Sheet2").Cells(Rows.Count, 1).End(3)(2) = Time
 Sheets("Sheet2").Cells(Rows.Count, 2).End(3)(2) = Sheets("Sheet1").[B3]
End Sub[/CO

 

[Keerthan17]

Hello, Keerthan.

I think the only way to achieve what you're looking for is by a macro solution.

Generally, data received from an external source does not trigger any event that can be used to run a macro.
You can do some tests on this by installing the two codes below in the Sheet1 module: right click the tab of that sheet and choose "view code", then turn on the external source and check if changing the value of B3 any message is displayed.

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "ws change has fired"
End Sub

Private Sub Worksheet_Calculate()
MsgBox "ws calculate has fired"
End Sub

If not, then a way around is to put a formula like = B3 in an unused cell on Sheet1, in order to force that sheet to recalculate after the external change of B3, then we can take advantage of the "calculate" event to run a code that will insert the time and value of B3 on Sheet2.

To implement this solution replace the previous ws_calculate with a copy of the code below.
To test it leave the Sheet2 empty, without the hours in column A, then turn on the external source, so the code will insert the time in column A and the last value of Sheet1 B3 in column B.

Private Sub Worksheet_Calculate()
Sheets("Sheet2").Cells(Rows.Count, 1).End(3)(2) = Time
Sheets("Sheet2").Cells(Rows.Count, 2).End(3)(2) = Sheets("Sheet1").[B3]
End Sub[/CO

Hi Osvaldo, Thanks for the help..

Firstly I checked the test code and i'm geting the msg "ws calculate has fired" with the worksheet_calculate method. Then placed the second code. I only get a single timestamp and nothing on column B of sheet2. Could you pls help..
And what can be done to record the time and data on Sheet2 for every second in a new row?

Thanks,
Keerthan

 

[Osvaldo Palmeiro]

Then placed the second code. I only get a single timestamp and nothing on column B of sheet2.

That's strange, and only should happen if B3 of the Sheet1 is empty.

And what can be done to record the time and data on Sheet2 for every second in a new row?

Please, try the 3 codes below in a standard module.
Manually run StartUpdate code to start and StopUpdate code to stop the process.

Public dTime As Date

Sub UpDate()
 dTime = Time + TimeValue("00:00:01")
 Application.OnTime (dTime), "UpDate"
 Sheets("Sheet2").Cells(Rows.Count, 1).End(3)(2) = Time
 Sheets("Sheet2").Cells(Rows.Count, 2).End(3)(2) = Sheets("Sheet1").[B3]
End Sub

Sub StartUpdate()
  Application.OnTime Now, "UpDate"
End Sub

Sub StopUpdate()
  Application.OnTime dTime, "UpDate", , False
End Sub