How to sort the data?
 

Hi thanks for your Help! But there's a slight problem, i couldn't run it as
there's an error, "Compile Error: Next without For"

I checked and dun think there's a problem, cos you used For Each and it
ended with Next. Could you advise me on this? Thanks!

"Tom Ogilvy" wrote:

Sheet to be copied to is Sheet2, sheet with data is Sheet1.


Dim rngYr as Range, rngDay as Range
Dim rng as Range, rngB as Range, rng1 as Range
Dim sAddr as String
With Worksheets("Sheet2")
set rngB = .range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End With
for each rngYr in rngB
if not isempty(rngYr) then
set rngDay = rngYr.offset(0,2)
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells(rows.count,1).End(xlup))
End with

set rng1 = rng.find(rngYr.Value)
if not rng1 is nothing then
sAddr = rng1.Addr
do
if rng1.offset(0,2).Value = rngDay.Value
rng1.offset(0,3).Resize(12,5).copy _
Destination:=rngDay.offset(0,1)
exit do
end if
set rng1 = rng.findnext(rng1)
loop while rng1.address < sAddr
End if
Next

Code is untested and may contain typos

--
Regards,
Tom Ogilvy


"KiriumF1" wrote in message
...
hi i have data in this form
Year Month Day of year, n DataA DataB DataC DataD DataE
97 Oct 291 111 222 333 444 555
666 777 888 999 000
97 Dec 350 123 345 456 234 456
321 232 543 432 246
654 332 569 432 234

Actually each year and month data has 12 rows each ( the example above has
2
rows and 3 rows as an example)

I would like to sort this data into another spreadsheet which already has
the data fields

Year Month Day of Year,n DataA DataB DataC DataD
96 Oct 298
... ... ...
... ... ...

How do i write a VBA program to search for the data in the first
spreadsheet
based on the year and day_of_year, n , and copy and paste the
corresponding
data to the second spreadsheet accordingly?

If i am unclear, basically, if the first field in the second spreadsheet
was
96 and 298, the program will search the first spreadsheet based on 96 and
298, copy the corresponding data from 12 rows of the data fields
DataA,DataB,
DataC, etc and paste this data onto the second spreadsheet at the rows
with
96 and 298, and so on.

Sorry if it's confusing! Thanks if someone can help me on this!

How to sort the data?
 

count your if's and end if's. sometime in a do loop or for
next loop, if you leave out a end if, you will get the
message that the do loop or for next loop is bad.

-----Original Message-----
Hi thanks for your Help! But there's a slight problem, i
couldn't run it as
there's an error, "Compile Error: Next without For"

I checked and dun think there's a problem, cos you used
For Each and it
ended with Next. Could you advise me on this? Thanks!

"Tom Ogilvy" wrote:

Sheet to be copied to is Sheet2, sheet with data is
Sheet1.


Dim rngYr as Range, rngDay as Range
Dim rng as Range, rngB as Range, rng1 as Range
Dim sAddr as String
With Worksheets("Sheet2")
set rngB = .range(.Cells(1,1),.Cells
(rows.count,1).End(xlup))
End With
for each rngYr in rngB
if not isempty(rngYr) then
set rngDay = rngYr.offset(0,2)
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1),.Cells
(rows.count,1).End(xlup))
End with

set rng1 = rng.find(rngYr.Value)
if not rng1 is nothing then
sAddr = rng1.Addr
do
if rng1.offset(0,2).Value = rngDay.Value
rng1.offset(0,3).Resize(12,5).copy _
Destination:=rngDay.offset(0,1)
exit do
end if
set rng1 = rng.findnext(rng1)
loop while rng1.address < sAddr
End if
Next

Code is untested and may contain typos

--
Regards,
Tom Ogilvy


"KiriumF1" wrote
in message
news:34030C6C-5507-40CE-86EF-
...
hi i have data in this form
Year Month Day of year, n DataA DataB DataC DataD
DataE
97 Oct 291 111 222 333 444
555
666 777 888 999
000
97 Dec 350 123 345 456 234
456
321 232 543 432
246
654 332 569 432
234

Actually each year and month data has 12 rows each (
the example above has
2
rows and 3 rows as an example)

I would like to sort this data into another
spreadsheet which already has
the data fields

Year Month Day of Year,n DataA DataB DataC DataD
96 Oct 298
... ... ...
... ... ...

How do i write a VBA program to search for the data
in the first
spreadsheet
based on the year and day_of_year, n , and copy and
paste the
corresponding
data to the second spreadsheet accordingly?

If i am unclear, basically, if the first field in the
second spreadsheet
was
96 and 298, the program will search the first
spreadsheet based on 96 and
298, copy the corresponding data from 12 rows of the
data fields
DataA,DataB,
DataC, etc and paste this data onto the second
spreadsheet at the rows
with
96 and 298, and so on.

Sorry if it's confusing! Thanks if someone can help
me on this!






.