+ Reply to Thread
Results 1 to 5 of 5

Getting Error Method range of object worksheet failed when trying to use Countifs function

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,669

    Getting Error Method range of object worksheet failed when trying to use Countifs function

    hi,

    Below are two examples of how i am using countifs in excel vba.

    THe first one works......no issues:
    junk1 = Application.WorksheetFunction.CountIfs( _
            ws_ecrfs.Range(alphacol(ecrf_L1Backlog_col) & 1 & ":" & _
                    alphacol(ecrf_L1Backlog_col) & lst_row_wsecrfs), "Yes", _
            ws_ecrfs.Range(alphacol(ecrf_DaysOutstanding_col) & 1 & ":" & _
                    alphacol(ecrf_DaysOutstanding_col) & lst_row_wsecrfs), ">" & PageEntryTAT)
    However, for some reason the following produces a method range of object worksheet failed error and i can not figure out why

    junk4 = Application.WorksheetFunction.CountIfs( _
            ws_queries.Range(alphacol(queries_qrystatus_col) & 1 & ":" & _
                            alphacol(queries_qrystatus_col) & lst_row_wsqueries), "Answered", _
            ws_queries.Range(alphacol(queries_L1Backlog_col) & 1 & ":" & _
                            alphacol(queries_L1Backlog_col) & lst_row_wsqueriesd), "Yes")

    Note: i did define the worksheets as follows:
    Dim ws_ecrfs As Worksheet
    Dim ws_queries As Worksheet
    'set link to outstandings worksheet
    Set ws_queries = Workbooks(fl_output).Worksheets(ShtOut_OutStandingQueries)
    'set link to ecrfs worksheet
    Set ws_ecrfs = Workbooks(fl_output).Worksheets(ShtOut_OutstandingeCRfs)

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Getting Error Method range of object worksheet failed when trying to use Countifs func

    What's being returned by alphacol in the lines you get the error?

    By the way, why not use Cells or a combination of Range/Cells instead of Range?

    Then you wouldn't need to convert from column number to column letter.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,669

    Re: Getting Error Method range of object worksheet failed when trying to use Countifs func

    alphacol is a function i created which returns the alpha character for a given number
    for example........alphacol(1) represents "A"

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Getting Error Method range of object worksheet failed when trying to use Countifs func

    I kind of guessed what alphacol does.

    Knowing what it's returning in the code where the error is might help in finding out what the problem.

    By the way, this is how you could just use Cells.

    With ws_queries
        junk4 = Application.WorksheetFunction.CountIfs( _
                .Cells(1,queries_qrystatus_col).Resize(lst_row_wsqueries), "Answered", _
                .Cells(1,queries_L1Backlog_col).Resize(lst_row_wsqueries), "Yes")
    End With

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,669

    Re: Getting Error Method range of object worksheet failed when trying to use Countifs func

    thanks norie........i take a look at 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] VBA | Advanced Filter | Run time error 1004 method range of object worksheet failed
    By williakm1013 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-05-2013, 08:30 PM
  2. [SOLVED] Error 1004 - Method Range of Object Worksheet Failed
    By mrbickelsworth in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2013, 02:18 PM
  3. [SOLVED] Getting Error - Method Range of Object - Worksheet Failed - Help!
    By missit in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-19-2013, 05:38 PM
  4. Sorting Worksheet result in Error method 'Range' of object"_global' failed
    By Silverboyke in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-18-2012, 05:23 PM
  5. Range Question / error 1004: method Range of object Worksheet has failed
    By Paul in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-07-2005, 10:06 AM

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