Hello,
I am juststarted learning VBA and needed some help on the creation of a macro to recorda set of values and put in a time stamp as well.
The spreadsheetis set up with the second row (row 2) with a value in each cell goinghorizontally, from column to column. These cells are continuously updated witha link to provide a live number of a specific values, whose names are in row 1above it.
I would like torecord each of these values at the same time, every single 1 minute timeinterval, to keep a log of them. As the values change/update, I'd like all thevalues in the 2nd row (the ones that are continuously updating) to be copiedand pasted to the row below, and in the first column (column A) have a timestamp of the exact time(every minute) that this value was recorded. At the nextminute, I want this to happen again, but this time the value pasted below thepreviously recorded/pasted row (so lets say at 12:00:00 the values are pastedin row 3, and at 12:01:00 the values are copied and pasted into row 4), so thatas time passes. a new row is formed with new snapshot values of the live cellsin row 2. I want it to be able to run continuously (infinitely recording valuesdownward) until I manually break the macro myself (I know this will be a hugeamount of data, but I can manage that).
Also, how wouldI be able to adjust the spreadsheet macro in case I decide to add even moreupdating cells in row 2, thereby using more columns to record data. So forexample, at first I'm keeping track of let's say 10 variables, range(B2:K2),but next week I want to keep track of range(B2:BB2). Is there a specific way Ican update the macro in order to run it the same, but now also incorporate thenew value recorded and time stamped, just like the others ( I realize that the timestamps for these "extra" products added in will not have thepreviously recorded data as the others would, and therefore would start waydown the rows in sync with the others based on the time I start recording), andwhere specifically would I change this value to incorporate the new range? Iwant this all contained within the same sheet of the workbook, to automaticallyrecord even as I do other stuff in other workbooks/worksheets.
This being said, I started some code, so I don’t have to start from scratch. Please seethe attached code (below) to give the best advice.
The function/macro "LastRow" was one I received from some help on anotherwebsite, to add into mine:
[VBA]Private Function LastColumn(Optional ByVal Rw As Long, Optional Ws As Worksheet) As Long
' 0056 V 2.1 Apr 2, 2012
' Return the number of the last non-blank column in row Rw.
' If no row is specified,
' return the last column from row 1.
' If no worksheet is specified,
' return the result from the currently active sheet.
Dim C As Long
If Ws Is Nothing Then Set Ws = ActiveSheet
Rw = IIf(Rw, Rw, 1)
With Ws
C = .Cells(Rw, .Columns.Count).End(xlToLeft).Column
With .Cells(Rw, C)
' in a blank row the last used column is 0 (= none)
If C = 1 And .Value = vbNullString Then C = 0
' include all columns of a merged range
LastColumn = C + .MergeArea.Columns.Count - 1
End With
End With
End Function[/VBA]
But I don't know how to implement it accurately to get the results I want. Do Ijust add the function underneath, and which part of the code do I change to fitmy needs? I would appreciate any step by step help on this.
I also received some other code to add a time stamp to each row, but I don't know how to factorit into the macro to make it work.
[VBA]Public Sub CreationDate(ByRef Target As Range)
Const CreateColumn As String = "B"
With Target
With .Worksheet.Cells(.Row, CreateColumn)
If Not IsDate(.Value) Then .Value = Date
.Offset(0, 1).Select
End With
End With
End Sub[/VBA]
I need help compiling this huge project to make it work efficiently.
I know how to make a button of this to initiate the macro, so that won’t be a problem.
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!
CODE: (this is all I have so far)
[VBA]SubValueStore()
Dim dTime AsDate
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[/VBA]
Reply With Quote