Trouble Creating Database With Timestamped Recorded Values

[chrisjwhite90]

Hello,

I am just started learning VBA and needed some help on the creation of a macro to record a set of values and put in a time stamp as well.

The spreadsheet is set up with the second row (row 2) with a value in each cell going horizontally, from column to column. These cells are continuously updated with a link to provide a live number of a specific products, who's names are in row 1.

I would like to record each of these values at a specific time interval below the updating values to keep a log of them to run some statistical analysis on them. Specifically, for each 1 minute time interval, I'd like all the values in the 2nd row (the ones that are continuously updating) to be copied and pasted to the row below, and in the first column ( column A) have a time stamp of the exact time that this value was recorded. At the next minute, I want this to happen again, but this time the value pasted below the prevously recorded/pasted row, so that as time passes. a new row is formed with new snapshot values of the live cells in row 2. I want it to be able to run continuously (infinetely recording values downaward) until I manually break the macro myself ( I know this will be a huge amount of data, but I can manage that).

I know how to make a button of this, so that wont be a problem, but how would I be able to adjust the spreadsheet macro in case I decide to add even more updating cells in row 2, thereby using more columns to record data. Is there a specific way I can update the macro in order to run it the same, but now also incorporate the new value recorded and time stamped, just like the others. ( I realize that the time stamps for these "extra" products added in will not have the previously recorded data as the others would, and therefore would start way down the rows in sync with the others).

I'm at a loss on how to do something this complex, and help or guidance would be appreciated. I hope I've been descriptive enough. Thanks!

[Sisyphus]

Look into the forum for Excel Macros (on this site). You will find a macro there that you can make run every minute. Figure out how to make it stop when you want it to stop.
The code you want to run every minute consists of copying data from one row (which is always the same). Write code that copies that row.
Then you need to paste the contents of the clipboard (what you copied above) to a cell in the next blank row at the bottom of the sheet. There is a function among the Excel Macros that returns the number of that row. In order to paste the clipboard you need to specify the first cell of the target. That would be Cells(LastRow, "A").
So, there is your program. All you need to do is put the parts together. If you need help, show us where you got stuck.

[chrisjwhite90]

I've compiled code that takes into account everything you've suggested using previous code on this site. I've added in 3 functions into the module, but I need help from here. All the cells I need to copy are in the 2nd row, from colums B to essentially infinity, but I dont know how to factor this into the code. And they need to be pasted the row below, +1 for every minute of time. Basically to create a data base of changing values as the day goes on.

Also, the time recorder, I'd like to start in row 3, column A, I don't know where to put this into the code.

For the SUB ADD ROW, I've specified that I want the target row to be the 3rd row, and then continue to add new rows +1 from the last. The first sheet in my workbook is named "Data", I would like the values only to be recorded in this sheet of the book, but by typing in "[Data]" in the parameters of the 3rd function I'm not sure if this does the job. Also, I want my source row to be the second row, from column B onward. I specified that as well but I don't know if I am doing it wrong.

I don't know how to make the marco stop besides breaking it, but I do know how to assign a button to it.

Entering all this in there are numerous errors and the macro doesn't run because of that. I'd really appreciate any help on this. I do not know correct syntax and I know my code is severly flawed because I've had no prior programming experince. Thanks a lot you guys.

Here's everything I have currently in the modu\le.

Code: Select all

Option Explicit

Sub ValueStore()

    Dim dTime As Date
    Dim R As Long
   
    R = LastRow("E") + 1
    Cells(R, "B").Value = Range("B2").Value
    Cells(R, "C").Value = Range("C2").Value
    dTime = Now + TimeValue("00:01:00")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

Public Function LastRow(Optional ByVal Col As Variant, _
                         Optional Ws As Worksheet) As Long
    ' 0059 V 3.2 Apr 2, 2012

    ' Return the number of the last non-blank row in column Col.
    ' Specify the column as string or number
    ' If no column is specified,
      ' return the last row from column A.
    ' If no worksheet is specified
      ' return the result from the currently active sheet.
   
    Dim R As Long
   
    If Ws Is Nothing Then Set Ws = ActiveSheet
    If VarType(Col) = vbError Then Col = 1
    With Ws
        R = .Cells(.Rows.Count, Col).End(xlUp).Row
        With .Cells(R, Col)
            ' in a blank column the last used row is 0 (= none)
            If R = 1 And .Value = vbNullString Then R = 0
            ' include all rows of a merged range
            LastRow = R + .MergeArea.Rows.Count - 1
        End With
    End With
End Function

Public Sub AddRow(Optional ByVal Rt As Long, [Target_Row=3]
                  Optional ByVal Rs As Long, [Source_Row=2)
                  Optional ByRef Ws As Data)

    If Ws Is Nothing Then Set Ws = ActiveSheet
    If Rt < 1 Then Rt = LastRow(1, Ws) + 1
    If Rs < 1 Then Rs = Rt - IIf(Rt = 1, 0, 1)
   
    With Ws
        .Rows(Rs).Copy
        .Rows(Rt).Insert xlShiftDown
        Application.CutCopyMode = False
        On Error Resume Next
        With .Rows(Rt)
            .Cells.SpecialCells(xlCellTypeConstants).ClearContents
            .Cells(1).Select
        End With
    End With
End Sub



[Sisyphus]

That is a very good start!
You should design your code in such a way that you can test every thought. Don't advance to the next thought until the previous one has been tested. For this purpose you need to have a worksheet where you do your testing, and when you post your question it is better all around if you post that workbook so that we can see your tests and their results.

