Problem with formula
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Problem with formula

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    First off thanks to Al Chara, you code helped allot in getting me this far, but here is my problem

    I can't get the follow code to work right:
    strfile = Source
    SheetName = Workbooks(Source).Worksheets(h).Name
    ActiveCell.FormulaR1C1 = "='" & strfile & "'!R21C4"

    it either puts the sheet numer in or just the file refering to the first work sheet. I can get one or the other but not both. gets frustrating to be so close to a goal.

    Can anyone tell how to make this look at the file source and the sheet number coresspoding to h. any help is much apreciated.

    Also at the end of the loop I want to close the sile souce, source is actually a varible. just a referece to a cell in the workbook. can't seem toget any of the functions to recognize it to close. I am a bit confused.

    Again any help is much appreciated
    Paul

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Hi kluitna,

    I believe the problem is that the formula reference your code builds does not include the worksheet. It should be:

    ActiveCell.FormulaR1C1 = "=[" & strfile & "]" & SheetName & "!R21C4"

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com