Need Find in Open Workbook Code Debugged. HELP
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Need Find in Open Workbook Code Debugged. HELP

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

    Default

     
    I am trying to do a find in another open workbook and return the results to the active workbook. I get an error in the Set c= line. Can anyone debug this?

    Code:
    Sub GetItNow()
    Dim NextAvailableRow As String
    Dim firstaddress As String
    Dim fndName As String
    Dim adr As String
    Dim pLt As String
    Dim pRt As String
    Dim cLt As String
    Dim cRt As String
    Dim c As Object
    Range("A41:D65535").ClearContents
    NextAvailableRow = Range("A65536").End(xlUp).Offset(1, 0).Address
    fndName = Range("A2").Value & "-*"
    Set c = Workbook("Database").Worksheets("Circuit").Columns("A").Find(fndName, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstaddress = c.Address
    Do
    adr = c.Address
    pLt = Range(NextAvailableRow).Address
    pRt = Range(NextAvailableRow).Offset(0, 3).Address
    cLt = Range(adr).Address
    cRt = Range(adr).Offset(0, 3).Address
    Worksheets("Circuit").Range(pLt, pRt).Value = Workbook("Database.xls").Worksheets("Circuit").Range(cLt, cRt).Value
    NextAvailableRow = Range(NextAvailableRow).Offset(1, 0).Address
    Set c = Workbook("Database").Worksheets("Circuit").Columns("A").FindNext(c)
    Loop While Not c Is Nothing And c.Address <> firstaddress
    End If
    End Sub
    Thank you,
    Murph

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What does the error say?

    The only thing I can think of without knowing that is you might want to change the columns part of the "Set c" line to:

    Columns("A:A")

    Also, in your declarations, you can declare "c" as a "Range" instead of an object.

    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-04-18 07:04 ]

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    First change all instances of workbook to workbooks.
    Second Workbooks("Database").Worksheets("Circuit").Columns("A") must exist

    I did not create dummies of your workbooks to test out the results hopefully the above spelling correction will get you started

    Yours in EXCELent Frustration
    KniteMare

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

    Default

    Knight Mare,

    I used your changes and it worked beautifully.

    Thank you for all of your help,
    Murph

  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Keaau, Hawaii
    Posts
    238
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    np
    I've made that same error myself, Workbook instead of ...books.
    Of course I can, and it will take only 900 hours of programming time to do it."
    <MARQUEE></marquee>

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