+ Reply to Thread
Results 1 to 5 of 5

Declare text to find in 2 workbooks

  1. #1
    Registered User
    Join Date
    05-28-2015
    Location
    Houston, TX
    MS-Off Ver
    2010
    Posts
    7

    Declare text to find in 2 workbooks

    Hello all,

    I have two workbooks. "Copy" is where I am organizing all of my data. "2014 Metric Summary Report" is a system output... and it's a mess. My goal is to have the macro find the asset (Listed in column A in both workbooks, but not the same order) and copy data from one workbook to the next for each asset.

    The debugger stops me at: Txt = Workbooks("WB1").Sheets("33300.20.5392877").Cell(i, 1).Value saying 'Subscript out of range.' Here is where I need the macro to somehow take the text in the cell and find that text in the second workbook.

    Please Login or Register  to view this content.
    Any thoughts? Thanks for your time!

    P.S. Also, how could I qualify the code so that if the text is not found, it just goes onto the next i?
    Last edited by imaginekat; 08-05-2015 at 10:41 AM.

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,171

    Re: Declare text to find in 2 workbooks

    I cant see where the activecell is.
    you could get an error depending....define its start

    range("A1").select
    For i = 3 To 31

    or
    go there in the loop:
    For i = 3 To 31
    Workbooks("WB1").Sheets("33300.20.5392877").Cell(i, 1).select
    Txt = activecell.Value

    (it could also be the wrong workbook)

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Declare text to find in 2 workbooks

    You have WB1 declared as a variable.
    Please Login or Register  to view this content.
    Then you are using it with quotes around it.
    Please Login or Register  to view this content.
    I think you need to lose the quotes.
    Please Login or Register  to view this content.
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  4. #4
    Registered User
    Join Date
    05-28-2015
    Location
    Houston, TX
    MS-Off Ver
    2010
    Posts
    7

    Re: Declare text to find in 2 workbooks

    Whoops! Silly mistakes! Thank you very much for your help!

  5. #5
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Declare text to find in 2 workbooks

    Thanks for the rep. points.

    Please click the Thread Tools drop down box above your first post and choose solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. loop through workbooks to find term, find not working
    By bsapaka in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-14-2015, 06:16 PM
  2. Replies: 9
    Last Post: 12-21-2014, 06:29 PM
  3. Find Text String across Multiple Workbooks
    By craigos in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2013, 05:18 AM
  4. I can't declare a paragraph as non-script text
    By dschmitt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2012, 10:22 PM
  5. Can't declare FinalRow
    By JapanDave in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2011, 02:02 AM
  6. Why declare variables
    By tryer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-17-2009, 12:03 PM
  7. Declare Multiple Variables for any Open Workbooks
    By Bravurian17 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-14-2009, 11:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1