Cell drag to the last blank cell with Macro for AMVER Programme

[TomAlex]

Hi
My name is Tom Alex. I work as a navigation officer onboard a Merchant Ship that mostly carries Oil and Chemicals. First I will give a short explanation about AMVER from Wikipedia.
AMVER, or Automated Mutual-Assistance Vessel Rescue is a worldwide voluntary reporting system sponsored by the United States Coast Guard. It is a computer-based global ship-reporting system used worldwide by search and rescue authorities to arrange for assistance to persons in distress at sea. With AMVER, rescue coordinators can identify participating ships in the area of distress and divert the best-suited ship or ships to respond. Over 2,800 lives have been saved by AMVER participating ships since 2000.

How AMVER works is that navigation officers like me send daily reports and sail plan so that AMVER can identify nearest ships. While it should have been automated and technology exists for the same, Unfortunately it has not yet been implemented. This requires us to manually send a message in format that is both time consuming and error prone. This leads to many navigators simply ignoring to send AMVER or sending with insufficient information.

I set out to make this task easy by creating an excel programme for the same. I did not have any formal training in excel and hence it took almost an year to get this done. Even now, its far from perfect. However it works and it makes life easier and saves life too( I hope).

I have made a youtube video that explains how this programme work. Since it looks like I am not allowed to post the link here, I am sharing the video ID - QYcc0Kq3gSk.

What I am trying is to automate process between 1:28 and 1:45. Basically to have it complete with a single button click.
I am also attaching the form that I have made. I know it looks messy and cluttered but this is best I can come up with

I am also very open to any ideas that can make this better. But do consider my knowledge level in excel .

I am sure this is a long post but I feel it may not be complete without giving some background into what I am trying to accomplish.

Many thanks in advance for your help

PS - I have posted this same query on another website

[zzzeddy]

Hi Tom

What I am trying is to automate process between 1:28 and 1:45

..I am having difficulty navigating to your link, and don't understand what process you are asking about.
But I want to help.

zeddy

[zzzeddy]

Hi Tom

