Need help completing project, sorting issue.

Mezmer

New Member
Joined
Dec 27, 2004
Messages
41
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? :pray:

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. :oops: ANy help is appreciated. Is there someplace I could even buy the macro, VBA or whatever I need to finnish this?

Thanks again.

M
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

jdc

Active Member
Joined
Nov 23, 2004
Messages
263
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

New Member
Joined
Dec 27, 2004
Messages
41
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. :oops:

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

Active Member
Joined
Nov 23, 2004
Messages
263
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

New Member
Joined
Dec 27, 2004
Messages
41

ADVERTISEMENT

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

Active Member
Joined
Nov 23, 2004
Messages
263
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

New Member
Joined
Dec 27, 2004
Messages
41

ADVERTISEMENT

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

Board Regular
Joined
Dec 21, 2004
Messages
76
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

Active Member
Joined
Nov 23, 2004
Messages
263
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
 

Forum statistics

Threads
1,148,220
Messages
5,745,456
Members
423,952
Latest member
EduardoM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top