Referencing a cell using a cell from a different sheet
Referencing a cell using a cell from a different sheet
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Referencing a cell using a cell from a different sheet

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    The cells I am trying to reference are in a sheet called interface. In this example, i am trying to use values in cells E37 and E39 to reference Ax and Hy where x and y are the respective values of cells E37 and E39 in sheet "Interface"... Why doesn't this work or how can i make it work?

    Range("A" & [Interface!$E$37] & ":" & "H" & [Interface!$E$39]).Select

    Thanks!

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    Try it this way

    Range("A" & Sheets("Interface").Range("E37") & ":" _
    & "H" & Sheets("Interface").Range("E39")).Select

    It will work just so long as you have relavent values in E37 & E39 of the sheet Interface.



  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That still doesn't work

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    In answer as to why your code does not work, see this link from earlier tonite:
    http://www.mrexcel.com/board/viewtop...2503&forum=2&2

    In answer to your second question about how to make it work, try this modification of your code. It worked when I tested it:

    Sub SelectRangeTakeTwo()
    On Error GoTo e
    Range("I" & Sheet2.[E37] & ":" & "I" & Sheet2.[E39]).Select
    Exit Sub
    e:
    MsgBox "Place a valid numeral in E37 or E39" _
    & vbCrLf & "of your Interface worksheet.", _
    48, "Can't determine range"
    End Sub

    Very important, this will work only if your Interface sheet is VBA worksheet object #2 of your workbook. Press Alt+F11 to get into the Visual Basic Editor, and see the pane on the left entitled Project - VBAProject. If you don't see it, press Ctrl+R.

    Then, find your workbook name, in the syntax VBAProject (WorkbookName), double click to open it, and then open the folder titled "Microsoft Excel Objects". My guess is you will a sheet name, in the syntax Sheet#(Interface), where # is the sheet object number for your Interface sheet. If that number is not 2, then modify this code by plugging in whatever number that Interface sheet is.

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    kwaring, instead of just saying "It still doesn't work" how about helping us help you by:

    1 Telling us the values in the cells E37 and E39

    2. Tell us what the error is you are getting.

    As I intitially said:

    "It will work just so long as you have relavent values in E37 & E39 of the sheet Interface"



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-16 02:46 ]

  6. #6
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Dave, sorry, didn't mean to step on you there; I responded before looking for subsequent posts.

  7. #7
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom

    No no, not at all! Just very hard to help when the only response you get is "it still doesn't work" Maybe I should just respond with" Well it does for me"


  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That still doesn't work... i checked to make sure my interface sheet is sheet2, it isn't so i changed everything to sheet1 but there is still a problem when trying to run the code. Gives the following error:
    Select method of range class failed.

  9. #9
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It worked for me, please copy and post your code so we can fix what's ailing it.

  10. #10
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    lol... sorry about the "it still doesn't work", what can i say, it's very late! anyways i have tried many values for E37 and E39 all integers above 0. thanks for all the help!

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