+ Reply to Thread
Page 2 of 2 FirstFirst 1 2
Results 16 to 26 of 26

String to repeat macro on each row with content

  1. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,744

    Re: String to repeat macro on each row with content

    You've now got three columns with Suppliers Wine 1, ...2 and ..3 can there be more?
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  2. #17
    Registered User
    Join Date
    01-29-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: String to repeat macro on each row with content

    Dear Keebellah,

    My original process has been quite long. I would import the wine order list into an Excel sheet. Put in the Sub Summary(), Sub Order() and Sub Rename() macros into a new module via Visual Basic from Notepad. I would then have to open Macros and put the shortcut for each macro via the Options i.e. Ctrl+o would run the Sub Order() etc.

    Then I could go to the wine order worksheet and run the Sub Summary() macro - to put the date into cell A1 and "SUMMARY SHEET" into cell B2. Then I would run the Sub Rename() to rename that main worksheet so that it could be identified later by Excel.

    As I have been told there could be anything up to 90 columns of orders for each customer on this sheet, Sub Order() consisted of copying the whole row for one client from the SUMMARY SHEET into row 3 of the inserted worksheet.

    Then on that new worksheet I copied columns F3 through to DZ3 (all the wine orders for that client) and then special Transpose pasted onto F4 to F104 (which should actually have been row F94 coming to think of it). I then deleted F3 to DZ3 and Shift cells up.

    I could then click on F3 select the whole column with Ctrl+shift+down and do the Text to Columns with "|" as the Other delimiter and put the Destination as $F$3.

    I then put all the headings onto Row 2 with the Customer Full Name in cell B2 and resized the columns then went back to the next row to be copied in the SUMMARY SHEET.

    When all the client orders were put into new worksheets, I would then run the Sub Rename() macro and the new worksheets would then be renamed after the client name in cell B2 of each worksheet.

    Phew!

  3. #18
    Valued Forum Contributor
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    733

    Re: String to repeat macro on each row with content

    Ideally, you would attach the "original" text file before converting to excel.
    Of course, the data must be crafted so that it is not true, it's about the layout of the data itself in a text file.

  4. #19
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,744

    Re: String to repeat macro on each row with content

    Do you have a sample text file of the order, let's say the 'original' file that you start with?

  5. #20
    Registered User
    Join Date
    01-29-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: String to repeat macro on each row with content

    The example is the "Wine List to put into order" on the first page of this thread - it usually comes through as a .csv file

  6. #21
    Valued Forum Contributor
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    733

    Re: String to repeat macro on each row with content

    It's already a converted form of file, a converted csv file, not a raw one.
    This is not about the original file, but about file corresponding to the truth, with the changed data.

  7. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,744

    Re: String to repeat macro on each row with content

    Okay, use a converted csv to xlsx file
    The actual processing file is zipped because xlam files are not allowed

    I have attached a file named Wine Order processing.zip which contains a file named Wine Order processing.xlam notice the xlam extension, it works like an Add-In but you do not need to specifically install it. Just place it in any folder and double click to open it.
    You won't see any different when its opened but it is loaded into memory I changed the macro shortcut key to Ctll+w because Ctrl+o is normally use to open a file
    There are two modules in the file, one named OrderProcessingModule and one named XIFileBrowseAndSelectmod this second one contains some general purpose UDFs for the extra functionality

    When you press Ctrl+w the first question will be to select where you want the target file(s) to be saved, can be any file location, this will be saved and used as default and you will be prompted if you want to use this location pressing No will bring you to the option to select another location.

    The next step is to point out where the wine order file is located

    Then it's opened an processed. The result is attached here too, name for the resulting file is Wine-20180516-2348.xlsx the time stamp is yyyymmdd-hhmm year month day hour minute
    I have attached the wine order file you posted in the first place

    Give it a run and I'm curious to hear your findings.
    Attached Files Attached Files

  8. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,744

    Re: String to repeat macro on each row with content

    Good morning, don't know if you've had the chance to look at it.
    I modified the xlam and added two ribbon buttons so you don't have to always use the macro shortcut.

    The macro shortcut will still work but I find buttons easier to use and now you also have the option to only unload the Wine order module from memory
    Just a question: have you considered creating a file for each customer? eg. Wine-20180517-0933-John Doe.xlsx and Wine-20180517-0932-Jane Smith.xlsx ?
    It's a minor modification in the code, you could even add the option create one file per order or create individual files per order.

    VBA can be fun
    Attached Images Attached Images
    Attached Files Attached Files

  9. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,744

    Re: String to repeat macro on each row with content

    Last one, promise
    Added a user form when you use the ribbon button
    The zip file contains the xlam module, the wine order file and the three outputs, one is all order i separate worksheets and the other is the result when selecting a separate file per customer
    Hope I haven't scared you off too much.
    This is what I do for a living as well (helps with the brain gymnastics )
    Enjoy ans just holler if you have questions.

    Hasta luego.
    Attached Images Attached Images
    Attached Files Attached Files

  10. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Excel 2010 2016
    Posts
    2,744

    Re: String to repeat macro on each row with content

    The manual
    One item is slightly different than the last version I uploaded, it's cosmetic

  11. #26
    Registered User
    Join Date
    01-29-2013
    Location
    Bristol, England
    MS-Off Ver
    Excel 365
    Posts
    29

    Re: String to repeat macro on each row with content

    Dear Keebellah,

    WOW! I cannot thank you enough! That is truly amazing - so beautifully put together and far more than I thought possible.
    I am thrilled. Thank you soo much.

    A very grateful and in awe vba newbie,

    Angelique

+ Reply to Thread
Page 2 of 2 FirstFirst 1 2

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1