Hello experts
I am trying to extract data from master sheet (SRS data) sheet and enter it into 3 new created sheets by the code. But I am stuck in the first sheet itself. I am facing a run time error 7 out of memory. Please help me to correct this code or help me with a new code for at least 1 sheet so that I can get the data for the remaining 2 sheets accordingly.
Thank you in advance.
Last edited by RAJESH SHAH; 01-23-2023 at 02:29 PM. Reason: #Solved by beyond excel
Administrative Note:
YOU HAVE HAD TO BE WARNED ABOUT THIS TOO MANY TIMES!!!
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Sorry. Forgot to do so. This is the only link I have posted to.
https://forum.ozgrid.com/forum/index...76#post1259576
Last edited by RAJESH SHAH; 01-23-2023 at 06:03 AM.
Apart from anything else check out you formula:
Step2 G (Qty)
=SUMIFS(SRSData!$K:$K,SRSData!$B:$B,$A2,SRSData!$R:$R,Step2!$F2,SRSData!$M:$M,Step2!C2)
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
John Topley. I am not getting it. There are no R R and M M range in the formula. If you check the Step1ER(Expected Result sheet) it is the same formula. The range is right and I think there must be some error in converting it into a code. Will check again.
I assume it is because of the values in columns E and F in step2 Sheet. The format is changed when posted from the SRSData sheet. The quantity column is connected to column F and hence the errror. Not sure. Back to the drawing board I guess.
... That is because your formula was wrong!!!!: it was not matching the correct ranges.
Attached now has correct formula.
John Topley. You entered the formulas of HSN sheet in Step2 sheet. Looks like you corrected the CHSN code. Will check and if it is correct than I think I can come up with the other 2 sheets. Will revert book as soon as I finish checking it.
Last edited by RAJESH SHAH; 01-23-2023 at 09:02 AM.
The code I checked was in macro "Astep2".
No/ You check the raw code of HSN which is not yet edited. I dropped the idea of using .Offset as it was not working and was trying for Formula array in the AStep1code.
Last edited by RAJESH SHAH; 01-23-2023 at 08:59 AM.
The HSN code will work only after Step2 and B2B SRS are created. So, I was trying to create Step2 sheet and get the result.
These are the formulas in Step2 which need to be corrected.
Please Login or Register to view this content.
Those are the formulas in "Astep2" which I corrected!
Oh Yeah. Sorry. I am fully confused. The formulas are posted correctly in the second row. Can you tell me why they are not filled down.? I have this code to fill them down.
Please Login or Register to view this content.
My bad. Even the remove duplicates is displaying the wrong answer. There are only 268 rows in Step2 sheet where as in the expected sheet it is 272 rows. Please let me check it out once again and try to rectify it. I want to keep this post open till it is completely solved. Will be back once I figure out what is the mistake. Thanks for the help with the formulas John Topley.
Please Login or Register to view this content.
The highlighted need to be moved to before ..
and you will need to add a "row counter" as you go through the loop for examplePlease Login or Register to view this content.
becomesPlease Login or Register to view this content.
nextrow is incremented each time you get a new key from the Scripting DictionaryPlease Login or Register to view this content.
Like this right ??
Please Login or Register to view this content.
I didn't understand this part.Originally Posted by JohnTopley
Please Login or Register to view this content.
The highlighted need to be moved to before ..
and you will need to add a "row counter" as you go through the loop for examplePlease Login or Register to view this content.
becomesPlease Login or Register to view this content.
nextrow is incremented each time you get a new key from the Scripting DictionaryPlease Login or Register to view this content.
I have changed the nextrow to LastRowStep2 as the variable is there and defined.
Please Login or Register to view this content.
Yes ... and same for your "Formulasarray" etc
Which is the highlighter which needs to be moved and to where. ?
Please confirm so that I can edit the rest of the code. Is it like this
Please Login or Register to view this content.
Does this line too need to be edited ? Please correct this line.
Please Login or Register to view this content.
John Topley. It is dinner time. Please leave a reply for post #21 and #22. Will continue as soon I finish my dinner.
This is the one needing changing
Sheets("Step2").Range("G2:K2").Formula = FormulasArray
to
Sheets("Step2").Range("G" & lastrowStep2 &":K" & lastrowStep2).Formula = FormulasArray
NOT the one(s) in post #21
and No : post #22 is OK
I seriously suggest you practice your VBA on something much simpler rather [than on your production system]!
Done. But still facing an error and the data is posted till the last row in the sheet in column F. Please check the workbook if I have missed anything or done something incorrect. The posting also starts from the 20th row.
One more thing please. The code to remove the duplicates is not showing the right number of rows in the result (Step2). If you manually select the columns 2,3,6,13,15,18 from SRSData, it will show 272 rows of data but with the code it is showing less.
After some corrections. the code ran without any error. But the columns in A to F are all posted from the wrong columns in Step1 sheet. Using F8, I checked and the error looks like in this line.
Please Login or Register to view this content.
I am sharing the updated file.
My mistake in changing loop But corrected ALL formula so now "looks" OK.Please Login or Register to view this content.
John Topley. The columns in step1 sheet from A to F are taking the data from the wrong columns.
Check the code yourself and make sure ALL the right sheets are being referenced correctly as the code "jumps" to/from numerous sheets: I am not here to teach you VBA.
Ok. Thanks John Topley for all the help and your time.
I have not changed anything relating to columns A-F: I assume you mean this ??
Please Login or Register to view this content.
I closed the file and reopened and tested again. It is working and I got all the headings and formulas correct. Only thing left is to check the remove duplicates code. Manually it will show 272 rows and by code it is showing 268 rows only.
So which rows are different: check the data as "Scripting Dictionary" is very unlikely to make a "mistake".
The very first row in SRS Data sheet for instance is not in Step2.
The earlier project which I did for step1 the same error is there. I didn't notice it till now.
Hello.
The template I attached basically does the following on each sheet:
a) Clear the entire sheet.
b) Filter and copy the unique values of each sheet.
c) Copy and paste down the formulas that are in row 2 of each sheet.
d) Sets all rows except row 2 to values.
And that's why the VBA code is so short: because it's based on the formulas in the 2 rows of each sheet.
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
Wow! We were almost done with the issues with step2 sheet. You solved all the 3 sheets in one go. Simply brilliant.
Thank you very much beyond excel.
This
For i = 2 To UBound(Data)
NOT this
For i = 6 To UBound(Data)
John Topley, If you come across for the reason for the issue in the remove duplicates code, please let me know what could be the reason. So that I can correct the previous code of step1. I really appreciate the time you spent to help me understand the code.
Thank you guys.
Thanks man. Now the result is perfect. I will correct the previous code too.
This is the code of the Step1 and I replaced 4 with 2 and it is showing an error at this line
If you correct this, then the step1 will also be completed.Please Login or Register to view this content.
Please Login or Register to view this content.
This was your previous code. I had to add one more criteria to remove the duplicates and I added Temp4 in the code. This has the same problem in the count of rows.Please Login or Register to view this content.
a) Thanks for the comment. You just need to click on the * below and to the left of post #38Wow! We were almost done with the issues with step2 sheet. You solved all the 3 sheets in one go. Simply brilliant.
Thank you very much beyond excel.
b) I am attaching the Sub Macro9() with explanatory comments as you requested:
Please Login or Register to view this content.
Thanks beyond excel. I am very grateful to you for the comments.
It was a great pleasure to help you and I hope you have the desire and time to gradually learn about these VBA topics.
Pleasure was all mine. I learn from the comments only. As you have provided the code with the comments it would help me to learn, edit, modify whenever and wherever necessary in future.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks