+ Reply to Thread
Results 1 to 5 of 5

Adding new sheet's cell value to old formula

  1. #1
    Eric Silva
    Guest

    Adding new sheet's cell value to old formula

    I have a summary sheet and I want to add data sheets so that they reference
    into the summary sheet automatically. I made a command button that accesses
    a macro to create, name, and move the sheet where it needs to go. Now all I
    have to do is add the values from the new locations to the summary sheet's
    formula.

    Here's shat I tried:

    Range("C13").Select
    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 + " +
    '&sheetname'!R[5]C[3]"

    where sheetname is the string variable associated with the name of the new
    sheet. Problem is that excel won't accept a variable value for a sheet name.
    I know there's got to be a better way to do this. Any hints?

  2. #2
    Tom Ogilvy
    Guest

    Re: Adding new sheet's cell value to old formula

    Assume the new sheet name is Sheet10:
    Dim sheetname as String
    sheetname = "Sheet10"
    Range("C13").Select
    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & _
    " + '" & sheetname & '!R[5]C[3]"

    --
    Regards,
    Tom Ogilvy


    "Eric Silva" <[email protected]> wrote in message
    news:[email protected]...
    > I have a summary sheet and I want to add data sheets so that they

    reference
    > into the summary sheet automatically. I made a command button that

    accesses
    > a macro to create, name, and move the sheet where it needs to go. Now all

    I
    > have to do is add the values from the new locations to the summary sheet's
    > formula.
    >
    > Here's shat I tried:
    >
    > Range("C13").Select
    > ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 + " +
    > '&sheetname'!R[5]C[3]"
    >
    > where sheetname is the string variable associated with the name of the new
    > sheet. Problem is that excel won't accept a variable value for a sheet

    name.
    > I know there's got to be a better way to do this. Any hints?




  3. #3
    Eric Silva
    Guest

    Re: Adding new sheet's cell value to old formula

    At first, I copy-pasted the code into my program, then I compiled, got an
    error, read through it and saw only three quotes. I added a quote to the
    line right before the second apostrophe and got this:

    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & " + '" & sheetname &
    "'!R[5]C[3]"

    This gives me the 1004 error, so it's still not reading something in the
    second half of the statement as the correct object type to be going into an
    excel formula. What's still wrong?



  4. #4
    Eric Silva
    Guest

    Re: Adding new sheet's cell value to old formula

    I don't know what I did but it suddenly started working just right. Thanks a
    lot for the help, Tom. I read some of your other posts before I put this one
    up and you're both knowledgeable and helpful. Keep it up! I might have
    another question someday.

    "Eric Silva" wrote:

    > At first, I copy-pasted the code into my program, then I compiled, got an
    > error, read through it and saw only three quotes. I added a quote to the
    > line right before the second apostrophe and got this:
    >
    > ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & " + '" & sheetname &
    > "'!R[5]C[3]"
    >
    > This gives me the 1004 error, so it's still not reading something in the
    > second half of the statement as the correct object type to be going into an
    > excel formula. What's still wrong?
    >
    >


  5. #5
    Tom Ogilvy
    Guest

    Re: Adding new sheet's cell value to old formula

    Just for completeness. there was a typo

    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & _
    " + '" & sheetname & '!R[5]C[3]"

    should have been

    ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & _
    " + '" & sheetname & "'!R[5]C[3]"

    --
    Regards,
    Tom Ogilvy


    "Eric Silva" <[email protected]> wrote in message
    news:[email protected]...
    > I don't know what I did but it suddenly started working just right.

    Thanks a
    > lot for the help, Tom. I read some of your other posts before I put this

    one
    > up and you're both knowledgeable and helpful. Keep it up! I might have
    > another question someday.
    >
    > "Eric Silva" wrote:
    >
    > > At first, I copy-pasted the code into my program, then I compiled, got

    an
    > > error, read through it and saw only three quotes. I added a quote to

    the
    > > line right before the second apostrophe and got this:
    > >
    > > ActiveCell.FormulaR1C1 = ActiveCell.FormulaR1C1 & " + '" & sheetname &
    > > "'!R[5]C[3]"
    > >
    > > This gives me the 1004 error, so it's still not reading something in the
    > > second half of the statement as the correct object type to be going into

    an
    > > excel formula. What's still wrong?
    > >
    > >




+ 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