+ Reply to Thread
Results 1 to 27 of 27

Updating a code to include the data above rows inserted

  1. #1
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Updating a code to include the data above rows inserted

    Hi All,

    I need a little help...I found the macro to insert the rows, but I was hoping someone could help me fix it so I can also copy the data above the inserted rows. Here is the macro I have to insert the rows:

    Please Login or Register  to view this content.
    I've attached a document that has sample data. There are two tabs; one with Raw Data and the other with the way I want it to look.

    Any help is greatly appreciated.

    Thank you,
    Genie
    Attached Files Attached Files
    Last edited by GAccounting; 09-13-2010 at 09:56 AM. Reason: Solved

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    Where did the values in columns G and I come from on the results sheet?
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    I manually filled it in, it will be the same repeating data for the entire form in this instance. In the other lists I will put a formula in to return the value and again I will fill it in for the sheet and I can add the columns after the macro is run. The actual file I am working with has well over 3k parts on it and I need to have 4 different price list files created from the same data.

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    I can't say I understand the logic of your examples.

    The sample data is too big, the "results" need to be a complete results sheet from the sample data.

    Try explaining what is going on step by step without referring to the original macro. Just walk us through it visually with the sample data to the results. There should be no "manual" part of this, if there are values to fill in and columns to add and such, explain all of that and provide the resource information for where those decisions are being made, a macro should be able to handle it all if the data is available.

  5. #5
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    Let me start from the beginning.

    I am creating different price lists that will all start with the same raw data (rows F and G). Each part will have 3 different types of pricing depending on the amount of items being purchased. There are two different situations, one will have the price breaks at 0, 11 and 24 with a constant value of 0 at 0, 5 at 11 and 10 at 24. This price sheet will need to be its own data.

    The other will be set at 1, 11 and 24 with different values being returned depending on the letter at the end of the part number (row G). For example if an item ends in R and the price break is at 1 then the value returned is 25, at 11 it is 30 and at 24 it is 35. If the item ends in X and the price break is at 1 then the value returned is 10, at 11 it is 15 and 24 it is 20. If the item ends in xx and the price break is at 1 then the value returned is 15, at 11 it is 20 and at 24 it is 25. For an item ending in C and the price break is at 1 then the value returned is 30, at 11 it is 35 and at 24 it is 40. If the item ends in B and the price break is 1 then the value returned is 35, at 11 it is 40 and at 24 it is 45. Those are all the letters that would be represented. This is why I was going to add the formula after...it's convoluted.

    I hope this helps and please let me know if additional information is needed.

    Thanks,
    Genie

  6. #6
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    It's not actually convoluted at all when you put it in a standard lookup table.
    Please Login or Register  to view this content.
    Is that clear and readable?

  7. #7
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    That is much easier to read then the way I stated it. Thanks for clearing that part up. Now, how do I get it to return the values into the spreadsheet? There is one other instance and that is that if there isn't a letter than then the value would be 0, 5 and 10.

    Please Login or Register  to view this content.
    Thanks again!

  8. #8
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    What about all the others that DO end in letters, just not the ones you have listed?

    Please Login or Register  to view this content.

  9. #9
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    For now, let's just presume all the ones that don't fit into the codes you've provided get the default 0,5,10 listing. Try this on a copy of your data with the exact same starting layout as your sample provided.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    I will give it a try tomorrow morning and get back to you. In the interim...Thank you! Also, your presumption was correct about the other items that did not end in the letters provided.

  11. #11
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    Ok...I tried it this morning and got an error: Compile Error/Named Argument Not found. I highlighted the portion of the code:

    Please Login or Register  to view this content.
    Awaiting your feedback,
    Genie

  12. #12
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    Did you try taking that parameter out? I didn't notice you were using Excel 97, that's pretty far back.

  13. #13
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    Do I have to take the entire section out? I work for a company that is pretty set in their ways and they don't want to upgrade

  14. #14
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    No, taking out the entire SORT command would be bad. Just try removing the parameters that Excel is complaining about.

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    Thank you...sorry for being a pain, but I have never worked with VBA and I have really limited knowledge of Macros (I thought I knew more, but have been come to realize that I don't) and I'm not really sure what to look for. I got a run time error 1004 on this section of code:

    Please Login or Register  to view this content.
    Any suggestions?

    Thanks again for all your assistance!

  16. #16
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    Turn on the macro recorder (Tools > Macros > Record a new macro) and record yourself highlighting column F and doing a REPLACE (CTRL-H) on "XX" replacing it with anything else, doesn't matter.

    Stop the macro recorder and look at the code, maybe the parameter differences will be obvious. If not, post that recorded code so I can look at it.

  17. #17
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    Ok...I got that one fixed and another one that came up, but now I'm stuck at this one:

    Please Login or Register  to view this content.
    The error was AutoFill method of Range class failed.

    Thanks again!

  18. #18
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    Ugh...you're killing me with these '97 limitations. Use this in place of that:

    Please Login or Register  to view this content.

  19. #19
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    I know, I know! I'm soooo sorry about that, but I know where you're coming from...I have to work with it everyday! The good news is that it ran through completely this time...the bad news is that it didn't duplicate the lines. I'm attaching the document so you can see it.
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    So, the macro didn't work...I would like to go back to the simple idea of creating a macro that will insert lines and copy the data from above. I will do the rest manually, it's filling down the information that takes too long not the rest. I would appreciate any assistance on this.

    Thanks,
    Genie

  21. #21
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    Your RAW DATA sheet has completely changed from the first example given. The macro will not work willy nilly on the sheet if you make substantive changes.

    The RAW DATA on this sheet appears to already have the 1,11,24 column and 0,5,10 column. Is that correct?

    If not, please correct your file again so that it is properly representative of the starting RAW DATA.

  22. #22
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    I apologize, I forgot to include the old RAW DATA. Here it is.

    Thanks again!
    Attached Files Attached Files

  23. #23
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    Hmm, I don't see your edited version of the macro in this file. I'm trying to make sure you're understanding as much of this as possible.

  24. #24
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    I apologize...I'm not making this any easier on you. I am in the middle a new software implementation and this is only a small part of it. Here is the updated file with the macro included.
    Attached Files Attached Files

  25. #25
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    Try this, I've stripped as many of the extraneous parameters as I can identify:
    Please Login or Register  to view this content.

  26. #26
    Registered User
    Join Date
    09-07-2010
    Location
    Albuquerque, NM
    MS-Off Ver
    Excel 97
    Posts
    16

    Re: Updating a code to include the data above rows inserted

    YOU ARE A ROCK STAR!!!! That worked beautifully! I am going to look at the macro closer so I can make the changes for the other three price lists I need to create. Thank you, thank you, thank you!

    Genie

  27. #27
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Updating a code to include the data above rows inserted

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ Reply to Thread

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