Furmula Question
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Furmula Question

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

    Default

     
    A quick question on setting up variables in a formula.

    I have the current formula:

    ActiveCell.FormulaR1C1 = "='" & strfile & "'!R21C4"

    I am using it to link several workbooks together. That have several worksheets in them.

    "strfile" is the workbook file name, This works just fine. My problem is I can't seem to get the formula on the next pass throughthe loop to look at the next worksheet in "strfile". Does anyone know the syntax to look at the same file and the next sheet in a file.?

    Any ideas are much appreciated.

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following code should help to get the worksheet names:

    Set STRFILE = ThisWorkbook
    For I = 1 To ThisWorkbook.Worksheets.Count
    MsgBox STRFILE.Worksheets(I).Name
    Next I

    If you need help to apply this to your situation, then post again.

    Hope this helps.
    Kind regards, Al.

    [ This Message was edited by: Al Chara on 2002-03-18 10:57 ]

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

    Default


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

    Default

    You have me on the right track Al. The problem is when I use the command Thisworkbook, it trys to look into the active workbook which is the one I am trying to link the data into.

    ActiveCell.FormulaR1C1 = "='[Est Source Item.xls]Estimate (1)'!R21C4"

    Above is the formula with the hard values in it for the given soucre file and sheet number. There is as many as 70 source files and each one has 30 estimates. I can loop though the source files with out a problem, but when but hav e not been able to successfuly right a loop inside the source file loop to go through each estimate.

    Hope this is clearer, it seems very simple but for some reason it eludes my even simpler mind.

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are the workbooks all open when you want to run this macro?

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

    Default

    Al, The only workbooks that are open is the one I am getting the the data from and the one I am liking to, Just had thought, the one I am gettinf the date from does not have to be open to establish the link. Or does it?


  7. #7
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does this help:

    Dim strfile As Workbook
    For h = 1 To Workbooks.Count
    Set strfile = Workbooks(h)
    For i = 1 To strfile.Worksheets.Count
    ActiveCell.Value = strfile.Worksheets(i).Range("d24")
    ActiveCell.Offset(1, 0).Select
    Next i
    Next h

    The code loops through all the open workbooks and their sheets and places cell D24 in the activecell.

    Kind regards, Al.

  8. #8
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Workbooks do not have to be open to read them, but remember a closed workbook can only return a value.

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

    Default

    Al, that codes works thanks. one question, how would i modiofy that same code if I did not want to have any workbooks open except for the one I am using. also the code you provide works well. My problem is closing all the given workbooks after the data has been extracted. I replaced your h loop with a dim called source wich is linked to a series of cells with file names. I have tried every in the help section to makethis work. I just get code errors,

    Again, thanks allot , been a big help. I have almost got this thing done.

  10. #10
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Did you read my response here:

    http://www.mrexcel.com/board/viewtop...2657&forum=2&1

    Question: If you don't have the workbooks open, then when and from where do you get the names of the workbooks?


    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

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