Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: PLEASE!

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can someone PLEASE tell me how to make reference to a cell in a closed workbook WITHOUT using the R1C1 reference format. I need to use Cells(x,y)so I can loop.

    It may just be a syntax thing but here is what I want:
    activecell.value = Workbooks("C:My Documentsemployee.xls).Worksheets(1).cell(x,y).value ----> Or something to this effect. I know the syntax is off but if ANYONE can help, I would greatly appreciate it.

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, here goes.

    I've had a look at this and your earlier post and I'm not sure why this has to be in a macro but I'm sure you have your reasons.

    First things first, the full formula that needs to go in cell A1 of the active workbook is:
    ='C:My Documents[Employees.xls]Sheet1'!$B1&" "&'C:My Documents[Employees.xls]Sheet1'!$C1

    As there is no dollar($) in front of the row reference, you could just copy and paste this formula down.

    If you wanted a macro to go through and fill each in for 75 rows then how about:

    Sub test ()

    For x = 1 To 75
    Range("A" & x).Formula = "='C:My Documents[Employees.xls]Sheet1'!B" & x & "&"" ""&'C:My Documents[Employees.xls]Sheet1'!C" & x & ""
    Next x

    End Sub

    Is this what you had in mind??

    Rgds
    AJ


    N.B. Where ever you see two backslashes above, only use one in your formulae/code (it's just the way this website works!)


    [ This Message was edited by: AJ on 2002-04-10 13:06 ]

  3. #3
    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

    Try the following:

    ActiveCell.Value = "= 'C:My Documents" & "[employee.xls]Sheet1'!" & Cells(1, 1).Address

    (Remember this board adds an extra slash mark)
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    AJ - Thanks for your response. The reason it has to be in a macro is because every week, the end users need to update their timesheets with the employee list. If someone has been deleted, they should bot appear in the next week. So the macro gets run 4 times in 4 weeks, pasting the records from the employee file, into the active workbook. I will try the code and hope it works. Thanks again.

Some videos you may like

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
  •