+ Reply to Thread
Results 1 to 4 of 4

Avoid Runtime error when converting to RANGE object

  1. #1
    Registered User
    Join Date
    02-09-2015
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    10

    Avoid Runtime error when converting to RANGE object

    I get a Runtime 1004 error, Method Range of object _Worksheet failed.
    The problem is that I loop through a an array of cells and retrieve their values and convert it to a Range
    Please Login or Register  to view this content.
    Problem is that when the value is not a valid for Range i.e the cell contains "hello" as opposed to "B12" or "B12:C14" I get the runtime error.
    I tried
    Please Login or Register  to view this content.
    I tried
    Please Login or Register  to view this content.
    both did not stop the error from appearing, is there anyway to test the value first to determine if the string is a valid argument for the Range() method?

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Avoid Runtime error when converting to RANGE object

    Where did you put the On Error Resume NExt?

    Could you place it in your code and post the rest please?

  3. #3
    Registered User
    Join Date
    02-09-2015
    Location
    New Jersey
    MS-Off Ver
    2010
    Posts
    10

    Re: Avoid Runtime error when converting to RANGE object

    Attached is the file I am playing with. the code is in the Sheet 1 module
    It is weird sometimes the "On Error Resume Next" works and others it doesn't
    The lookup cells are on the lookup sheet in column C
    try changing the value in the data sheet of any of the cells in E10:E20 it will throw the error but if I change the value in B7 (or any other cell on the data sheet) it will work but won't throw the error even though it is looping through the same values.


    Truth is if you change Cell Lookup!C1 to "Cell Name" it will throw the error when you change any cell on the data sheet

    Thanks
    Attached Files Attached Files
    Last edited by zfeld75; 09-24-2015 at 10:50 AM.

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Avoid Runtime error when converting to RANGE object

    Your On Error Resume Next is in the wrong place, it should be before:
    Please Login or Register  to view this content.
    Not after it

+ 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] Runtime error 1004: Method 'Range' of object '_Worksheet' failed
    By Phil Payne in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-17-2013, 01:30 AM
  2. Runtime error '1004' - Method 'Range' of object '_worksheet' falied
    By vijaykumarnachapalli in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-05-2013, 02:45 PM
  3. Runtime Error - Subscript out of Range / Object Error
    By JHRice in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2012, 05:14 PM
  4. [SOLVED] Runtime Error '91': Object variable... When Running in Range Selection
    By Stonesteel15 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 05-01-2012, 09:50 AM
  5. Runtime Error 1004 Method 'Range of Object'_Global' failed
    By popfan in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2011, 12:52 AM
  6. Runtime error .. 20028018: Method value of object range failed
    By petca059 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-11-2010, 04:40 AM
  7. 1004:runtime error method 'range' of object'_Global failed
    By trksbc in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2009, 03:25 AM
  8. Object range method runtime error
    By Neal Zimm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 10: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