Hi all:
I want to filter Column(A) from sheets(main).range(H1:lastrow), then Filter column(B) = "R-1234"
After Filter then find row Max, Min, Average in Sheet(A1234).range(Q2:Q9999)
Fill "MAX", "MIN" in column(R), Fill average.Value in column(S)
My code belove but can run't that I want. It's alway find Max, Min value of all data, not in filter data.
Please help me...
I want to filter Column(A) from sheets(main).range(H1:lastrow), then Filter column(B) = "R-1234"
After Filter then find row Max, Min, Average in Sheet(A1234).range(Q2:Q9999)
Fill "MAX", "MIN" in column(R), Fill average.Value in column(S)
My code belove but can run't that I want. It's alway find Max, Min value of all data, not in filter data.
Please help me...
VBA Code:
Public Sub Get_DATA()
Dim cn As Object, rs As Object, i As Byte, lr As Long, lR2 As Long, z As Long, fso As Object
Dim lrow As Long, lRowI As Long, ans As Long, ii As Long, x As Long, y As Long, iRng As Range, iMin As Double, iMax As Double
With ThisWorkbook.Sheets("main")
lrow = .Cells(Rows.Count, "H").End(xlUp).Row
For i = 1 To lrow
ans = Sheets("main").Range("H" & i).Value
lRowI = Sheets("A1234").Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("A1234")
.Range("A1:A" & lRowI).AutoFilter Field:=1, Criteria1:="*" & ans & "*", Operator:=xlOr, Criteria2:=ans
.Range("A1:A" & lRowI).AutoFilter Field:=2, Criteria1:="R-1234"
Set iRng = .Range("Q2:Q9999").SpecialCells(xlCellTypeVisible)
iMax = Application.WorksheetFunction.Max(iRng)
iMin = Application.WorksheetFunction.Min(iRng)
.Range("U" & .Range("C" & Rows.Count).End(xlUp).Row).Value = WorksheetFunction.Average(.Range("Q2:Q9999").SpecialCells(xlCellTypeVisible))
x = .Range("Q2:Q9999").Find(iMax).Row
y = .Range("Q2:Q9999").Find(iMin).Row
.Range("R" & x).Value = "MAX"
.Range("R" & y).Value = "MIN"
.Range("S" & x).Value = Application.WorksheetFunction.CountIfs(.Range("A2:A9999"), ans, .Range("Q2:Q9999"), "<" & .Range("Q1"))
.Range("A1:A" & lRowI).AutoFilter
End With
Next i
Application.CutCopyMode = False
End With
End sub