One for the VBA Guru's

goldenvision

goldenvision

Board Regular
Joined
Jan 13, 2004
Messages
234
Firstly, before I get into this I'm not even sure if this is possible, but I figure that if it is someone on here will know how to do it. Here goes:

Background
I have a table on an SQL database which holds tens of thousands of line items relating to stock items. (Let's call this Main Stock). On a weekly/monthly/quarterly basis I receive new stock lists from manufacturers (Let's call this Manuf Stock) which will contain items already in Main Stock plus new items not included.

The Problem
I need to add any new add items from the Manuf Stock list to the Main Stock list. This would be relatively easy apart from there not being a matching reference on both files. Also, items that may appear to be new items on the Manuf Stock list could actually just be reclassified items. For example: item 205/55/16 94V XPLY Run Flat Pilot HXM could appear as a new item on the Manuf Stock list when it is actually a reclassification of 205/55/16 94V Run Flat Pilot HXM. (This is actually a very simplistic example). In the case of items being reclassified the requirement is to simply point the item on the database to a different code.

Continuing to use the above example this would be stored in the Main Stock file as Section:205, Profile:55, Rim:16, Load Index:94 Speed Rating:V Details: Run Flat Pilot HXM

What I would like to be able to do is have two Excel windows open, one with Manuf Stock and one with Main Stock. Then to click on Section:205 in the Manuf Stock list and have the Main Stock file automatically filter to show only item with a Section of 205, then click on Profile:55 and have the Main Stock file automatically filter to show only items with a Section of 205 and a Profile of 55 and so on for 5 criteria.

I have read through a lot of VBA books and there seems to be a number of functions that could do elements of this but I don't know how to put it all together as a module. I would rate my VBA skills as an Enthusiastic Amateur.

If anyone can give any advice, pointers or guidance on this it would be greatly appreciated. Even if you read through this and think it can't be done can you please reply to this and say so, so that I atleast know.

If you have any questions or anything needs clarifying please ask as I'll be watching the post.

Thanks in advance.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
Hi there,

just my 2 cents here :)
You describe a procedure of clicking in one workbook/worksheet which then has an effect on another workbook/worksheet and vice-versa. This is certainly possible to do in VBA and depending on what you exactly want regarding functionality, could be fairly easy...
If what you want also includes getting data from and putting data back into your SQL database, that would complicate matters, but it can be seen as a different problem.
I understand you have an existing list and you get a new list. Then you must somehow check and match the new list to the existing list, adding any real new items. The to and from clicking procedure you describe seems somewhat strange to me, could you explain more on how you would handle these two lists? How do you compare them? What are the criteria to move data from the 'new' list to the 'old' list?
I'm willing to help you getting on your way, but you will need to provide a whole lot more information...

Edit: and by answering this post, I do not, in any way, claim to be a VBA-guru!! :LOL:
 
Upvote 0
Hermanito,
Thanks for your response.

I think a bit of clarification is in order.

This is the procedure I would go through.
Both the Main Stock file and the Manuf Stock file will be open. I would look at the first line item in the Manuf Stock file (for example 205/55/15 94V. This is stored in 5 seperate cells). I would click on the cell in the Manuf Stock file containing 205. This would filter the Main Stock file to only show 205's. I would then click on the 55 in the Manuf Stock file. This would filter the Main Stock file to only show 205/55. I would then click on the 15 in the Manuf Stock file. This would filter the Main Stock file to only show 205/55/15's etc. Once the Main Stock file was displaying all 205/55/15 94V's I would look at the other details of the line item and determine whether or not it was a genuine new item or a reclassification. If it is a reclassification I would take the MIDAS code (unique stock ref) from the Main Stock file and apply it to the Manuf Stock file. If it is a genuinely new item I would add a new MIDAS code to the Manuf Stock file.

I hope that this clarifies it a bit.
 
Upvote 0
Why would you filter in multiple steps? Why not directly check the Main Stock file for any matches on those 5 criteria together? Is there a purpose in doing it in 5 steps?
The MIDAS code you talk about: do you generate this yourself? Does it come with the Manuf Stock file? Is it on a separate list?
Many questions, I know, I'm just trying to follow and get a clear grasp of the problem, in hope of proposing a nice and clean solution...
Further I would expect that any VBA code would have to be in the Main Stock file, since you maintain that one yourself, correct? Or is this file generated by the SQL-database everytime you want to check it?
 
Upvote 0
In answer to your questions:
Why would you filter in multiple steps? Why not directly check the Main Stock file for any matches on those 5 criteria together? Is there a purpose in doing it in 5 steps?
Click to expand...
Each Manuf Stock file holds the section/profile/rim/load/speed information in a different format. A module is ran on each Manuf Stock file to split this information out into 5 columns to match the format of the Main Stock file.
The MIDAS code you talk about: do you generate this yourself?
Click to expand...
This is generated from a mixture of manufacturer, sprls and description.
Does it come with the Manuf Stock file? Is it on a separate list?
Click to expand...
No and No. See above.
Further I would expect that any VBA code would have to be in the Main Stock file, since you maintain that one yourself, correct?
Click to expand...
This is sort of correct. I would envisage an Excel template with a link to the SQL table which I would refresh each time I needed to do this process.

Keep firing the questions.
 
Upvote 0
the plot thickens :)
so there are different formats of Manuf stock files... how many? You speak of a module that is ran on each Manuf Stock file to split the info, this module is in VBA and already written and working?
This is sort of correct. I would envisage an Excel template with a link to the SQL table which I would refresh each time I needed to do this process.
Click to expand...
A template is possible, but probably a normal workbook would do, or an add-in...
 
Upvote 0
There are around 30 different manufacturers and each one has there own preferred way of naming items. As this is cherished information we cannot change this. The module that runs against these determines who the manufacturer is and then applies the relevant 'mask' over the top of the item code and extracts the sprls information.
 
Upvote 0
so, am I correct to assume that a consistent layout of the data in both Manuf worksheet as in Main worksheet can be used as a starting point?
 
Upvote 0
so, am I correct to assume that a consistent layout of the data in both Manuf worksheet as in Main worksheet can be used as a starting point?
Click to expand...
The layout and format of the columns I need to filter on are identical in both files, yes
 
Upvote 0
I have setup a small testcase here, one macrofile, one Manuf.xls file, one Main.xls file...
What it does now, just to show you what is possible, it is kept very basic and simple, but it should give you some ideas on how to continue: when the macro is activated, application level event trapping is enabled, from then on, every selected Section in Manuf.xls will be searched and all found matching Sections in Main.xls will be highlighted.
A picture is worth a thousand words they say, unfortunately I do not know how to paste a picture here...
Please PM me with an email-address so I can send you a zipped file with my testcase setup... trying to do that via the forum would be difficult, since it's about three excel files, and a macrofile with three modules...
If you do not fully trust sent excel-macro-files (good attitude to not trust any excelfile), first open it with macro's disabled and examine the code :cool:
If you like the testcase, we can continue discussing how to go on...
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question
Print to PDF in VBA
Replies
0
Views
65
visheshkotha
V
tlc53
  • Question
VBA Save All Relevant Report into One Document
Replies
5
Views
439
tlc53
tlc53
A
  • Solved
How can I exclude subfolders in a subfolder
Replies
5
Views
373
Ascherith
A
M
  • Question
VBA search for specific file name in folders and their subfolders
Replies
7
Views
823
Candyman8019
C
M
  • Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
488
MissDB101
M

Forum statistics

Threads
1,214,927
Messages
6,122,309
Members
449,080
Latest member
jmsotelo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top