Need help completing project, sorting issue.

[Mezmer]

Hi,

I am using Excel 2002.

I work for a small shipping company. We have 6 boats. These boats send in reports on postions, with time and date. These positions are logged to a file.

I import this file into a spreadsheet, the format is a bit messed up but i have it cleaned. I need the order reveresed so I just sort it, no problem there. I have the import set to auto update.

What I need to do from that point is sort the data by most recent position update per boat, and send that update to that boats worksheet.

So it would go along the lines of. Boat A, Boat B, Boat C, Boat D , Boat E and Boat F as worksheets, using the actual boat names. Each one with it'se most recent position update displayed in the first row.

I've attempted to record macros to sort the data then paste it, but the macros seem to overlap and the data gets posted out of place (Boat A to Boat B.. etc.) Sort seems to get a little messed up at times as well. I've been using a page change event to trigger this.

Is there a simple way I can sort this information and paste it to the individual sheeets (automaticlly) avoiding this overlap I have? ray:

Once the filtering, sorting is done I compare the most recent update to the current time now(), and based on the IF= I display if the postion is late or on time. That much works fine.

This is really driving me nuts. ANy help is appreciated. Is there someplace I could even buy the macro, VBA or whatever I need to finnish this?

Thanks again.

M

 

[jdc]

Hi,

Let me see if I have this correct,

1 You import the report into one workbook
2 You separate each boat's times and positions etc to separate worksheets
3 Each worksheet is then sorted on time with the top row being the latest
4 You then send Boat A's latest position back to Boat A, Boat B to Boat B etc

Not being thick but if they sent you their position in the first place won't they know where they were when they last posted their position, wouldn't it be better for them to know the other 5 boats positions.

Regards

John

 

[Mezmer]

Yessir, they sure ought to know where they themselves are, God I hope they do... .

Sorry, I think I was a wee unclear. The project has a workbook assigned to each boat, Boat A, Boat B , Boat C... I want the latest updates to go to those worksheets on the spreadsheet I have. The boats have no access to it.

The idea is to get the information from all boats, check to see if they have missed a positon time (from a set interval checked aginst present time ie. now()). It's for me to know if they have missed, without having to manually check through the data.

My issue now is more of a macro timing issue. Say the project has 7 sheets, the front page and the 6 vessels. When I run the macros from the front sheet, they step all over each other. The first one sorts the data as it comes in from the report, then a macro will change the cell format so the time is useable in a formula, next a copy and paste. Instead they keep ending on the wrong worksheet... giving errors. When I run the macros individually, they work fine.

I'm messing up the order, or timing of the macros. Is there a way to be sure each one completes its task before the next begins?

I'm just using along the lines of the below.

Private Sub Worksheet_Change(ByVal Target As Range)
Call Sortmac
Call Timemac
Call Boatfilter
End Sub


Thanks again,

M

 

[jdc]

Hi M

More idea than code, how about adding a timing break between each macro which would be termed as a procedure in this code

This example runs my_Procedure 15 seconds from now.

Application.OnTime Now + TimeValue("00:00:15"), "my_Procedure"

So hopefully each one would finish before then next one starts.

Sort of a 15 second pause before the next part of the code runs.

Regards
John

 

[Mezmer]

Thanks JDC!! Sure works to slow things down!!

Here is my new problem though... I filter the data using autofilter. Say I auto filter the list and filter for SSMinnow. The most recent SSminnow is on the frst line below my autofilter, so I copy and paste this onto the SSminnow worksheet.

Now, this is the issue. I create a macro that does exactly that using the record feature. The problem is when the data page is updated, the macro no longer works. It pulls another boat from the list and paste it.

The problem, from lookingt the macro, is that it records copyting say A2, which is where the most recent SSminnow entry is at the time When an update comes along there may be another boat in the A2 cell, and it goes ahead and copies the A2 and the other boat.. instead of the most recent SSminnow.

this is the macro

Range("A5").Select
Sheets("Sorted list").Select
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:="SSminnow"
Range("A3:E3").Select
Selection.Copy
Sheets("SSMinnow").Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Sorted list").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Sheets("Status").Select
Range("A5").Select
End Sub

Any idea how I can fix this?? Thanks again!!

M

 

[jdc]

Hi

I would forget the autofilter in this macro as it is volatile and I can't think of a way of picking up the activecell, so use this code instead

Sub Find_SSMinnow()
Dim R As Range
Sheets("SSMinnow").Select
Range("A5").Select
Sheets("Sorted list").Select
Range("A1").Select
'Selection.AutoFilter
'Selection.AutoFilter Field:=5, Criteria1:="SSminnow"
Set R = Cells.Find(What:="SSMinnow", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=True)
Range(R, R.Offset(0, -4)).Select 'this having found the first cell, as it is sorted selects the cells on that row, (0) and (4) to the left of it.
Selection.Copy
Sheets("SSMinnow").Select
Range("A2").Select
ActiveSheet.Paste
Range("A2").Select
Sheets("Sorted list").Select
Range("A1").Select
Application.CutCopyMode = False
Sheets("Status").Select
Range("A5").Select
End Sub

Then do the same for the different boats, and in one new macro just use the line

Call Find_SSMinnow

to run each macro with the 15 second pause between

HTH

John

 

[Mezmer]

My Good Lord JDC!!! Thank you so much!!! This took quite a bit of error out of the system!!

Only issue I now have left, is this.. and it's an annoyance more then anything. When I import the date from a text file (, seperated file) the format comes over a bit of a mess. I can not determine date and time. The cells are there with the date and time in them but I can not just go in and change them by formatting cells. I use text to colums, which does work but for the date I have to split the date into 3 values and then recombine them in order to format the date. 1/2/2005 becomes a cell with 1 a cell with 2 and a cell with 2005 in them... then I recombine them and get the orginal date.

It's a niuisnace really, not really a wall in my way but is there a simple fix for this sort of thing??

Once again, my profound gratitude!!

M

 

[RobShaw]

with regard to the date formatting issue, when you import from the csv file, is the date coming in as text?

i.e. "1/2/2005"
I had a very similar problem, and formatting the column to date format didn't pick up the info. part of the issue was the date was in the American format and I needed the UK format, but if you go into format cells and select custom telling it exactly the layout of the date, somehow that allowed excel to 'understand' the cells and allowed me to change the formating round after, it might work for your sheet.

 

[jdc]

Hi Mezmer

You have a at least a couple of ways to combine the cells to form a date

1) with the day in A1, month in B1, year in C1 use this
=DATE(A1,B1,C1) and format it to DATE in your preferred style

2) use CONCATENATE =CONCATENATE(A1,"/",B1,"/",C1) again you may have to format it for DATE.

Then just hide the columns with the partial date info in.

Just to be sure the date is set up properly I convert month from mm to mmm, that way it can't be misread.

Pleased to have been of help

John