Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counter in "If" condition
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counter in "If" condition
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 -- Rick (MVP - Excel) "rjagathe" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Assigning "Counter" for Groups | Excel Programming | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
row by row comparison using a "countif" and "and" condition? | Excel Programming | |||
How do I set up a "roll over" counter in excel 2003? | Excel Worksheet Functions | |||
multiple file uploading - runtime error'13': type mismatch "While Counter <= UBound(FName)" | Excel Discussion (Misc queries) |