I watched your video a few times (couldn't hear any sound though).

I think what you are asking for regarding "cell drag to last blank cell" is a way to automatically copy the contents of cell [K9] down to the last inserted-row for your Way points on the [Sailing Plan] sheet.

This is a simple update to your routine assigned to the button [PRESS SECOND] on your [Sailing Plan] sheet.
Essentially, each time your loop inserts a row (at row 10), you just need to add an extra line to copy the cell from [K9] to the newly-inserted [K10] i.e. use..

Rows(10).Insert
[K9].Copy [K10]

..that should do it.

zeddy

[zzzeddy]

Hi Tom

If you have an questions, please ask.
Do you want to automate some other steps?

zeddy

[TomAlex]

Hello zzzeddy

Many thanks for your response. The video does not have any sound.

I am not sure you have quite understood my query. I am sure I have failed in actually conveying it to you.

Number of rows being inserted into Sailing Plan Sheet is dynamic. Meaning it is based on number of entries in WP Sheet. This number is coming to A1 Cell in Sailing Plan Sheet. Also it is not merely copying from previous cell. Its copying data based on a condition from sheet AMVER Position Records.

Anyway many thanks for your assistance. Much appreciated.

[Artik]

Try it:

Please Login or Register  to view this content.

I would like to remind you that the VBA project is password protected which prevents many people from helping you. Either publish the workbook without security or share the password.

Artik

[zzzeddy]

Hi Tom

Thanks for clarifying the sound issue.
OK, now I understand your issue.

Here's another way to do the job for your macro assigned to your [PRESS SECOND] button..

Please Login or Register  to view this content.

Note: you could combine your Step1 and Step 2 into a single Step.
..or you could just put a formula in cell [A1] =COUNT('WP Entry'!C:C)-2
..that way you don't need a Step 1

Artik has given a good solution, but maybe mine is simpler for you to follow.

zeddy

[TomAlex]

Dear Artik

Many thanks for your Macro. It works like a charm. Much appreciated.

PS - Thanks for the heads-up. You are right. VBA project was password protected and I forgot to remove the lock. Although you have provided me with a solution that works, for the sake of good order, I am uploading an unlocked form.

Once more many thanks

[TomAlex]

Dear zzzeddy

Many thanks for your response. I have tried both yours and Artik Method and I can confirm both works. But like you said, I can easily understand how code works in your case. Its simple and its added advantage that you have put comments to explain how it works. That is really appreciated. I have combined both into one step by putting the code in A1.

Once more many thanks

[zzzeddy]

Hi Tom

Thanks for the unlocked sample file.

I have added a routine to clear the Position Rows that were added by the code in post#7

This is effectively an 'undo' button.
There are other ways of doing this, but you should learn a new trick here.

zeddy

[zzzeddy]

Hi Tom

In your video, you copied the block on the external ROUTE SHEET from cell range [B8:G21] and then pasted this as values to the [WP Entry] sheet, at cell [C6]

So I presume the intention (in the video example) is to use Way Point 3 on the ROUTE SHEET as your new start point i.e. the COSP 000 start point, i.e. Way Point 000 for the [WP Entry] sheet.

In the video, you then copied the corresponding speed-range, starting from Way Point 3, [K8:K21] from the ROUTE SHEET to cell [o7] on the [WP Entry] sheet i.e. offset by 1 row down from the Way Point 000 in row 6??? You then deleted the 'extra' speed cell at the bottom of the posted speed list in column [O] on the [WP Entry] sheet. Was this intentional???

In any case, we could probably automate this to simplify the process. We could import the csv route data as a temporary new sheet (e.g. as [JRC ECDIS] ), and then just 'ask' the User what Way Point number they want to use as the new Start Point. For the video example, it would be the equivalent of 'I want to start from WPT No 3 on the ROUTE SHEET', and then the data for the [WP Entry] sheet would be done automatically (i.e. without requiring the User to highlight-copy-and-paste and then highlight-copy-and-paste again).

If you would like to see this done, it would be helpful to provide a sample route csv file here (to save me creating one).

zeddy

[TomAlex]

Hello

Many thanks. I shall upload the csv as sample files. Thanks for your assistance.

[zzzeddy]

Hi Tom

I have attached a sample v3 file for you to have a look at.

This version assumes you have copied this v3 file, and also your demo csv Route files from post#1 (included here), into a test folder of your choice.

On the [Welcome] sheet I have added a new button [Select Route CSV file..]
Clicking this button will display a file-dialog to allow the User to select a route csv file (by default, it will show the csv files located in the same folder as the Excel AMVER demo file).

The chosen route file is then imported automatically to a new sheet, [ECDIS Route]. The User can look at this to check it's OK.

On the [WP Entry] sheet the User can enter the WPT No in cell [T3] to indicate the Way Point number to use for the COSP 000 Start Point.
Or the User can click the spinner button in cell [U3] to choose the starting WPT No for the WP Entry.
The Latitude/Longitude and Leg Speed data values are then automatically imported from the [ECDIS Route] sheet to the [WP Entry] sheet.
Please test this out.

In my attached version, I added some flags on your [Welcome] sheet.
If you click a Flag, you will see some translated instructions.

I haven't done full translations for ALL the sheet tabnames, buttons etc etc etc but this could easily be done.

I just thought it might be useful to encourage Users to use your Excel Program!

Of course, you could define whatever Flags etc you wanted. I just did a basic demo for you.
In anticipation of changing sheet tabnames according to language selected via the Flags, I have referred to the sheet tabnames in the vba code via their sheet-codename-property. I can explain this to you in more detail. Next time.

zeddy

[zzzeddy]

Hi Tom

In the attached v4 file..
On the [Welcome] sheet, clicking a flag will now change the tabsheet names and button-text to the corresponding Language.
Check the button [Select Route csv File] after clicking a Flag.

If you right-click on the button and look in the Excel formula-bar, you will see that the text for the button-shape is linked to a cell on the sheet named [Translate].

This method has been used to assign the appropriate text to other 'buttons', for example...
[Click to copy the report to notepad]
..if you right-click this shape, look in the formula-bar etc etc.

More Flags could be added of course. I just picked some for demo purposes.

The sheet [Translate] acts as the source for these translations. I haven't verified whether the translations are accurate, you would have to get someone who speaks that language etc etc. I just used google. You could easily update the [Translate] sheet as appropriate.

This [Translate] sheet could be hidden.

I haven't done ALL the translations, it was just to show you what could be done in principle.

In the version 4 attached file, I tidied up the vba code a little bit.
I like printed vba documatation, so when I come back much later on I can see what happened when and who did it. So I started you off with what I like to use, just a suggestion. Also, I like giving vba modules names to make it easier for finding stuff. You just use the Properties box to create a name for a vba module. I also renamed the codenames for the worksheets, and you change these by using the Name property (in the vba window, if you can't see the Properties window, press [F4] or use the top-panel-menu: View>Properties Window)

Hope you find this useful. I'm sure there is more that could be done.

zeddy