# For loop problem

#### Michaelpfreem

##### Board Regular
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.

Mike

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### VoG

##### Legend
Perhaps (untested)

Code:
``````For Crit1 = 1 To 42
Crit2 = Crit1 + 1
Sheets("SortData").Select
Selection.AutoFilter Field:=1, Criteria1:=">=" & Crit1, Operator:=xlAnd, _
Criteria2:="<=" & Crit2
Range("A2:B20000").Copy Destination:=Sheets("Sheet2").Range("A2")
Sheets("Sheet2").Range("I1:AV1").Copy
Sheets("Sheet3").Select
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Sheet2").Range("A2:B20000").ClearContents
Next Crit1
Application.CutCopyMode = False``````

#### Michaelpfreem

##### Board Regular
Thank you very much mate you are a hero, that code is also a little bit cleaner as well, I will have to take note of the way you arranged some of those lines for future use.

Thanks again

Replies
3
Views
312
Replies
1
Views
310
Replies
1
Views
162
Replies
2
Views
124
Replies
4
Views
108

1,191,579
Messages
5,987,428
Members
440,096
Latest member
yanaungmyint

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back