Michaelpfreem
Board Regular
- Joined
- Mar 14, 2008
- Messages
- 92
Hello all, I am having a little for loop problem (see code below)
For Crit1 = 1 To 42
For Crit2 = 2 To 43
Sheets("SortData").Select
Selection.AutoFilter Field:=1, Criteria1:=">=" & Crit1, Operator:=xlAnd, _
Criteria2:="<=" & Crit2
Range("A2:B20000").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("I1:AV1").Select
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet2").Select
Range("A2:B20000").Select
Selection.ClearContents
Next Crit2
Next Crit1
As you can see I am trying to get to different numbers (crit1 and crit2) to loop through a given number of changes. I have up and till now just been repeating this code and changing the numbers in the line auotfilter line. this leads to huge macros. I know you can write for loops to change one number at a time, in fact that is all that seems to happen with this macro only crit2 changes, crit1 stays the same.
So i am wondering is it possible to get the for loop to change both unmbers that I need for my autofiltering to work.
Many thanks in advance,
Mike
For Crit1 = 1 To 42
For Crit2 = 2 To 43
Sheets("SortData").Select
Selection.AutoFilter Field:=1, Criteria1:=">=" & Crit1, Operator:=xlAnd, _
Criteria2:="<=" & Crit2
Range("A2:B20000").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("I1:AV1").Select
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Sheet2").Select
Range("A2:B20000").Select
Selection.ClearContents
Next Crit2
Next Crit1
As you can see I am trying to get to different numbers (crit1 and crit2) to loop through a given number of changes. I have up and till now just been repeating this code and changing the numbers in the line auotfilter line. this leads to huge macros. I know you can write for loops to change one number at a time, in fact that is all that seems to happen with this macro only crit2 changes, crit1 stays the same.
So i am wondering is it possible to get the for loop to change both unmbers that I need for my autofiltering to work.
Many thanks in advance,
Mike