+ Reply to Thread
Results 1 to 2 of 2

Error! when searching data from worksheet.

  1. #1
    Registered User
    Join Date
    02-22-2013
    Location
    pakistan
    MS-Off Ver
    Excel 2007
    Posts
    89

    Error! when searching data from worksheet.

    Hello EveryOne,

    I have got a UserForm to search data from worksheet and show it in userform.

    In my search userform, the data is searched by first selecting " Year " and then " Batch Number " (Two Batches runs in a Year)
    so I filled combo boxes with "Batch 1" and "Batch 2"

    for example "Year 2013 " would have "Batch 1 and Batch 2"
    "Year 2014" would have "Batch 1 and Batch 2"

    In my worksheet, it has Data for "Year 2013" and data for "Year 2014" has not yet been inserted.

    The Problem is that when In the Userform I select " Year 2013 " and select Batch 1 or 2 it shows data in list box and in textboxes.

    But when I select "Year 2014" and select "Batch 1" or "Batch 2" it gives error.


    I know there is no data for "Year 2014" yet in the worksheet, but I want it should not give error in vba coding, But It should display MsgBox "No record found"

    I have attached worksheet and userform.

    Here is the code
    Please Login or Register  to view this content.
    I hope you guys will be understaning what I really mean?

    Plz help

    Kind Regards
    z-eighty2
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,368

    Re: Error! when searching data from worksheet.

    Hi,

    Immediately after your For x..Next x loop add

    Please Login or Register  to view this content.
    I note that you have a loop to find the relevant row. As an improvement I'd suggest you consider a better way of finding the desired row. Loops can be time consuming for this type of thing when you have many tows to test.

    One way would be to have a helper column in your data that concatenated the Year and Batch numbers, then you could do a simple Find for the combination of ComboBox1 & Combobox2.

    There are no doubt several other methods, but avoid the loop in some way.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. macro error while searching data in multiple sheet
    By sanyaa in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-22-2013, 12:34 PM
  2. Searching for same data in a worksheet and copying answer into another cell
    By armen323 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-27-2013, 05:01 AM
  3. Searching data through out the worksheet
    By Munni in forum Excel General
    Replies: 2
    Last Post: 04-07-2011, 05:06 AM
  4. [SOLVED] Searching and Retrieving data from a Worksheet
    By Larry Snyder in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-01-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1