Hi, I've worked all weekend to learn VBA. I came close, but code is probably what a proper coder would call (very) ugly. I would really appreciate tips on how to make a more streamlined code in addition to the key question:
A rather big set of data are collected on sheet 1 ("Disorderly"). Column A, C, E and so forth are timestamps of when the the measurement is done. Column B, D, F and so forth are the corresponding value of the measurement to the timestamp. I want to make a time-series analysis using the variables, hence it is important that the variables origin from the same time of measure. Some sensor have a malfunction a might skip a measurement or two. The code should collect the sensor vales which belongs to the same timestamp onto the same row in the next sheet ("Organized"). It get's a bit more complicated because there are two kinds of sensors, so it would be beneficial if the time value the code is searching for is a timestamp is within an interval (let's say 45 sec).
Any help will be much appreciated, hopefully I will learn enough VBA to help other too eventually (gotta start somewhere though).
Here's my filthy code (it was a test, so not every row is included because it takes rather long time to compile all the rows. Also this is without any kind of checking if the "what" part of the range.find function is within a time interval based on the cell which is the one we search for. The code does what it should, but there is a long way to go still. I am going to include code which ensures deleting of rows with empty cell and the empty columns in the "Organized" sheet ):
A rather big set of data are collected on sheet 1 ("Disorderly"). Column A, C, E and so forth are timestamps of when the the measurement is done. Column B, D, F and so forth are the corresponding value of the measurement to the timestamp. I want to make a time-series analysis using the variables, hence it is important that the variables origin from the same time of measure. Some sensor have a malfunction a might skip a measurement or two. The code should collect the sensor vales which belongs to the same timestamp onto the same row in the next sheet ("Organized"). It get's a bit more complicated because there are two kinds of sensors, so it would be beneficial if the time value the code is searching for is a timestamp is within an interval (let's say 45 sec).
Any help will be much appreciated, hopefully I will learn enough VBA to help other too eventually (gotta start somewhere though).
Here's my filthy code (it was a test, so not every row is included because it takes rather long time to compile all the rows. Also this is without any kind of checking if the "what" part of the range.find function is within a time interval based on the cell which is the one we search for. The code does what it should, but there is a long way to go still. I am going to include code which ensures deleting of rows with empty cell and the empty columns in the "Organized" sheet ):
VBA Code:
Private Sub CommandButton1_Click()
Dim rH As Long, Row As Long, Col As Long, CurrentRow As Long, NextCellValue As Double
Dim RowSize As Integer, ColSize As Integer
Dim foundRng As Range
Set DataArk = Worksheets("Disorderly")
Set ResArk = Worksheets("Organized")
'Copy the first two rows
rH = 1
For Row = 1 To 9
ResArk.Rows(Row).EntireRow.Value = DataArk.Rows(rH).EntireRow.Value
rH = rH + 1
Next Row
'Copy column 1 and 2 in DataArk
DataArk.Columns(1).Copy Destination:=ResArk.Columns(1)
DataArk.Columns(2).Copy Destination:=ResArk.Columns(2)
'Search through every second column for the associated timestamp in column 1
ColSize = DataArk.Cells(3, Columns.Count).End(xlToLeft).Column
For Row = 10 To 20
For Col = 3 To ColSize Step 2
RowSize = DataArk.Cells(Rows.Count, Col).End(xlUp).Row
Set foundRng = Range(DataArk.Cells(10, Col), DataArk.Cells(RowSize, Col)).Find(DataArk.Cells(Row, 1)) ', After:=DataArk.Cells(CurrentRow - 1, Col), SearchOrder:=xlByRows, SearchDirection:=xlNext)
If foundRng Is Nothing Then
ResArk.Activate
ResArk.Cells(Row, Col + 1) = ""
DataArk.Activate
Else
NextCellValue = DataArk.Cells(foundRng.Row, foundRng.Column + 1).Value
ResArk.Activate
ResArk.Cells(Row, Col + 1).Value = NextCellValue
DataArk.Activate
End If
Next Col
Next Row
End Sub
Last edited by a moderator:
