Macro to special paste dynamic values into text values with periods

**iyudhi** · 01-31-2011, 06:53 PM

Hi,

I'm trying to have value pulled from an external source copied into text values. The data is updating with 5 seconds period, and everytime the value changes it needs to update the text values.

The reason is that I can't get the graph (based on the pulled values) to update every time the data changes. When I try to convert it into text values the graph update in real time.

I have very limited experience with macro so any help is appreciated.

Thank you very much,

**MarvinP** · 01-31-2011, 07:59 PM

Hi iyudhi and welcome to the forum,

We need a little more information to start working on your problem. Are you doing Stock Symbol updates coming from somewhere and trying to do a daily history file in 5 second intervals?

Where your data is coming from and how you are importing it are of concern.

Could you supply a sample sheet and more information?

**iyudhi** · 02-01-2011, 02:02 PM

I have attached the the screenshot, perhaps that would make it clearer. The data is coming from a 3rd party software which is also doing the update. the time axis will change over time and also the data. You can see an example of the query to pull up the data at the top.

You'll notice that the data on the graph didn't match the data on the spreadsheet.

My plan is to copy and special paste the data into values every 5s. It seems that's the only way the graph would update.

Thanks for your help,

Originally Posted by MarvinP

Hi iyudhi and welcome to the forum,

We need a little more information to start working on your problem. Are you doing Stock Symbol updates coming from somewhere and trying to do a daily history file in 5 second intervals?

Where your data is coming from and how you are importing it are of concern.

Could you supply a sample sheet and more information?

**MarvinP** · 02-01-2011, 03:26 PM

Hi,
The way to build a macro is to decide exactly what you want to do by hand. Then turn on the macro recorder and record the steps to accomplish what you want. Turn off the macro record and go look at the VBA code that was produced. From there you can see what basic VBA commands are used to accomplish what you want. Next you might need to modify the code so it works with different sizes of data or copies to the end, instead of over cells you need.
Here is a good site to read: http://www.excel-vba.com/excel-vba-contents.htm

**iyudhi** · 02-01-2011, 04:32 PM

Hi Marvin,

Thanks... your suggestion actually help... I managed to record a macro to simulate what I want to do... (Can't believe it was that simple)

Do you know a way to set up a scheduler so the macro can be run/refreshed for a certain period of time (this case... 5 seconds)

Originally Posted by MarvinP

Hi,
The way to build a macro is to decide exactly what you want to do by hand. Then turn on the macro recorder and record the steps to accomplish what you want. Turn off the macro record and go look at the VBA code that was produced. From there you can see what basic VBA commands are used to accomplish what you want. Next you might need to modify the code so it works with different sizes of data or copies to the end, instead of over cells you need.
Here is a good site to read: http://www.excel-vba.com/excel-vba-contents.htm

**MarvinP** · 02-01-2011, 04:42 PM

VBA has a Timer() function which returns the current time.
See http://www.vbaexpress.com/kb/getarticle.php?kb_id=626 for a simple example.

You set some variable = Timer() and then subtract this variable from the current time (Timer()) until you get to 5 seconds. When this happens you reset the variable to the current time and run your routine.

hope that helps.