Please refer the files attached.
I've to two workbooks.
In Myworkbook file I have only Tyre codes and descriptions. In the second file I've tyre codes with their tubes and flaps codes & descriptions.
In the Myworkbook file in sheet1, I wants to insert rows and copy tubes and flaps codes & descriptions from ProductSet file.
Please also refer the Example sheet in myworkbook file.
- Abhinav
In D2 =VLOOKUP($B2,'[ProductSet.xlsx]PRODUCT GROUP'!$D$2:$H$222,3,FALSE) and copy down
in E2 =VLOOKUP($B2,'[ProductSet.xlsx]PRODUCT GROUP'!$D$2:$H$222,4,FALSE) and copy down
in F2 =VLOOKUP($B2,'[ProductSet.xlsx]PRODUCT GROUP'!$D$2:$H$222,5,FALSE) and copy down
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hello Alan,
This is not how i required. I needed the result to appear by inserting new rows after every matches if any. Please see the Example Sheet attached in file 1 for how I required the data in sheet1.
I do not see any expected results in your workbooks. Please post a workbook with an example of what you want.Please see the Example Sheet attached in file 1 for how I required the data in sheet1.
In the attached example file, the yellow colored rows are newly inserted rows for tubes and flaps of the above tyre which having a set of tubes and flaps.
The similar result i wants in sheet1 of myworkbook file.
When refering to source worksheet Sheet1 and comparing with the result worksheet
it seems you forgot to explain important things ! …
Now I've combined both files into one workbook (NEW_SHEETS) and shorten my data as well for better understanding.
Please help.
-Abhinav
Try this ‼
As a beginner starter :
PHP Code:Sub Demo1()
Dim R&, Rg As Range, C%
Application.ScreenUpdating = False
With Sheet1.UsedRange.Rows
For R = .Count To 2 Step -1
Set Rg = Sheet3.UsedRange.Columns(3).Find(.Cells(R, 1).Value2, , xlFormulas, xlWhole)
If Not Rg Is Nothing Then
For C = 5 To 3 Step -2
If Rg(1, C).Value2 Then
.Item(R + 1).Insert
.Item(R + 1).Value2 = Array(Rg(1, C).Value2, Rg(1, C + 1).Value2, .Cells(R, 3).Value2)
End If
Next
End If
Next
Set Rg = Nothing
End With
Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
It is not making any change in my before sheet. Please help sir.Originally Posted by Marc L
As it works a treat on my end with your last attachment ! …
And do not quote the whole previous post as it's just clutter !
I'm running that macro code in my last attachment (NEW_SHEETS.xlsx) in sheet "BEFORE SHEET".
But after running the code I'm not seeing any changes in that "BEFORE SHEET".
What I'm doing wrong? Please help.
- Abhinav
As it rocks on my side, as it's very not a mind readers forum, attach your workbook updated with the code …
Hello Marc,
Please find the attached workbook with code.
Hello Marc,
Please help me out. The code you have provided is not working on any of my systems. I don't know where i'm doing mistake. Btw i actually required the code to run in my earliest attachments of this thread, Where, the working formula file(Myworkbook.xlsx - Sheet1) is different and the source file (ProductSet.xlsx) is different. If required you can take my PC by teamviewer/anydesk also.
- Abhinav
Last edited by abhinavbinkar; 08-13-2018 at 04:29 AM.
As your post #13 attachment works like a breeze on my side on different PC & Excel versions
so I can't do nothing more; to see with the IT of your systems …
Sir,
The code is not working on my personal laptop also. I'm a newbie in vba and excel. I think I might doing some silly mistake to execute this macro code.
I will be really thankful if you help me to guide me further. You can also remotely take my laptop via teamvierwer or anydesk programme.
I desperately needs to get this done sir. please help me.
- Abhinav
Question about your data:
in sheet "AFTER SHEET (EXAMPLE)"
for code "11111600" , Description and Stock information is coming from sheet "BEFORE SHEET" => Clear
for code "31011500", Description is coming from sheet "SOURCE SHEET" but what about the Stock ???
- Battle without fear gives no glory - Just try
Is there any order or row where to put new data in sheet "BEFORE SHEET" or can we paste all new data at the bottom ?
Sir,
please wait for 5 minutes i'm uploading new files for better understanding.
Sir,
Please find my new files.
1. MYFILE1.xlsx - The file in which the macro code to run.
2. SOURCE SHEET.xlsx - The file from which data of tubes/flaps codes & description to be taken.
3. EXAMPLE-HOW MY BEFORE SHEET SHOULD LOOK AFTER CODE RUN.xlsx - This file is just for example of what the end result I required.
The "Stock" quantity of tubes and flaps should be the same of its Tyre in Before Sheet.
The "Stock" quantity of tubes and flaps should be the same of its Tyre in Before Sheet.
for code "31011500" from where quantity (3) is coming from ???
Are all files already loaded in Excel, or are they in the same folder ??
Would you pay attention to previous questions and give answer !!!
Sorry, I just saw your message, Today is our nation India's Independence day, so was celebrating that.
for tube code "31011500" the quantity has to come "3" because of its respective tyre code "11111600" is "3". The same stock "3" also needs to come for its respective flap code "50021530" too.
just like that for tube code "31021510" the quantity has to come "0" because of its respective above tyre code "11121480" is "0". The same stock "0" also needs to come for its respective flap code "50021550" too.
and also for example that for tube code "33288500" the quantity has to come "3" because of its respective above tyre code "13078330" is "3". This tyre code does not come with flap so there is no need to insert the row for flap for this size.
Both my MYFILE1.xlsx and Source Sheet.xlsx are in the same folder.
- Abhinav
The "Before Sheet" before running code contains Only Tyre's code, description and stock (which changes daily). By macro we needs to insert its tubes & flaps codes/description (if available) from Source Sheet file of their respective tyre codes in "Before Sheet". The stock for newly inserted tubes/flaps rows by macro in before sheet should come as same as their above tyre stock in Before Sheet.
Last edited by abhinavbinkar; 08-15-2018 at 12:56 PM.
I think I am starting to understand something.
See next code and file attached.
To start slowly all data is in the same file.
Note : if some code are NOT found in sheet "SOURCE SHEET" they are not treated ...!
Please Login or Register to view this content.
Last edited by PCI; 08-15-2018 at 02:19 PM.
Hello Patrick sir,
Thanks for giving you precious time and efforts. The code is working. Some few changes required are below:
I don't need the result in "After sheet". I wants the code to execute and get my result in the Before Sheet itself.
My "Source sheet" sheet and "Before Sheet" sheet are in Separate Workbook in the same folder.
If any tyre code in "Before sheet" does not exist in the "Source sheet" file, after running the code the row of tyre is disappearing in the "After sheet". This needs to get corrected in the code.
Optionally if possible, can the code run faster. Because my actual "Before Sheet" data normally contains more than 400 hundred rows of tyre codes.
- Abhinav
Last edited by abhinavbinkar; 08-15-2018 at 03:26 PM.
The code could rename the result sheet "After sheet" and delete "Before Sheet " is it OKAYI don't need the result in "After sheet". I wants the code to execute and get my result in the Before Sheet itself.
Clear, the code sent was the trial to be sure the understand is correctMy "Source sheet" sheet and "Before Sheet" sheet are in separate Workbook in the same folder.
Yes, and you want to keep the code as it is ??If any tyre code in "Before sheet" does not exist in the "Source sheet" file, after running the code the row of tyre is disappearing in the Aftersheet. This needs to g corrected in the code.
How many rows ???contains more than 400 hundred rows of tyre codes.
Yes it is possible to speed up
The code could rename the result sheet "After sheet" and delete "Before Sheet" - It's Okay.
The number of rows usually contains between 300 to 500 rows in Before Sheet.
I wants to keep the code of tyre which not exist in "source sheet" as it is in the After sheet.
- Abhinav
See next code
Is still needed to speed up the code but on my PC it takes 17s for 500 rows , so not too long ...!
Please Login or Register to view this content.
Excellent. Brilliant. Amazing. You're a genius sir. Thanks a lot. It working now. God bless you.
- Abhinav
Sir,
Just one thing left. Will it work if my "Source sheet" sheet in the separate workbook?
No I forgot this pointWill it work if my "Source sheet" sheet in the separate workbook?
Nevertheless see next code faster than the previous one
Please Login or Register to view this content.
Last edited by PCI; 08-15-2018 at 05:33 PM.
Sir,
When my "Source Sheet" file is in separate workbook, it is giving error (image attached)
And Lastly I wants to modify couple of following things in the code:
My "Source Sheet" file is directly located in "C" drive and not in any sub-folder there. After code run in "MYFILE2.XLSX" can it automatically open the "Source Sheet" file from that path and after the code finish it should automatically close the "Source Sheet" file.
I've added one more Column "D" in the "Before Sheet". It is the location code column. It should work just like the Column "C" Stock.
All the newly created tubes/flaps Rows made by macro in "Before Sheet", it should copy the location code(column D) of their respective above tyre code(Just like it do for Column C - Stock).
- Abhinav
Last edited by abhinavbinkar; 08-16-2018 at 01:37 AM.
See next code
Adjust Const to your need
Please Login or Register to view this content.
It is mind blowing. Superb. Exactly what I required. Thanks a ton. SOLVED.
- Abhinav
Oufff we got it
Sir, One query here. This code is working absolutely fine when I'm running this code by View+Macros+View Macro+run, but it is only not working properly when I run this macro by assigning it any Shortcut key. Is it correctable for this code?
- Abhinav
Last edited by abhinavbinkar; 08-19-2018 at 01:20 PM.
Certainly, what is the short key you want to use ???Is it correctable for this code?
Send the last file you want to prepare.
I'm assigning the shortcut key to this macro as Ctrl+Shft+k
See attached your file with the last macro sent: Adjust Const to your need
And confirm it's OK
Last edited by PCI; 08-20-2018 at 06:44 AM.
Sir,
It is getting stuck after opening Source Sheet.xlsx file.
What do you mean by
IS the shortkey working ??It is getting stuck after opening Source Sheet.xlsx file.
Please send the files concerned
I've assign the shortcut key as ctrl+shft+r.
When I ran the macro by shortcut key the macro starts and finish without making any changes in my Before Sheet.
Macro is ending by displaying "Source File.xlsx" but not doing any modification in "Before Sheet".
But the same working great when I ran that code manually.
Last edited by abhinavbinkar; 08-20-2018 at 07:14 AM.
There is 2 macros: Treat5 and Treat6
You have to put the macro in a module and not in the sheet's code
See your file attached and comment
Sir,
I've deleted Treat5 & Treat6 macros and created a fresh Treat6 macro in Module.
But still it is giving same results with the shortcut key.
Last edited by abhinavbinkar; 08-20-2018 at 11:34 AM.
Can you send the 2 files to permit to do the full test on my computer
I am still waiting a contact from you ...!
Sir,
Please find both the files.
Please Login or Register to view this content.
There is no issue
See file attached after job done
Ok. Thanks sir. Now its working. Solved.
Really ... I do not see what's making the difference with previous file ...!Now its working
But result is there.
Enjoy Excel
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks