Ask Experts Questions for FREE Help !
Ask
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
    		  
    #1

    Dec 21, 2010, 03:25 PM
    VBA Issue
    Ok, so here's what I am trying to do. I am trying to use a variable sheet name as a Named Range in my procedure but it is not working.

    Here's the part of my code that is failing:

    ActiveWorkbook.Names.Add Name:=NameRange1, RefersToR1C1:= _
    "=OFFSET(DataSheet1!R1C1,0,0,COUNTA(DataSheet1!C1) ,COUNTA(DataSheet1!R1))"
    ActiveWorkbook.Names(NameRange1).Comment = ""


    I have declared DataSheet1 as a String that reads like this:

    date_1 = Format(Date - 1, "mm.dd.yy")
    DataSheet1 = date_1 & " Data"


    I've tried to put single quotes around DataSheet1 in the Offset formula but that fails as well:

    ActiveWorkbook.Names.Add Name:=NameRange1, RefersToR1C1:= _
    "=OFFSET('DataSheet1'!R1C1,0,0,COUNTA('DataSheet1' !C1),COUNTA('DataSheet1'!R1))"
    ActiveWorkbook.Names(NameRange1).Comment = ""

    Any ideas?
    JBeaucaire's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
    		  
    #2

    Dec 21, 2010, 08:22 PM

    If the DataSheet1 part of the string is separate vba variable, you must reference it separately and concatenate it into the string you're building. You'll definitely need those quote in there, too.

    RefersToR1C1:= "=OFFSET('" & DataSheet1 & "'!R1C1, 0, 0, COUNTA('" & DataSheet1 & "'!C1), COUNTA('" & DataSheet1 & "'!R1))"


    NOTE:

    DataSheet1 = Format(Date - 1, "mm.dd.yy") & " Data"
    jakester's Avatar
    jakester Posts: 582, Reputation: 165
    Senior Member
    		  
    #3

    Dec 22, 2010, 09:39 AM
    Quote Originally Posted by JBeaucaire View Post
    If the DataSheet1 part of the string is separate vba variable, you must reference it separately and concatenate it into the string you're building. You'll definitely need those quote in there, too.

    RefersToR1C1:= "=OFFSET('" & DataSheet1 & "'!R1C1, 0, 0, COUNTA('" & DataSheet1 & "'!C1), COUNTA('" & DataSheet1 & "'!R1))"


    NOTE:

    DataSheet1 = Format(Date - 1, "mm.dd.yy") & " Data"
    Couldn't you leave you a greenie! Thx, JB, that was exactly what I needed and it worked perfectly.
Not your question? Ask your question View similar questions
 

Question Tools Search this Question
Search this Question:

Advanced Search


Check out some similar questions!

Excel vba [ 1 Answers ]

I am working on an excel sheet vba, I wanted to un hide the cells with a click on one cell above it without using the command button

VBA code [ 2 Answers ]

Once the month ends, cell "A1" must tick over to zero, where "A1" is used by me as a counter to show how much has been deliverd to date for the month.

Counter in VBA [ 19 Answers ]

I have an Excel spreadsheet witch I use for my wage calculations. Annually I have to submit a report to government showing total amount earned by each worker as well as total income tax deducted. Wages are calculated and paid fortnightly. I have a counter like this example,” Mycount =...

Hiding PivotItems using VBA [ 2 Answers ]

I have built a simple macro using VBA which hides certain dates from view in my pivot table. For example, I have date values like: 1/1/2007, 1/5/2007, 2/15/2007, 5/1/2008, 2/1/2009. I've been able create my macro to hide dates that I specify in the below code: Sub Macro1() With...

View more questions Search