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

Keerthan17

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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
 

Attachments

  • Time.PNG
    Time.PNG
    12.1 KB · Views: 14

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Keerthan17

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 24, 2009
Messages
693
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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.
VBA Code:
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

New Member
Joined
Mar 14, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
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.
VBA Code:
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.
VBA Code:
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

Well-known Member
Joined
Feb 24, 2009
Messages
693
Office Version
  1. 365
Platform
  1. Windows
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. :unsure:

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.

VBA Code:
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
 

Forum statistics

Threads
1,143,620
Messages
5,719,784
Members
422,244
Latest member
AYSHANA

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
Top