counter in "If" condition

[rjagathe]

I have data as follows:

A B C D

COUNTRY SEX AGE SELECTION
1 INDIA F 10 IND F 30 1
2 INDIA F 45 IND ALLSEX NOAGEBAR 1
3 INDIA M 15 IND ALLSEX NOAGEBAR 2
4 USA F 12
5 INDIA M 35 IND ALLSEX NOAGEBAR 3
6 ENGLAND F 29
7 INDIA F 14 IND F 30 2
8 ENGLAND F 13
9 INDIA F 25 IND F 30 3
10 INDIA F 12 IND F 30 4
11 ENGLAND M 12
12 USA M 23
13 INDIA F 15 IND F 30 5
14 INDIA F 45 IND ALLSEX NOAGEBAR 4
15 INDIA F 13 IND ALLSEX NOAGEBAR 5
16 USA M 23
17 INDIA F 24
18 ENGLAND M 25
19 INDIA F 26
20 INDIA F 45


Taking into account cols A,B and C,
I have to select first 5 rows satisfying criteria:
country=India ,sex=f and age below 30 and populate Column D as shown
above.Then I have to continue from row 1 and make another selection of
5 more rows with criteria : country=India,sex= all and age no bar and
populate Col D.The number of selection may vary in multiples of 5 and
data in col D should contain a counter as IND F 30 1,IND F 30 2 ,IND
F 30 3, etc.,

I want to automate this using vba.Please help.

 

[Don Guillett]

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

 

[Rick Rothstein]

Give this macro a try...

Sub TopFives()
Dim X As Long, Z As Long, LastRow As Long
Dim Count30 As Long, CountAll As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = 1 To 2
For Z = 2 To LastRow
If X = 1 Then
If UCase(Cells(Z, "A").Value) = "INDIA" And _
UCase(Cells(Z, "B").Value = "F") And _
Cells(Z, "C").Value <= 30 Then
Count30 = Count30 + 1
Cells(Z, "D").Value = "IND F 30 " & Count30
End If
If Count30 = 5 Then Exit For
Else
If UCase(Cells(Z, "A").Value) = "INDIA" And _
Cells(Z, "D").Value = "" Then
CountAll = CountAll + 1
Cells(Z, "D").Value = "IND ALLSEX NOAGEBAR " & CountAll
End If
If CountAll = 5 Then Exit For
End If
Next
Next
End Sub