Hi!

Right at the bottom is the problem that i faced and i have slowly came up
with some long winded programming as shown just below. Is there a better and
more efficient way to rewrite my program? So far this portion sorts DataC in
descending order, and finds all instances of jan 92 and output in 1 row and
then finds all instances of jan 93 and output in the next row, and so forth,
resulting in 7 rows( jan92 to jan 98), and the first column is wat i actually
need, the rest is redundant cos i dun know how to look for the largest value.
Is there a way to loop this for highest dataD, highest dataE and so forth to
dataN? Or do i have to just cut and paste 12 times? Thanks!!!

Sheets("Datasheet2").Select
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("g2") _
, Order2:=xlDescending, Key3:=Range("D2"), Order3:=xlAscending,
Header _
:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 92 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(2, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 93 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(3, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 94 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(4, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 95 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(5, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 96 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(6, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 97 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(7, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 98 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(8, 22).Offset(0, j)
j = j + 4
End If

Next i



-------------------------------------------------------------------------
i have data in the form

mth n(day of yr) year dataA dataB dataC ..... dataN
1 24 94 .. .. .. ..
1 10 97 .. .. .. ..


the data is from 1jan 92 to 31dec 98, and i have sorted in order of month.
the purpose is to obtain the highest dataC value for jan92 , jan93, ... jan98
and output the month, n(day of yr) and year, probably in another sheet.
Similarly, this is done for feb92 to feb98, and for the rest of the months.
After this is done, this process is repeated for highest value of dataD, and
dataE, and so on, up to dataN.

Currently, i am manually sorting dataC in descending order and looking for
the highest value for each month from 92 to 98, and doing dataD now. As there
is over 2000 rows of data, how can i write a VBA program to automate this
process? Thanks in advance!

Oh i juz realised how inefficient my code is...i have 12 time periods and 12
months of data each...so if i have to cut and paste the code, i would have to
do it 144 times. Please advise!

"KiriumF1" wrote:

Hi!

Right at the bottom is the problem that i faced and i have slowly came up
with some long winded programming as shown just below. Is there a better and
more efficient way to rewrite my program? So far this portion sorts DataC in
descending order, and finds all instances of jan 92 and output in 1 row and
then finds all instances of jan 93 and output in the next row, and so forth,
resulting in 7 rows( jan92 to jan 98), and the first column is wat i actually
need, the rest is redundant cos i dun know how to look for the largest value.
Is there a way to loop this for highest dataD, highest dataE and so forth to
dataN? Or do i have to just cut and paste 12 times? Thanks!!!

Sheets("Datasheet2").Select
Cells.Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range("g2") _
, Order2:=xlDescending, Key3:=Range("D2"), Order3:=xlAscending,
Header _
:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 92 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(2, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 93 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(3, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 94 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(4, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 95 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(5, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 96 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(6, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 97 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(7, 22).Offset(0, j)
j = j + 4
End If
Next i

j = 0
For i = 0 To 217
If Cells(2, 2).Offset(i, 0).Value = 1 And Cells(2, 2).Offset(i,
2).Value = 98 Then
Cells(2, 2).Offset(i, 0).Resize(1, 3).Copy _
Destination:=Cells(8, 22).Offset(0, j)
j = j + 4
End If

Next i



-------------------------------------------------------------------------
i have data in the form

mth n(day of yr) year dataA dataB dataC ..... dataN
1 24 94 .. .. .. ..
1 10 97 .. .. .. ..


the data is from 1jan 92 to 31dec 98, and i have sorted in order of month.
the purpose is to obtain the highest dataC value for jan92 , jan93, ... jan98
and output the month, n(day of yr) and year, probably in another sheet.
Similarly, this is done for feb92 to feb98, and for the rest of the months.
After this is done, this process is repeated for highest value of dataD, and
dataE, and so on, up to dataN.

Currently, i am manually sorting dataC in descending order and looking for
the highest value for each month from 92 to 98, and doing dataD now. As there
is over 2000 rows of data, how can i write a VBA program to automate this
process? Thanks in advance!

I am not expert or guru in VBA. I am also learning from this NG. Since
no one has answered your post, I will try.

I could not understand what exactly you need but still....
From your code, it seems that you are acting on value of cell is
93,94,95 etc. In such cases you can use If..Elseif.
You can also wrap the code to loop thru all the cells which will reduce
the length of code.

You can define a range for the highest datac, datad....datan. Then you
can use for each or do while or do until loop as the case may be to
loop thru the range.

Hope this is useful
Regards,
Shetty