Hello all. I am trying to create a tool that will improve my life by importing, massaging and exporting data in the form I need. So far I have a separate submodule that imports all the data into excel, does some filtering, then combines the data side by side on one sheet.
Heres the issue. Each batch of data is only useful when compared at the same time, and due to the simplicity of the software downstream the output data must be presented on a single data sheet. My goal is to shift the data (not column headers) down so the times line up with the most important reference logger so I can do further manipulations and then export the info (ex: if logger_2 starts 15 minutes after logger_refrence then I would like the range of data for logger_2 to move down 15 rows).
I have already created a cell that will determine the number of rows the data needs to be moved down, I am running issues into moving the data down that many rows while also using a for loop so I can change the number of loggers used in the future.
All data is imported in n row by 6 column chunks and placed directly adjacent to each other (Columns A:F are logger 1, G:L is logger 2, etc). Cell with the value to move down is C1, I1, etc and is calculated in a different submodule. Columns start row 2 and if at all possible I'd like to leave them alone, just offset the data range.
I apologize for my poor code, I took a vba course in college and never looked back (and it shows).
Cheers!
Sub DownData()
'Start counter
Dim i As Integer
Dim k As Double
Dim LastCell As Range, RowLength As Long
Dim NumberImported As Integer
'Check Number of Columns
With Range("A2").EntireRow
Set LastCell = .Cells(1, .Columns.Count).End(xlToLeft)
End With
'Determine Length of Row2, determine number of imported CSV files
RowLength = LastCell.Column
NumberImported = (RowLength / 6)
'Range("D1") = NumberImported
'Re-set position/worksheet before running
ActiveSheet.Range("A1").Select
Worksheets("Combined_Data").Activate
'Count range. 1 to total minus one so first slot skips the sensor, then goes to sensor-1
For i = 1 To (NumberImported - 1)
'Position of the cell w/ number needed to move down <----Error occurs here
k = Range(1, (3 + (i * 6))).Value
'Range of the cells, from 3rd row per logger input (six * number in)
Range(Cells(3, (i * 6) + 1), Cells((k + 2), (i * 6) + 6)).Insert
Next
End Sub
Heres the issue. Each batch of data is only useful when compared at the same time, and due to the simplicity of the software downstream the output data must be presented on a single data sheet. My goal is to shift the data (not column headers) down so the times line up with the most important reference logger so I can do further manipulations and then export the info (ex: if logger_2 starts 15 minutes after logger_refrence then I would like the range of data for logger_2 to move down 15 rows).
I have already created a cell that will determine the number of rows the data needs to be moved down, I am running issues into moving the data down that many rows while also using a for loop so I can change the number of loggers used in the future.
All data is imported in n row by 6 column chunks and placed directly adjacent to each other (Columns A:F are logger 1, G:L is logger 2, etc). Cell with the value to move down is C1, I1, etc and is calculated in a different submodule. Columns start row 2 and if at all possible I'd like to leave them alone, just offset the data range.
I apologize for my poor code, I took a vba course in college and never looked back (and it shows).
Cheers!
Sub DownData()
'Start counter
Dim i As Integer
Dim k As Double
Dim LastCell As Range, RowLength As Long
Dim NumberImported As Integer
'Check Number of Columns
With Range("A2").EntireRow
Set LastCell = .Cells(1, .Columns.Count).End(xlToLeft)
End With
'Determine Length of Row2, determine number of imported CSV files
RowLength = LastCell.Column
NumberImported = (RowLength / 6)
'Range("D1") = NumberImported
'Re-set position/worksheet before running
ActiveSheet.Range("A1").Select
Worksheets("Combined_Data").Activate
'Count range. 1 to total minus one so first slot skips the sensor, then goes to sensor-1
For i = 1 To (NumberImported - 1)
'Position of the cell w/ number needed to move down <----Error occurs here
k = Range(1, (3 + (i * 6))).Value
'Range of the cells, from 3rd row per logger input (six * number in)
Range(Cells(3, (i * 6) + 1), Cells((k + 2), (i * 6) + 6)).Insert
Next
End Sub
