+ Reply to Thread
Results 1 to 12 of 12

Add Worksheet Name to Range Variable

  1. #1
    Registered User
    Join Date
    11-07-2012
    Location
    United Stated
    MS-Off Ver
    Excel 2007
    Posts
    20

    Add Worksheet Name to Range Variable

    I have two worksheets that I'm working with. The first, "Raw_Data", contains the data and ranges that I need for a formula used in the second worksheet, "Cover Page". The following code successfully retrieves the range based on column header:

    Please Login or Register  to view this content.
    The problem I'm having is that I'm using the variable csCol (along with other variables not listed in the sample code) in the formula =countifs(csCol,"Open",lvl2Col,"International") that populates on the worksheet "Cover Page". Because the range does not include the "Raw_Data" worksheet name, the countifs formula does not work.

    So, I need assistance to include the worksheet name in the range address. Thanks in advance.

  2. #2
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Add Worksheet Name to Range Variable

    If you want to include the worksheet name to a range in a formula created by VBA then for example like this:
    Please Login or Register  to view this content.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES

  3. #3
    Registered User
    Join Date
    11-07-2012
    Location
    United Stated
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Add Worksheet Name to Range Variable

    Thanks for the reply. Using your example, I tried the code below but got a compile error due to the tick marks(I believe).

    Please Login or Register  to view this content.

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Add Worksheet Name to Range Variable

    Hi rzrbkpk

    I assume you're refering tp this line of Code
    Please Login or Register  to view this content.
    Try adding the . (dots)
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Registered User
    Join Date
    11-07-2012
    Location
    United Stated
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Add Worksheet Name to Range Variable

    Quote Originally Posted by jaslake View Post
    Hi rzrbkpk

    I assume you're refering tp this line of Code
    Please Login or Register  to view this content.
    Try adding the . (dots)
    Please Login or Register  to view this content.
    Adding the dots did function, but it produced the same results where the range address only includes the cell range and not the worksheet name.

  6. #6
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Add Worksheet Name to Range Variable

    If csCol and lvl2 are range objects and you are using them in a WorksheetFunction in VBA (instead of writing a formula into cell) then you do not need the sheet name. My example in post #2 was for writing a formula into a cell.

    with the assumption that "Open" and "International" are named ranges try:
    Please Login or Register  to view this content.
    and if they aren't:
    Please Login or Register  to view this content.
    Last edited by tehneXus; 05-30-2013 at 05:52 PM.

  7. #7
    Registered User
    Join Date
    11-07-2012
    Location
    United Stated
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Add Worksheet Name to Range Variable

    Quote Originally Posted by tehneXus View Post

    and if they aren't:
    Please Login or Register  to view this content.
    Your second assumption is correct. I used your suggestion, but get Run-time error '91': Object Variable or With block variable not set. This is the error that I have been getting. I assumed it was because of the range reference, because I had these variables .address value in the watch window and they return the cell range without the sheet name.

    Please Login or Register  to view this content.
    I may have made a wrong assumption and the error may be something else.
    Last edited by rzrbkpk; 05-30-2013 at 07:02 PM.

  8. #8
    Registered User
    Join Date
    11-07-2012
    Location
    United Stated
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Add Worksheet Name to Range Variable

    So I made a little progress with this. I changed the first part of the code to get the variable correct by making it a string:

    Please Login or Register  to view this content.
    So csCol now resolves to "Raw_Data!$Y$1:$Y$1424" (same for the variable lvl2). However, I get a 'Type Mismatch' error in the Countifs formula:

    Please Login or Register  to view this content.
    I'm assuming it's a type mismatch because the formula is expecting a range and I'm providing a string. Thoughts?
    Last edited by rzrbkpk; 06-01-2013 at 01:26 PM.

  9. #9
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Add Worksheet Name to Range Variable

    Could you please post the whole Sub ?

  10. #10
    Registered User
    Join Date
    11-07-2012
    Location
    United Stated
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Add Worksheet Name to Range Variable

    This is the shortened version that I'm testing with so that I don't have to walk through the entire code. I just removed other loops for column headers and additional Countifs formula. I say that so that others reading this will not think that I'm putting too much effort into a simple formula. At any rate:

    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Add Worksheet Name to Range Variable

    See code and test data here: CountIFS_TEst.xlsm

  12. #12
    Registered User
    Join Date
    11-07-2012
    Location
    United Stated
    MS-Off Ver
    Excel 2007
    Posts
    20

    Re: Add Worksheet Name to Range Variable

    Thank you very much TehneXus. That worked. The issue was not including "application" with worksheetfunction.countifs. The code works now! Strange thing is when I update the larger part of my code, it returns the accurate values sometimes and then other times returns 0. I had two co-workers walk through the code too and we're all scratching our heads. Oh well, different problem, different day...different post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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