PDA

View Full Version : Adjusting macro to loop through criteria



rhudgins
04-21-2010, 12:15 PM
I would like to run the following macro in a loop for the five criteria listed in cells AE3:AG7. Right now it is only running the criteria for Example #1. Example #1 pastes the final data into cells F8:I22.

I would like example #2 to paste the final data into cell K8:N22.
I would like example #3 to paste the final data into cell P8:S22
I would like example #4 to paste the final data into cell U8:X22
I would like example #5 to paste the final data into cell Z8:AC22

Here is my macro. The macro is also in the attached spreadsheet.





Public CurRow As Long
Public Code As Long
Sub RunResults()
Sheets("Chopped Up Long Data Close").Select
RunIt
End Sub
Private Sub RunIt()


Range("AE3").Select
Code = ActiveCell.Value
If (Code > 0) Then
getreport (Code)
End If


End Sub


Private Sub getreport(Code As Integer)

'First line
Dim Rng As Range, MyCell As Range, i As Long
Set Rng = Range("A25:A66")
i = 102
For Each MyCell In Rng
If MyCell.Offset(0, 3).Value = Range("AF3") And MyCell.Offset(0, 4).Value = Range("AG3") Then
MyCell.EntireRow.Copy
Range("A" & i).PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
i = i + 1
End If
Next MyCell
Application.CutCopyMode = False



Range("A8:D22").Select
Selection.Copy
Range("F8:I22").Select
Selection.PasteSpecial paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


Cleanup

End Sub

Private Sub Cleanup()
Sheets("Chopped Up Long Data Close").Select
Range("A102:IU131").Select
Selection.ClearContents


End Sub