Noob Looking for Help with VBA

[Bastiaan]

Hey everyone,

For a project at Uni I'm analyzing a big package of Data. (Not allowed to go into to much detail about the data.)
Currently im trying to analyze the data using VBA in Excel.
However I've never programmed in my life.
For the past week I've been trying to make VBA do what I want by looking through forums.
This however is consuming to much time to find the specific scripts for my problems.
I was wondering if this forum(someone) can help me with formulating my scripts and at the same time make me understand what each line of code does.

Heres an example of something i've been struggling with to get:


My data is ordered by Date and time :'" 31-1-2018 0:00:00"

2 big sets of minute data and 5 minute data

within these sets there are "events" I need to filter out.

An event is for example between 2017-09-13 10:10 -
2017-09-13 22:55

I want to copy the Evenrows in this time frame and paste them into a new workbook without open spaces

Same for the Unevenrows in the same workbook but below the evenrow set

Then when im copying from the 5 minute data set i need to convert this to 1 minute data.

which is just copying the row 4 times beneath and adding +1 to the minutes

Alright here's what i got so far: ( not much )

Sub CopyRowsAcross()
Dim i As Integer
Dim ws1 As Worksheet:
Set ws1 = ThisWorkbook.Sheets("Blad1")
Dim ws2 As Worksheet:
Set ws2 = ThisWorkbook.Sheets("Blad2")

For i = 2 To ws1.Range("E80000").End(xlUp).Row

If ws1.Cells(i, 5) = 1-9-2017 0:00:00 Then ws1.Rows(i).Select

'So far it worked however It doesnt recognize the Date + time only as an actual number.

'Row.Select.Even untill Cells(i,5) 2017-09-13 22:55. '

Copy ws2.Rows(ws2.Cells(ws2.Rows.Count, 2).End(xlUp).Row + 1)

Next

'In new worksheet convert from 5 minute data to 1 minute data'
'By inserting the first row 4 time and only adding 1 minute untill going to the next row.
'copy the worksheet into a new workbook named the Start and End moment : 2017-09-13 10:10 -
2017-09-13 22:55'

End Sub


Is this something you can help me with on a consistent basis over the next month.

If this is considered a rude question or something that is "obviously" to much work / hassle than I appoligize in advance for my ignorance.

I'm not trying to have some1 do my project for me. If some1 is willing to reply to an email/pm on a daily basis I can also compensate this person if wanted.

Anyway thanks for the attention and appoligies for my english/grammar (im not a native speaker)

















[TABLE="width: 104"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[PatOBrien198]

If it were me, I'd record a macro doing what you want it to do, then customize the code to fit your flexibility needs.

I'd add a NEW column with the following formula: =ISEVEN(ROW())
This will return a TRUE or a FALSE (for the odd numbered rows)

Then create a filter on your data,
Set a filter condition on the date column (Dates between)
Set the NEW column filter to "TRUE"
Copy the results to your new worksheet,
Set the NEW column filter to "FALSE"
Copy the results to your new worksheet

you may need to create from scratch the code to insert 4 new rows, incrementing the minutes by 1, but we can get into this later....

then "Move or Copy" the worksheet to a new workbook, give it a name, save it, close it

return to your main workbook, remove the filter selections
etc.....

 

[Bastiaan]

Thanks for the Autofilter Tip

This is my Current Code :

Sub FilterByDate()
Dim dDate As Date
Dim dDate2 As Date
Dim strDate As String
Dim Date1 As Double
Dim Date2 As Double
AutoFilterMode = False
 
 
    dDate2 = DateSerial(2017, 9, 13) , TimeSerial(10, 10, 0) 'Lagere Waarde
    dDate = DateSerial(2017, 9, 13) , TimeSerial(22, 55, 0) 'Hogere waarde
    Date1 = dDate
    Date2 = dDate2

    
With Columns(5)
        .AutoFilter Field:=1, Criteria1:=">=" & Date2, Criteria2:="<=" & Date1

AutoFilterMode = False
End With
End Sub

However my Autofilter data is filtering on the following value 429914236111111
And when i look for the true value of that specific cell its 42991,4236111111
Any1 got a suggestion?

 

[Bastiaan]

EDIT my COde is the Following :

Sub FilterByDate()
Dim dDate As Date
Dim dDate2 As Date
Dim strDate As String
Dim Date1 As Double
Dim Date2 As Double
AutoFilterMode = False
 
 
    dDate2 = DateSerial(2017, 9, 13) + TimeSerial(10, 10, 0) 'Lagere Waarde
    dDate = DateSerial(2017, 9, 13) + TimeSerial(22, 55, 0) 'Hogere waarde
    Date1 = dDate
    Date2 = dDate2

    
With Columns(5)
        .AutoFilter Field:=1, Criteria1:=">=" & Date2, Criteria2:="<=" & Date1

AutoFilterMode = False
End With
End Sub

 

[Bastiaan]

Found it ! Date1 and Date 2 As Long was the awnswer

 

[Bastiaan]

NVM i was wrong

 

[Bastiaan]

-

 

[Bastiaan]

.AutoFilter Field:=1, Criteria1:=">=" & Str(Date2), Criteria2:="<=" & Str(Date1)
was the solution