• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search item code no from item master dataset

Hello Narayan Sir,

With go through your program.. Sir, I have 1000+ items and some item description is same 2 same, only there code no. & part no. is different. Now i am entering the text [i.e. Bearing 2007] and its give list of 2 items found. At the time of click 1st item its return the corresponding code no & part no. and also same code no & part no. give for 2nd item selection. As i attached file. Also if i add new item in dataset he could not find the new item added by me. Why its happend?

In this case how i get the 2nd item correct code no. & part no. It could be solve Sir?

Warm Regards,


Zameer Shaikh
 

Attachments

  • Item Search by USERFORM.xlsm
    23 KB · Views: 4
Hello Narayan Sir,

Thanks a lot... my hopes are increased when you say "Give me some time".

If its cleared all is will be well.

Thanks once again Sir.

Warm Regards,


Zameer Shaikh
 
Good Morning Narayan Sir,

Thanks for your reply. I have checked file, but its give Run time error 13: Type Mismatch

Whats wrong?

Warm Regards,

Zameer
 
Hello Narayan Sir,

Thank you Sir, As you give 2 button [Check for the text Anywhere within cell & Check for the text at the start of cell]. When i am type Bearing list generate releated to this word, then i select the items by item it give right code no & part no., But when i enter last text of item like 2007 its generate Bearing 2007 [2 items], but return wrong code no. & part no.. its displayed 1st 2 code no. & part no. which data starting point. Also new added item list search, but return wrong number.

This is for your kind information please sir,

Warm Regards,

Zameer Shaikh
 
Hi Zameer ,

Can you please confirm the following ?

In the case where there are multiple entries with the same description , will the code numbers for these entries all be unique ? Or will the part numbers be unique ?

Narayan
 
Hello Narayan Sir,

There could be multiple entries of same item, but code is unique. It may part no also same the items. because of suppliers are different, hence single item but code no & part numbers are different.

As you can see the item sr.no.13 &14 Bearing 2007 two time comes with different code no. / part no. & rate.

Warm Regards,

Zameer Shaikh
 

Attachments

  • Item Search by USERFORM.xlsm
    23.3 KB · Views: 4
Hello Sir,

I checked file. its work perfectly. But when i am entering new item its search, but when select the new item its give run time error 1004 "Unable to get the match property of worksheetfunction class.

Wats wrong sir?

Warm Regards,

Zameer
 
Hi Zameer ,

The problem was that the range being accessed from within the MATCH function was static , and referring to only the range D2 through D15. Let me modify the code a little to make it dynamic.

Narayan
 
Hello Sir,

Please make it flexible with table that will be update automatically as date update.

Warm Regards,


Zameer Shaikh
 
Hello Narayan Sir,

Finally you touch the sky. I'll always respect you sir, Once again i said you really good heart person..

God always bless you... Thanks, Thanks....... lots of thanks.


Sir, I am physically challenged person.. and doing Govt. Jop in transport sector in store department.

With your & your team [Chandoo.org] support, i think i'll become excel hero...


:p:):DD
 
NARAYANK991 said:
Hi Zameer ,

That's touching ; all the best.

Narayan
Click to expand...

Hello Narayan Sir,

I have work with your code as on my level, but it could not make. Here i am attaching my program file with your code its give errr 400! Here is little bit changes of cell, that code no. / part no. and description of item are there. but closing balance next 3 colomn. data should access from ItemMasterRegister.

I have created userform, please look in code whats wrong... I want Code No. / Part No. & Quantity which store in ItemMasterRegister.

Sir, I am very new in VBA as i said, Can you explain what wrong with that.

This is for your kind information & necessary action plese.


Warm Regards,

Zameer Shaikh.
 

Attachments

  • SIMS.xlsm
    999.5 KB · Views: 5
Hi Zameer ,

I am sorry but I cannot spare the time to debug the application fully ; the basic problem was that I had used a named range called List , referring to the list of descriptions ; once this named range is created , one problem was resolved.

Now , there is a problem that within the code , I am using the following statement :

code_number = .List(selected_item_index, 1) * 1

This halts program execution when it encounters a code such as NCN1BK0256 , which is not numeric. This will now involve changing the code or changing the type of data in column E ; all this is going to take a lot of time , since I do not know whether after resolving this problem , there will not be other problems further down the line. At this stage I cannot spare the time for this.

The file with the needed named range is uploaded.

Narayan
 

Attachments

  • SIMS.xlsm
    999.1 KB · Views: 9
Hi Zameer ,

Instead of the following line :

code_number = .List(selected_item_index, 1) * 1

try this code

Code: 
If IsNumeric(.List(selected_item_index, 1)) Then
  code_number = .List(selected_item_index, 1) * 1
Else
  code_number = .List(selected_item_index, 1)
End If
Narayan
 
Hello Narayan Sir,

BRAVO!!!!

I have replace the above code with above code given by you. Its work Perfect. Numeric & text code display with selected item.

Also i have change the name range IssueControl Register to ItemmasterRegister. Its works perfect & smooth.

A Big Thanks with Big Heart...

Only there is quantity return from colomn K. I want the quantity return from Colomn N in label7.

How do i get the quantity from Colomn N. I had tried to change the label7 caption match row 7 instead of 4.

But it gives error.

This is for your kind information please.


Warm Regards,


Zameer
 
You must log in or register to reply here.
Back
Top