Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Space in formula (excel xp)

    I'm writing a macro where i'm putting 2 cells that are separated by a space on sheet2.

    I can recreate it except for the space in between the 2 cell references.

    ActiveCell.FormulaR1C1 = "='shee1'!RC & "" "" & 'sheet1'!RC[1]"

    How can i write the above in a vba module? Thank you for the help.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Space in formula (excel xp)

    Like this:

    ActiveCell.FormulaR1C1 = "='sheet1'!RC & "" "" & 'sheet1'!RC[1]"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Space in formula (excel xp)

    ok, thank you. What if the 'sheet1' is a variable called shtName? I tried this and it doesn't work

    ActiveCell.FormulaR1C1 = "=shtName & "!RC & "" """ & shtName & "!RC[1]"

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Space in formula (excel xp)

    ActiveCell.FormulaR1C1 = "='" & shtname &"'!RC & "" "" & "'" & shtname & "'!RC[1]"

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Space in formula (excel xp)

    ok i used this

    shtname = "11mvs"


    ActiveCell.FormulaR1C1 = "='" & shtname &"'!RC & "" "" & "'" & shtname & "'!RC[1]"

    and get error 1004, application.defined or object error. am i missing a quote somewhere because when i cursor over the first shtname it says 11vs but when i cursor over the 2nd shtname it shows nothing. thanks

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Space in formula (excel xp)

    Try this:
    ActiveCell.FormulaR1C1 = "='" & shtname & "'!RC & "" "" & '"& shtname &"'!RC[1]"

    My mistake I had an extra dbl-quote in it.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •