+ 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 - RIP 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,464

    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

    RIP - d. 06/10/2022

    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