Stock Exchange Data Log

BuffaloGuy

New Member
Joined
Dec 5, 2017
Messages
27
I am attempting to log stock data every 'n' minutes (1 in this example), I bring in the information via data in excel data types.
This code is supposed to refresh data every minute and copy the data from cells C3 through I3 and copy it into row 103 and keep the data and add the new data on top.

I copied this code from someone else and tried to modify it to my needs. I likely butchered it.

I get a application-defined or object-defined error message.

VBA Code:
Sub StartLogging()
  Const strSOURCE_SHEET = "Data" ' <-- name of sheet containing stock data
  Const strTARGET_SHEET = "Data" ' <-- name of sheet to periodically log data to
  Const strSOURCE_RANGE = "C3:I3" ' <-- cell address of data to be logged
  Dim intNextRow As Integer
  Dim rngSource As Range
 
  On Error GoTo ErrorHandler
  Application.ScreenUpdating = False
  Set rngSource = ThisWorkbook.Sheets(strSOURCE_SHEET).Range(strSOURCE_RANGE).Rows(1)
  With ThisWorkbook.Sheets(strTARGET_SHEET)
    intNextRow = .Cells(103, .Rows.Count).End(xlDown).Row + 1
    .Cells(103, intNextRow).Value = Now()
    .Cells(103, intNextRow).Font.Bold = True
    .Cells(104, intNextRow).Resize(rngSource.Cells.Count).Value = rngSource.Value
    .Rows(intNextRow).AutoFit
  End With
  m_dtmNextSchedule = Now() + TimeValue("0:01") ' <-- reschedule for 1 minute time
  Application.OnTime m_dtmNextSchedule, "StartLogging", Schedule:=True
 
ExitHandler:
  On Error Resume Next
  Application.ScreenUpdating = True
  Exit Sub
ErrorHandler:
  MsgBox Err.Description, vbExclamation
  Resume ExitHandler
End Sub

Sub StopLogging()
  On Error GoTo ErrorHandler
  Application.OnTime m_dtmNextSchedule, "StartLogging", Schedule:=False
  Exit Sub
ErrorHandler:
  MsgBox Err.Description, vbExclamation
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub
1611535803231.png
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,957
The following will perform the desired function. There must be something in Cell A102 for this method to begin copying to cell
A103.

VBA Code:
Option Explicit

Dim TimeToRun


Sub MacroRun()

    TimeToRun = Now + TimeValue("00:00:05")
    Application.OnTime TimeToRun, "Macro1"
    
End Sub

Sub Macro1()
    Calculate
    Worksheets("sheet1").Range("C3:I3").Copy
    
    Worksheets("sheet1").Cells(Rows.Count, "a").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

    Application.CutCopyMode = False

MacroRun

End Sub

Sub stopMacros()

    On Error Resume Next
    Application.OnTime TimeToRun, "Macro1", , False
    
End Sub
 
Solution

BuffaloGuy

New Member
Joined
Dec 5, 2017
Messages
27
Thank you so much. This will work much better. Its working pretty well, but it copies the data to cell A2:G2
Thoughts?
 

BuffaloGuy

New Member
Joined
Dec 5, 2017
Messages
27

ADVERTISEMENT

I needed it to work in column C, thats why it didn't work. I figured that part out.

Many Thanks!!
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,957
VBA Code:
Worksheets("sheet1").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
 

BuffaloGuy

New Member
Joined
Dec 5, 2017
Messages
27
Yeah that's what I did.
Will the MacroRun refresh the the worksheet on the same interval?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,957
"Will the MacroRun refresh the the worksheet on the same interval?"

No. You can add that.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,327
Messages
5,624,029
Members
416,006
Latest member
PCaffrey

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