Macro to insert comment in a fixed size w/ shortcut

Is 2.5 the width or the height?

Please Login or Register  to view this content.

Untested

content deleted by poster

I would look at how the information gets to comment. I would probably be easier to manipulate the data as the comments are being created. Or possibly even before that. After the information is placed in a comment its going to be more difficult to create custom formatting like you have above.

Perhaps they could be copied and pasted into cells in a column. Then you could have a macro that reorganizes the information and creates comments at the same time?

Can you provide a sample workbook showing the following:

1. What addresses copied from amazon and pasted into a cell looks like
2. What column the addresses will be
3. Where you want the comments input
4. An example of how each same address should be modified

When the address are pasted from amazon they are pasted into different cells?

Hi, elgato,

what about some more samples to determine if the length of each set of data is identical - and a very good reason for using DE instead of D, BRD, GER. How many countries will you cover, and do you have more abbreviations like DE, SWZ or IT? Up to now Split could be used to obtain the results but thatīs depending on the number of cells filled or empty cells. And I guess the second Berlin stands for Bundesland?

Ciao,
Holger

Hi, elagto74,

there is no consistency that I can make up for any code to work. FOr the german as well as the fernch address the street name and house number are in the second row. For german housen number is at the end, for french at the beginning. ZIP is behind the twon name for Italy and GB.

So this would lead me to state to scan through the area, get the country name and have functions for each individual country in order to deliver the correct correlation between what has been delieverd from the system and what you want from it for the other software needed.

A sample to determine what Function/Sub to call may look like

Please Login or Register  to view this content.

Youīll find the information in the Immediate Window / Direktfenster stating start line for a range, the Function/Sub to call and the number of cells the address covers. Please mind that the blank in between the words is either Chr(32) or CHR(160):

Please Login or Register  to view this content.

Ciao,
Holger

Hi, elgato74,

it will take some time for me to check and maybe adapt the codes and calling them from the draft I have in mind, it might even take time until tomorrow morning. Iīll keep you informed about what I have come up with.

Ciao,
Holger

Hi, elgato74,

it would be easier to start on working on the data thatīs inserted to transform that into a common format in order to just have one routine but the import you have right now s.... - you should get somebody to get that straight in order to avoid to have to write a routine for each and every country later on.

All the code goes into one module as the variables only have a scope for the module they are dimmed in, due to the individual codes for the countires the code is quite a bit longer as I owuld expect it to be:

Please Login or Register  to view this content.

Code should do fine with the samples delievered but Iīm afraid no program will be able to detect if the First Name is listed before or after the Surname without a huge list to compare to and decide from there if the lower or upper bound of the array should be taken for the First Name. Code for GB needs to be adjusted as I took Brynna to be the town name. But I think you should get an idea on how to work with this. Again: Iīd recommend to sort of streamline the data import and just have one macro to do the work.

Concerning transfering this information into a table: to me youīre overcomplicating this part as the data could be written into the table directly from the import without transferring it into a comment and reading out that comment. Maybe I donīt understand the need for this extra step as I canīt figure out any reason for it without knowing why this step might be necessary.

Ciao,
Holger

Hi, Felix,

I had the workbook as a sample and I canīt get any run time error to appear (especially not a 1004).

I changed the main code a bit:

Please Login or Register  to view this content.

Code should get started on Sheets("Muster") by pressing the button.

Ciao,
Holger

Hi, Felix,

depending on the number of different settings you could create variations of the individual/local macros created and call them on a one to one base - these would need to be slightly adjusted but the basic way to handle them is laid down in the samples.

Another idea might be to create a UserForm which may show the data split up and allow you to put them in the sequence you want. Still there would be problems regarding multiple entries which need to be put together, and Iīm not so sure that it really might be a good idea - you could use ComboBoxes for the ordernumber and List- or ComboBoxes for the entries. I donīt know how good your VBA skills are but instead of doing it all manually and each month I would at least go the first way and have a growing number of macros for the indivual countries. And I would comment the codes in order to make it easier to understand which ideas made me take that particula route to coding and for which fields in which order the code was planned.

Ciao,
Holger