Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
About my looping
Just started VB for some functions that I would love to realise. but
the onlyu thing that I have for the moment is Sub Macro2() ' ' Macro2 Macro ' Macro recorded 11/07/2007 by STD03 ' ' i = 0 Do While Now() 39269 Range("B5:G7").Select Selection.Copy Range("B13").Select ActiveCell.Offset(i, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False i = i + 3 Loop End Sub B5:G7 is some dynamic data from other database which change irregularly. my code makes it copy and paste in a enormous frequency. what i want to realise is to make it copy every 5 seconds, in order to sotck the data on a regular base. just started VB learning and I have a very short period to make this realised. So hope I can get some help. Thanks a lot in advance |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
About my looping
Jonathan,
You would be better off using the OnTime method to control the data write. Use the code below, and run StartIt to start the process, and StopIt to stop the process. This will put the date and time into column A next to the stored values for record-keeping. HTH, Bernie MS Excel MVP Dim NextTime As Date Sub StartIt() Update End Sub Sub Update() Dim myCell As Range NextTime = Time + TimeValue("00:00:05") Range("B5:G7").Copy Set myCell = Cells(Rows.Count, 2).End(xlUp)(2) myCell.PasteSpecial _ Paste:=xlPasteValues With myCell.Offset(0, -1).Resize(3) .Value = Now .NumberFormat = "mm/dd/yy hh:mm:ss" End With Application.OnTime NextTime, "Update" End Sub Sub StopIt() On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False End Sub "jonathan" wrote in message ps.com... Just started VB for some functions that I would love to realise. but the onlyu thing that I have for the moment is Sub Macro2() ' ' Macro2 Macro ' Macro recorded 11/07/2007 by STD03 ' ' i = 0 Do While Now() 39269 Range("B5:G7").Select Selection.Copy Range("B13").Select ActiveCell.Offset(i, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False i = i + 3 Loop End Sub B5:G7 is some dynamic data from other database which change irregularly. my code makes it copy and paste in a enormous frequency. what i want to realise is to make it copy every 5 seconds, in order to sotck the data on a regular base. just started VB learning and I have a very short period to make this realised. So hope I can get some help. Thanks a lot in advance |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
About my looping
On Jul 11, 3:56 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Jonathan, You would be better off using the OnTime method to control the data write. Use the code below, and run StartIt to start the process, and StopIt to stop the process. This will put the date and time into column A next to the stored values for record-keeping. HTH, Bernie MS Excel MVP Dim NextTime As Date Sub StartIt() Update End Sub Sub Update() Dim myCell As Range NextTime = Time + TimeValue("00:00:05") Range("B5:G7").Copy Set myCell = Cells(Rows.Count, 2).End(xlUp)(2) myCell.PasteSpecial _ Paste:=xlPasteValues With myCell.Offset(0, -1).Resize(3) .Value = Now .NumberFormat = "mm/dd/yy hh:mm:ss" End With Application.OnTime NextTime, "Update" End Sub Sub StopIt() On Error Resume Next Application.OnTime NextTime, "Update", schedule:=False End Sub "jonathan" wrote in message ps.com... Just started VB for some functions that I would love to realise. but the onlyu thing that I have for the moment is Sub Macro2() ' ' Macro2 Macro ' Macro recorded 11/07/2007 by STD03 ' ' i = 0 Do While Now() 39269 Range("B5:G7").Select Selection.Copy Range("B13").Select ActiveCell.Offset(i, 0).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False i = i + 3 Loop End Sub B5:G7 is some dynamic data from other database which change irregularly. my code makes it copy and paste in a enormous frequency. what i want to realise is to make it copy every 5 seconds, in order to sotck the data on a regular base. just started VB learning and I have a very short period to make this realised. So hope I can get some help. Thanks a lot in advance- Hide quoted text - - Show quoted text - Thanks so much. it works perfectly so far. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
About my looping
Sorry to hear about your looping
|