Open Forum

Expand all | Collapse all

Macro to update MSRP

  • 1.  Macro to update MSRP

    TOP CONTRIBUTOR
    Posted 23 days ago
      |   view attached
    Good morning,
    I am creating a macro to update all of our items MSRP.  I have a normal excel file with the item number and retail price but when I start to create the macro it wants to change some of the $ amounts to over 10 decimal places.  I have attached a screen shot.  I have done this in the past and never had a problem.  I have tried to format the cells to text, currency, custom, etc but it still does this.  What am I missing?  I am sure I had this problem last year but I cant remember what I did to fix it.
    Thanks,

    ------------------------------
    Katie Pieczynski
    Accounts Receivable Manager
    DANSR
    Champaign IL
    ------------------------------


  • 2.  RE: Macro to update MSRP

    GPUG ALL STAR
    Posted 23 days ago
    @Katie Pieczynski,

    I don't see in the screen shot where in the GP macro the values are typed. Actually, I'm not sure what I'm seeing. You said that you have the prices in an Excel workbook, but you're creating a macro in GP. How are you jumping back and forth?

    Regards,​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List
    ------------------------------



  • 3.  RE: Macro to update MSRP

    TOP CONTRIBUTOR
    Posted 23 days ago
    I am using mail merge to create the macro.  So I have an excel file with item number and dollar amount and they are all hard coded.  When I used mail merge and select my list it it pulls in this showing me what my list will be.  During this time is when it is bringing out all the decimal places.

    ------------------------------
    Katie Pieczynski
    Accounts Receivable Manager
    DANSR
    Champaign IL
    ------------------------------



  • 4.  RE: Macro to update MSRP

    GPUG ALL STAR
    Posted 23 days ago
    @Katie Pieczynski,

    Ah! Thank you for the extra detail. I have never used that capability. That explains why your screen shot had all of those red Word squiggles for misspelled words!

    ​Personally, I would use brute force. Once the macro file is created in Word, use search and replace to rid yourself of those annoying extra decimal places. Then save the revised file and run it in GP.

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List
    ------------------------------



  • 5.  RE: Macro to update MSRP

    TOP CONTRIBUTOR
    Posted 23 days ago
    Thank you I will look into that.

    ------------------------------
    Katie Pieczynski
    Accounts Receivable Manager
    DANSR
    Champaign IL
    ------------------------------



  • 6.  RE: Macro to update MSRP

    Posted 23 days ago

    Hi Katie,

     

    I have had a similar issue.  I fixed it by using this link:

     

    https://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-i-maintain-excel-formatting-ie-2-decimal/adb01b48-2e43-4758-a8b4-548fd2826006?db=5

     

    I hope this helps.

     

    George

     






  • 7.  RE: Macro to update MSRP

    GPUG ALL STAR
    Posted 22 days ago
    @George Kuntz, MSc,

    Very interesting! That is definitely out there! #BizarreWordFunctions!​​

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List
    ------------------------------



  • 8.  RE: Macro to update MSRP

    Posted 16 days ago

    @Steve Erbach  glad I can help out. ��

     






  • 9.  RE: Macro to update MSRP

    SILVER CONTRIBUTOR
    Posted 22 days ago
    I've had trouble with formatting in the past, I've found that if you make the change to excel, close excel and do not open it again, it holds it. If you need to reopen the file, you need to change the format again. I'm not sure if it'll correct your issue, but it's worth a try.

    Good luck.

    ------------------------------
    Tamara DeStefano
    Manager of Financial Operations
    Phigenics, LLC
    ------------------------------



  • 10.  RE: Macro to update MSRP

    TOP CONTRIBUTOR
    Posted 22 days ago
    Hi Katie,

    Word's Mail merge makes me crazy!  That's why I ended up creating a solution in Excel (Replicator.xlsm) that does the same stuff (plus a whole lot more).  Everyone that has used it has commented on how easy it is compared to Word's mail merge.  You can download the excel file and my Summit 2018 PowerPoint presentation at my website www.Rnoldz.com/replicator  Replicator - A new way to create GP Macros

    You will have total control over the formatting!

    If you have any problems or questions, just give me a yell!

    Good luck & have fun!


    ------------------------------
    John Arnold
    Senior Software Engineer
    US Digital
    Vancouver WA
    ------------------------------



  • 11.  RE: Macro to update MSRP

    GPUG ALL STAR
    Posted 22 days ago
    @Katie Pieczynski and @John Arnold,

    Excellent work, John! I resist working in Word as much as I can. Your Replicator.xlsm makes that easier!

    Regards,​​

    ------------------------------
    "Sparkly" Steve Erbach - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach

    ───────────────
    Excel Webinar List
    ------------------------------



  • 12.  RE: Macro to update MSRP

    TOP CONTRIBUTOR
    Posted 21 days ago
    Thanks John,
    I was able to get it to work but I will definitely take a look at your file and PP.  I hate working with word!

    ------------------------------
    Katie Pieczynski
    Accounts Receivable Manager
    DANSR
    Champaign IL
    ------------------------------