Why don't you use an arrayformula in O33 ?
PHP Code:
={SUM(COUNTIF(N10:P31;$A$2:$F$2))}
Why don't you use an arrayformula in O33 ?
PHP Code:
={SUM(COUNTIF(N10:P31;$A$2:$F$2))}
Sorry yujin,
in my previous post I have attached a wrong file _80.... instaed of the one here joined
RIC
RIC,
I'm not sure this is what you want but please give it a try.
Sub PuregeFile() Dim vPar As Integer Dim vMin(2) As Integer, vMax(2) As Integer Dim vTotalMax As Integer Dim rng(16) As Range Dim i As Integer, j As Integer, n As Integer Dim ReadLine As String Dim buf Dim cnt1 As Integer, cnt2 As Integer, cnt3 As Integer vPar = Range("Y8") vMin(0) = Range("AV4"): vMin(1) = Range("AV6"): vMin(2) = Range("AV8") vMax(0) = Range("AX4"): vMax(1) = Range("AX6"): vMax(2) = Range("AX8") vTotalMax = Range("AZ7") For i = 0 To 16 Set rng(i) = Range("H10").Offset(, i * 3).Resize(15, 3) Next i Open ThisWorkbook.Path & "\_ToPurgeFile.csv" For Input As #1 Open ThisWorkbook.Path & "\OutputFile.csv" For Output As #2 Do Until EOF(1) Line Input #1, ReadLine buf = Split(ReadLine, " ") cnt3 = 0 For n = 0 To 2 cnt1 = 0 For i = n * 4 + 2 To n * 4 + 5 cnt2 = 0 For j = 0 To 5 cnt2 = cnt2 + WorksheetFunction.CountIf(rng(i), buf(j)) Next j If cnt2 = vPar Then cnt1 = cnt1 + 1 End If Next i If (cnt1 >= vMin(n) And cnt1 <= vMax(n)) Then cnt3 = cnt3 + cnt1 End If Next n If cnt3 > 0 And cnt3 <= vTotalMax Then Print #2, ReadLine End If Loop Close #2 Close #1 End Sub
Hi yujin
surely I made mistakes in trying to explain what I would like
the check that I would like to do must be limited to groups containing data, therefore 12 and not 17 groups, I tried to adjust the code - see the three comments I entered - but it stops at the line that contains
cnt2 = cnt2 + WorksheetFunction.CountIf (rng (i), buf (j))
and based o my poor knowledge I can't find where the problem lies
based on the check that I did at least the two groups of six numbers:
49 55 66 78 85 87
9 23 36 55 83 90
they should be accepted therefore present in the Outputfile file
RIC
Hi Ric,
You don't have to modify my code.
I defined the range array rng(16) but only data-containing ranges, from rng(2) to rng(13), are checked.
If you run the code, OutputFile.csv gets 44 groups of six numbers containing "9 23 36 55 83 90" and "49 55 66 78 85 87".
morning yujin
as usual you're right ... I didn't pay the right attention in studying the code you wrote ... in my opinion it didn't return the strings of expected numbers because I hadn't noticed that the instruction:
If cnt3> 0 And cnt3 <= vTotalMax Then
accepts range of values from 0 up to the value of the variable vTotalMax when instead only strings that satisfy only the value of vTotalMax must be accepted
I changed the line to
If cnt3 = vTotalMax Then
and now I get what I wanted !! :-)
;-) thanks again for your availability and patience
sincerely
RIC63
Hello, RIC
I'm pleased that I could be of any help of you :-)
It's kind of fun for me to find out the code someone needs.