loops
I’ll start with my spread sheet layout and explain in steps what I’m trying to achieve, I will then say what I’ve tried code wise so far but everything I have put together so far fails
My small version of my spread sheet is attached the full spread sheet is 17MB and alot of rows test-full.xls
[Spreadsheet1.png]
It consists of
make,model,size1,size2,size3,fuelsystem1,fuelsystem2,fuelsystem3,years,filterref
the actions i want to replicate in code First I filter by make, then model, then filter ref, then fuelsystem1 (petrol)
[Spreadsheet2.png]
I use these autofilters to match a filter into year ranges if a size falls into the year range they get merged (copied to the next free size column of the same year range)
I then delete any rows with empty size data [Spreadsheet4.png]
[Spreadsheet3.png]
The last filter (fuelsystem1) is done a second time to filter the diesel or other fuel types and do the previous step of merging size by year again I then go on to filter the next filterref to apply sizes to year ranges The resulting spreadsheet is
[Spreadsheet result.png]
I then go on to filter the next model in model column within that make and repeat the above steps on that model I then repeat all the above for the next make in make column I did consider use of a pivot table but don’t know how to manipulate them in VBA (this is my first time doing something more than a simple function in VBA) if I cant figure it out in VBA then I’m going to go to python to save the day
Code I have tried
I have tried to find a way to loop through all criteria’s of an autofilter seems vba has no built in function to perform something like Criteria:=Array(=Criteria
)
So I created a separate table with all my unique values to use as criteria in a separate sheet. Thought I could load these as an array and pass them to the search criteria and loop on each filter is this the right way to tackle this And trying to work out how I can search through them all
]ActiveSheet.Range(“A:I”).AutoFilterField:1,_ Criteria:Array(),Operator:=xlFilterValuesSo my idea was to load the following into Arrays
Make Model Filter Fuel
Dim makeArray AsVariant
Dim totalRows AsLong
Dim makeLoop AsInteger
totalRows = Rows(Rows.Count.End(xlUp).Row
makeArray=Range(Cells(1,1)Cells(totalRows,1)).Value
For makeLoop = LBound(makeArray)To UBound(makeArray)
With Sheets(“Sheet1”)
.AutoFilterMode =False
.UsedRange.AutoFilter
.UsedRange.AutoFilter field:=1, Criteria1:=makeArray(makeLoop), Operator:=xlFilterValues
EndWith
Next makeLoop]Should I nest the for loops or does VBA have better ways to do this
this was my intention
Loop through search criteria makeArray() Set filter range ¬ loop through modelArray() Set filter range ¬ loop through filterArray() Set filter range ¬loop through fuelArray() Set filtered range
Look at this range and work out date ranges that fall into each other then copy size for each into these Then return should go in to the next filterArray() loop
Is this possible with VBA? I program in Python mainly so considering this route but thought I’d check VBA first as its built into excel and therefore should have more control over a spreadsheet (I’d have thought) although it can’t get the autofilters criteria list or remove duplicates from an array simply and i noticed you have to declare the length of the array first so i have to count the rows
should i just go with good old python as i really have no idea when it comes to VBA
sorry if the snippets of code are a bit broken but i'm on my centos laptop at the moment so no excel handy
the link to the spreadsheet and images as SO wont let me post on this account
https://drive.google.com/open?id=0Bx...0dMb2duRzRialU
so i had a go at it today just to get the filter loops working and copy out the data to a new sheet but its not working
VB:
Sub test()
Dim MakeLoop As Integer
Dim ModelLoop As Integer
Dim filterLoop As Integer
Dim fuelLoop As Integer
For MakeLoop = 2 To 58
Selection.AutoFilter Field:=1, Criteria1:=Worksheets("values").Cells(MakeLoop, 1).Value
For ModelLoop = 2 To 1415
Selection.AutoFilter Field:=2, Criteria1:=Worksheets("values").Cells(ModelLoop, 1).Value
For filterLoop = 2 To 1663
Selection.AutoFilter Field:=9, Criteria1:=Worksheets("values").Cells(filterLoop, 1).Value
For fuelLoop = 2 To 12
Selection.AutoFilter Field:=6, Criteria1:=Worksheets("values").Cells(fuelLoop, 1).Value
Dim validRow As Range, rw As Range
For Each validRow In Selection.Areas
For Each rw In validRow.Rows
If rw.Row >= 2 Then
rw.EntireRow.Copy Sheet3.Cells(2 + (rw.Row - 2) * 3, 1)
End If
Next rw
Next validRow
Next fuelLoop
Next filterLoop
Next ModelLoop
Next MakeLoop
End Sub
the idea was to copy the data out then manipulate it by merging the years that fall inside of each other e.g 2006/2010 fits inside 2001/2011 but cant event get that far
Seems a very intensive way to do it
I’ll start with my spread sheet layout and explain in steps what I’m trying to achieve, I will then say what I’ve tried code wise so far but everything I have put together so far fails
My small version of my spread sheet is attached the full spread sheet is 17MB and alot of rows test-full.xls
[Spreadsheet1.png]
It consists of
make,model,size1,size2,size3,fuelsystem1,fuelsystem2,fuelsystem3,years,filterref
the actions i want to replicate in code First I filter by make, then model, then filter ref, then fuelsystem1 (petrol)
[Spreadsheet2.png]
I use these autofilters to match a filter into year ranges if a size falls into the year range they get merged (copied to the next free size column of the same year range)
I then delete any rows with empty size data [Spreadsheet4.png]
[Spreadsheet3.png]
The last filter (fuelsystem1) is done a second time to filter the diesel or other fuel types and do the previous step of merging size by year again I then go on to filter the next filterref to apply sizes to year ranges The resulting spreadsheet is
[Spreadsheet result.png]
I then go on to filter the next model in model column within that make and repeat the above steps on that model I then repeat all the above for the next make in make column I did consider use of a pivot table but don’t know how to manipulate them in VBA (this is my first time doing something more than a simple function in VBA) if I cant figure it out in VBA then I’m going to go to python to save the day
Code I have tried
I have tried to find a way to loop through all criteria’s of an autofilter seems vba has no built in function to perform something like Criteria:=Array(=Criteria
)So I created a separate table with all my unique values to use as criteria in a separate sheet. Thought I could load these as an array and pass them to the search criteria and loop on each filter is this the right way to tackle this And trying to work out how I can search through them all
]ActiveSheet.Range(“A:I”).AutoFilterField:1,_ Criteria:Array(),Operator:=xlFilterValuesSo my idea was to load the following into Arrays
Make Model Filter Fuel
Dim makeArray AsVariant
Dim totalRows AsLong
Dim makeLoop AsInteger
totalRows = Rows(Rows.Count.End(xlUp).Row
makeArray=Range(Cells(1,1)Cells(totalRows,1)).Value
For makeLoop = LBound(makeArray)To UBound(makeArray)
With Sheets(“Sheet1”)
.AutoFilterMode =False
.UsedRange.AutoFilter
.UsedRange.AutoFilter field:=1, Criteria1:=makeArray(makeLoop), Operator:=xlFilterValues
EndWith
Next makeLoop]Should I nest the for loops or does VBA have better ways to do this
this was my intention
Loop through search criteria makeArray() Set filter range ¬ loop through modelArray() Set filter range ¬ loop through filterArray() Set filter range ¬loop through fuelArray() Set filtered range
Look at this range and work out date ranges that fall into each other then copy size for each into these Then return should go in to the next filterArray() loop
Is this possible with VBA? I program in Python mainly so considering this route but thought I’d check VBA first as its built into excel and therefore should have more control over a spreadsheet (I’d have thought) although it can’t get the autofilters criteria list or remove duplicates from an array simply and i noticed you have to declare the length of the array first so i have to count the rows
should i just go with good old python as i really have no idea when it comes to VBA
sorry if the snippets of code are a bit broken but i'm on my centos laptop at the moment so no excel handy
the link to the spreadsheet and images as SO wont let me post on this account
https://drive.google.com/open?id=0Bx...0dMb2duRzRialU
so i had a go at it today just to get the filter loops working and copy out the data to a new sheet but its not working
VB:
Sub test()
Dim MakeLoop As Integer
Dim ModelLoop As Integer
Dim filterLoop As Integer
Dim fuelLoop As Integer
For MakeLoop = 2 To 58
Selection.AutoFilter Field:=1, Criteria1:=Worksheets("values").Cells(MakeLoop, 1).Value
For ModelLoop = 2 To 1415
Selection.AutoFilter Field:=2, Criteria1:=Worksheets("values").Cells(ModelLoop, 1).Value
For filterLoop = 2 To 1663
Selection.AutoFilter Field:=9, Criteria1:=Worksheets("values").Cells(filterLoop, 1).Value
For fuelLoop = 2 To 12
Selection.AutoFilter Field:=6, Criteria1:=Worksheets("values").Cells(fuelLoop, 1).Value
Dim validRow As Range, rw As Range
For Each validRow In Selection.Areas
For Each rw In validRow.Rows
If rw.Row >= 2 Then
rw.EntireRow.Copy Sheet3.Cells(2 + (rw.Row - 2) * 3, 1)
End If
Next rw
Next validRow
Next fuelLoop
Next filterLoop
Next ModelLoop
Next MakeLoop
End Sub
the idea was to copy the data out then manipulate it by merging the years that fall inside of each other e.g 2006/2010 fits inside 2001/2011 but cant event get that far
Seems a very intensive way to do it