Now, this code is very nearly perfect:-
R = LastRow("E") + 1
Cells(R, "B").Value = Range("B2").Value
Cells(R, "C").Value = Range("C2").ValueBut then you lost the logic that plain language would have supplied you with. Try this:-
You wish to run the above code every minute. (Actually, you wish to run the code after B2:C2 have been updated. That does happen every minute but the code that is doing it isn't in evidence.) Now, you are taking the LastRow from column E but you are writing only to columns B and C in the new row. This will lead to the code constantly writing to the same "last" row. I think this is what you had in mind:-

Code: Select all

    'Update row 2
    'Add a formatted row at the bottom
    'Set values in the new row B:C = B2:C2

As you can easily see, you have only done the last part.
Now your question burns down to how to call a procedure. This is because "Add a formatted row at the bottom" just requires you to call the procedure AddRow. The code is,

Code: Select all

AddRow R, 2

Observe that your statement that Rt should be 3 isn't correct. It should be R which will be 3 only the first time. Next time around it will be 4 etc. But Rs will always be 2. So, row 2 must have the correct format and whatever formulas you have.
BTW, I wouldn't use E to determine the LastRow even if E always has a value. Your code clearly shows that B and C have values. So, your code will be easier to read if you determine LastRow by B or C.
I also recommend that you make your two functions, LastRow and AddRow Private. They are used only within the module where they are in. Giving them a Public scope can't improve their performance but might cause unexpected problems. True, that is rather unlikely, but that is exactly the reason why the trouble will be difficult to shoot when and if it raises its ugly head. So, if the need is local, don't use global tools.

Finally, I point out that you don't seem to have taken my advice about the Timer. I wrote:-

Figure out how to make it stop when you want it to stop.

That isn't as easy as it is to start the Timer. You may have to invest some time into study until you have it figured out. To tell you the truth, I tried once and failed. That task was a little different from yours, but I remember enough to have some respect for it.

[chrisjwhite90]

Thank you for the help, I've completely redesigned my code so it's easier to see and I think I've got it.

Below is my almost finished code. I don't know if I've called AddRow and set up the following called functions correctly in the Sub ScheduleRoecordData(). Also, I dont know whether to combine TimeStamper and CopyPasteValues into one, or leave them seperately, your thoughts? Please point out any issues that you see! I'm excited to get this thing running asap.

Code: Select all

Dim TimeToRun

Sub auto_open()
    Call ScheduleRecordData
End Sub


Sub ScheduleRecordData()
    AddRow R, 2
    TimeToRun = Now + TimeValue("00:01:00")
    Application.OnTime TimeToRun, "CopyPasteValues", "TimeStamper"
End Sub


Sub CopyPasteValues()
'
' CopyPasteProductValues Macro
' Simple copy and paste into row below, taken from a personal recorded macro

    Range("B2:BV2").Select
    Selection.Copy
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Call ScheduleRecordData
End Sub


Sub TimeStamper()
    Range("A3").Value = Now
    Range("A3").NumberFormat = "m-d-yyyy, H:MM:SS AM/PM"
    Call ScheduleRecordData
End Sub


Sub AddRow(Optional ByVal Rt As Long, _
                  Optional ByVal Rs As Long, _
                  Optional ByRef Ws As Worksheet)

    If Ws Is Nothing Then Set Ws = ActiveSheet
    If Rt < 1 Then Rt = LastRow(1, Ws) + 1
    If Rs < 1 Then Rs = Rt - IIf(Rt = 1, 0, 1)
   
    With Ws
        .Rows(Rs).Copy
        .Rows(Rt).Insert xlShiftDown
        Application.CutCopyMode = False
        On Error Resume Next
        With .Rows(Rt)
            .Cells.SpecialCells(xlCellTypeConstants).ClearContents
            .Cells(1).Select
        End With
    End With
End Sub


Function LastRow(Optional ByVal Col As Variant, _
                         Optional Ws As Worksheet) As Long

    ' Return the number of the last non-blank row in column Col.
    ' Specify the column as string or number
    ' If no column is specified,
      ' return the last row from column A.
    ' If no worksheet is specified
      ' return the result from the currently active sheet.
   
    Dim R As Long
   
    If Ws Is Nothing Then Set Ws = ActiveSheet
    If VarType(Col) = vbError Then Col = 1
    With Ws
        R = .Cells(.Rows.Count, Col).End(xlUp).Row
        With .Cells(R, Col)
            ' in a blank column the last used row is 0 (= none)
            If R = 1 And .Value = vbNullString Then R = 0
            ' include all rows of a merged range
            LastRow = R + .MergeArea.Rows.Count - 1
        End With
    End With
End Function


Sub auto_close()
    On Error Resume Next
    Application.OnTime TimeToRun, "CopyPriceOver", , False
End Sub


[chrisjwhite90]

Ran the code, and instead of recording the values in row 2 every minute, it somehow copies the row and exponentially pastes it. It started to overload my machine, so I broke the code and tried to debug, but I can't figure out the issue!

I hope I correctly stopped the issue, you may need to quickly break the code to make sure it doesnt do the same to you. Looking at the macros, I've placed commas in certain parts to stop it from automatically doing this.

Also, I have colored formatting in row 2, but I don't want the same formatting to be copied down when it pastes the values in the following rows. How do I fix something like this?

I will attach my spreadsheet here so I can get some constructive criticism on what to do. For some reason I can't attach files on the forum ( I click Add the File and it doesn't do anything), so click the shared link to see the spreadsheet.

http://www.filedropper.com/datacollection

[Sisyphus]

Your problem results from insufficient testing of code components. You must write a little project that just copies the code from row 2 and pastes it to the last row in the sheet. Running it repeatedly should add a new row each time.
You must also, separately, test the code that updates row 2.
Finally, you must test your timer. For this purpose I suggest that you just code to count numbers in a single cell. Let your timer increment the number in that cell every 10 seconds. Only after that works perfectly, and you know how to start and end the cycle, should you put all your code together.
Instead of proper tests you have gone from writing code to a trial run. That is too fast. You can't tell where the error comes from or how many errors there are. Designing and writing tests is the more boring part of code writing - about 60% to 80% of the task. However, you will be rewarded by seeing your code run smoothly if it was carefully assembled from fully tested components.
I don't understand why you wish to run the code every minute unless you are updating row 2 every minute. There seems to be no point in testing code that copies row 2 every minute unless data in row 2 have changed.
If you wish to post worksheets please attach them to your post on this site. It is against forum rules to post attachments elsewhere.