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:
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.
Please Login or Register to view this content.
Any help is greatly appreciated.
Thank you,
Genie
Last edited by GAccounting; 09-13-2010 at 09:56 AM. Reason: Solved
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 theicon 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!)
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.
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.
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
It's not actually convoluted at all when you put it in a standard lookup table.
Is that clear and readable?Please Login or Register to view this content.
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.
Thanks again!Please Login or Register to view this content.
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.
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.
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.
Ok...I tried it this morning and got an error: Compile Error/Named Argument Not found. I highlighted the portion of the code:
Awaiting your feedback,Please Login or Register to view this content.
Genie
Did you try taking that parameter out? I didn't notice you were using Excel 97, that's pretty far back.
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
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.
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)
Any suggestions?Please Login or Register to view this content.
Thanks again for all your assistance!
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.
Ok...I got that one fixed and another one that came up, but now I'm stuck at this one:
The error was AutoFill method of Range class failed.Please Login or Register to view this content.
Thanks again!
Ugh...you're killing me with these '97 limitations. Use this in place of that:
Please Login or Register to view this content.
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.
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
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.
I apologize, I forgot to include the old RAW DATA. Here it is.
Thanks again!
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.
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.
Try this, I've stripped as many of the extraneous parameters as I can identify:
Please Login or Register to view this content.
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
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks