Excellent code now a question of Feasibility

[Howard]

The link below is a generic workbook using the excellent code Claus has been so helpful with.

In essence a six digit "item" is derived from six drop down values and the code looks up that "item" on sheet3 and returns stuff offset to both the right and left of the lookup column F back to sheet 1 into the gray shaded area.

Hence, it builds a list of the lookup "item" and its components in the gray area.

If there is no match for the "item" as it looks in column F of sheet 3, then there is a msgbox pop up "No Match".

Here is where I am wondering about the feasibility of identifying which one of the digits in "item" is causing the No Match.

The workbook as it is sheet 1 shows 1 2 3 4 5 6 as "item". (note the 6 is shaded). On sheet 3 see the same color shade entry in column F and you will see that entry is 1 2 3 4 5 7. This of course causes a No Match alert.
(And I used the very first entry to make it easy to demo)

Is there an easy way to notify the user that the number 6 is causing the mismatch? If the alert said "No match, component 6 does not match" that would great. But is it feasible?

I know this is where the mismatch is because I set it up as a demo for this post. I'm thinking things will get real sticky when as a user you pick components for an item and get a No Match, you don't know which component is causing the No Match.

https://www.dropbox.com/s/dgy83hwqa4n9vbl/Assemble Number FIND and list or NO MATCH Drop Box.xlsm

Regards,
Howard

 

[GS]

Howard,
I haven't looked at your sample workbook yet but are you trying to
'build' items with various component content so that the 'item' ends up
being a 'package' of specific components?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Howard]

Howard,

I haven't looked at your sample workbook yet but are you trying to

'build' items with various component content so that the 'item' ends up

being a 'package' of specific components?



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com

Yes, that is pretty much the idea. The components are electrical parts of plugs and receptacles. You match up the parts to make an "item" (plug or receptacle) and then with some yet to be developed code a price will be determined and probably some discount applied at certain volumes etc. Still a bit of a moving target at this point.

One of the "has to be's" of the workbook is that it can be used by someone with zero computer knowledge. I don't buy completely into that concept, a few hours training seems appropriate, but it's not my show.

As an aside, the reason I made this generic workbook which uses nearly all the same cell locations as the original the OP offered up is because the original was so laden with merged cells and an pretty neat method that would post a drawing of the plug and receptacle once all the components were selected. Down side was I would test some simple code to make sure I was on track and often the workbook would freeze up and crash. Took about a minute to open and the same to save.

Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 17 Dec 2013 17:53:30 -0800 (PST) schrieb Howard:

https://www.dropbox.com/s/dgy83hwqa4n9vbl/Assemble Number FIND and list or NO MATCH Drop Box.xlsm

if you had real numbers you could look for the next larger or next
smaller number. But with your strings it is not so easy.
Please have a look:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Assemble Number" and test it


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Wed, 18 Dec 2013 09:07:12 +0100 schrieb Claus Busch:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

another suggestion: Create depending Data Validation that no false
numbers can be selected.

Regards
Claus B.

 

[Howard]

Hi Howard,



Am Tue, 17 Dec 2013 17:53:30 -0800 (PST) schrieb Howard:






if you had real numbers you could look for the next larger or next

smaller number. But with your strings it is not so easy.

Please have a look:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for the workbook "Assemble Number" and test it





Regards

Claus B.

Well, that itself is pretty clever, I used easy to produce test data not thinking it would make any difference what was in the cell.

This would be more of a representation of what you would see in the drop downs in a working workbook. The Data Validation may be the most practical.

UK Plug

IEC 60320 C13

5 Meter

H05VV F 3G1.0mm2

Black

H05VV-F 3G1.0mm2 3A/250V

I'll look into the DV method.

Thanks, Claus.

 

[Claus Busch]

Hi Howard,

Am Wed, 18 Dec 2013 02:02:07 -0800 (PST) schrieb Howard:

This would be more of a representation of what you would see in the drop downs in a working workbook. The Data Validation may be the most practical.

UK Plug

IEC 60320 C13

5 Meter

I have another suggestion on SkyDrive. Please look again for
"Assemble Number"


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Sun, 22 Dec 2013 12:29:40 +0100 schrieb Claus Busch:

I have another suggestion on SkyDrive. Please look again for
"Assemble Number"

there is no other way than depending Data Validation.
I got errors while intensive testing.

Regards
Claus B.