+ Reply to Thread
Results 1 to 6 of 6

Case When Error with certain values

  1. #1
    Registered User
    Join Date
    10-21-2014
    Location
    Canada
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Unhappy Case When Error with certain values

    HI!!!

    I'm having an unusual problem... I'm trying to look through a number of different columns for a certain value. If that value is there, then I want to copy over a certain range of cells into a new workbook. Some columns might contain certain values while other columns don't, and vice versa.

    I have discovered the root of my problem. If the value (for example, 32) is NOT in the first column being searched, the rest of my case statement don't work, and I get the error "Run-time error '1004': Application-defined or object-defined error". See a sample of my code below (I have 20 cases in total, and several lines of code before/after this; it's just a sample)

    Please Login or Register  to view this content.
    If I set the criteria to a number that IS in the 1st column (example, 24) then it works.

    I'm really stuck, any thoughts would be helpful!

    Thanks.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Case When Error with certain values

    Hello Johnatha,

    In a Select Case statement, the Case statements that follow compare the value that follows the Select Case statement above.

    Please Login or Register  to view this content.
    Case 1, Case 2, etc are looking for numbers. These are not sequence numbers for each Case statement.

  3. #3
    Registered User
    Join Date
    10-21-2014
    Location
    Canada
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Case When Error with certain values

    Hi Leith Ross,

    Previous code that I should've included:

    Please Login or Register  to view this content.
    I understand what you're saying, but my problem is not with the case statement itseld. It's the fact that it's searching for values that aren't in some columns (I think).

    Thanks!

  4. #4
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Case When Error with certain values

    Hello Johnatha,

    Ah, the missing piece of the puzzle. Have you turned off the autofilter before the Select Case is executed?

  5. #5
    Registered User
    Join Date
    10-21-2014
    Location
    Canada
    MS-Off Ver
    MS Office 2010
    Posts
    28

    Re: Case When Error with certain values

    Hi Leith Ross!

    Yes, at the beginning of each Case statement, I start off with ".AutoFilterMode = False" to turn it off. Any way I can say like "if cells null, don't copy"?

    Sorry, I have no clue what to do!

    Thanks

  6. #6
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Case When Error with certain values

    Hello Johnatha,

    You include skipping blanks in your filter by adding an "AND" operator and Criteria2 =
    Please Login or Register  to view this content.

+ 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. [SOLVED] Case inside a Case throws error 424
    By Jon.R in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-08-2013, 12:36 AM
  2. Case Select compile error "Case without case select"
    By coasterman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-23-2012, 06:50 AM
  3. In case Run-time error '13' do this...
    By Gabor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-29-2007, 03:02 PM
  4. Case without select case error
    By Tano in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-25-2007, 10:27 AM
  5. Program vb to do something in case of an error.
    By bjwoodruff in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-04-2005, 11:05 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