Look a the value of the current cell on the other worksheet
Look a the value of the current cell on the other worksheet
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Look a the value of the current cell on the other worksheet

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have written a small macro that I want it to look the value of the Active cell from one workbook on a second workbook, but I am have no luck......This is what I have, if it leave my value just to say Active cell, when you switch windows, the value changes. But I can't seem to get it to work using the following command either. What am I doing wrong??

    exch = Windows("Rates").ActiveCell.Value
    Windows("GLEIS.DBF").Activate
    Range("A2").Select
    Do Until ActiveCell = (exch)
    ActiveCell.Offset(1, 0).Select
    Loop

  2. #2
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Would this do the trick?

    exch = Windows("Rates").ActiveCell.Address
    Windows("GLEIS.DBF").Activate
    Range(exch).Select


    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    When I run this it give me a runtime error 9: Subscript out of range. The debugger highlights the exch= line.

    Any Ideas?
    Russell

  4. #4
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I just copied and pasted your version without looking closely enough...you were using another workbook I supplied the code for a range call. The code below works for standard sheet names change as needed.

    Sub Test_Me()
    exch = Windows("Book1").ActiveCell.Address
    Windows("Book2").Activate
    Range(exch).Select
    End Sub

    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  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

    Ever thought of using the INDIRECT function?
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    exch = Windows("Rates").ActiveCell.Address
    Windows("GLEIS.DBF").Activate
    Range(exch).Select


    This is the formula I have, but it still gives me the same runtime error....I don't know what I am doing wrong....
    Russell

  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

    You need an extension on Rates:

    exch = Windows("Rates.xls").ActiveCell.Address

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

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Seattle Area, WA
    Posts
    212
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK GREAT!!!! Almost there....When I do this puts me at the same Address of the Rate.xls Active Cell, but what I am trying to accomplish is to search for the Value of the Active Cell on the Rate sheet say 950 which is located somewhere in Column A of the 2nd file. Any Ideas??

  9. #9
    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:
    With Workbooks("GLEIS.xls").Worksheets("Sheet1").Columns(1)
    Set c = .Find(Windows("Rates.xls").ActiveCell)
    End With
    Windows("GLEIS.xls").Activate
    ActiveSheet.Range(c.Address).Select

    replace GLEIS.xls with GLEIS.DBF or whatever.
    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