Consulting

Results 1 to 4 of 4

Thread: Macro to Look for Specific text in other workbook and report value in cell beneath it

  1. #1

    Macro to Look for Specific text in other workbook and report value in cell beneath it

    Hi everyone,

    Can someone help me develop a VBA code that when executed will look for the word "Total" in another workbook and report whatever number in the cell beneath it to the main workbook? For example, I have a master spreadsheet that has the macros and another workbook that has a quote to a customer. When I click on the VBA code button, I want it to look for the word "Total" in the quote spreadsheet and report the number value beneath it to the master spreadsheet. Any idea how I can do this using VBA, please?

    Thank you!

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    When the Main (xlsm) book is open, is the Totals book also open?
    If not: Is it always in the same folder relative to the Main's path?
    Is the Totals book an xlsm, xlsb, or xlsx File?
    Will there be more than one Totals book?
    If more than one, will they all be in the same folder?
    If many Books, is the Totals sheet always named the same?

    Is the "Totals" Cell always in the same Column? I'm assuming it is not always in the same Row.
    If not, is the Header of the "Totals" Column always the same String and in the same Row?
    ...If so, which Row and what is the Header String?
    Is the "Totals" Cell always next to last in the Column with the total Value the last cell?

    Personally, when developing a Spreadsheet, I always place the sums at the top of columns, below the Header, so they are always in the same Row, regardless of the depth of the columns.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Quote Originally Posted by SamT View Post
    When the Main (xlsm) book is open, is the Totals book also open?
    If not: Is it always in the same folder relative to the Main's path?
    Is the Totals book an xlsm, xlsb, or xlsx File?
    Will there be more than one Totals book?
    If more than one, will they all be in the same folder?
    If many Books, is the Totals sheet always named the same?

    Is the "Totals" Cell always in the same Column? I'm assuming it is not always in the same Row.
    If not, is the Header of the "Totals" Column always the same String and in the same Row?
    ...If so, which Row and what is the Header String?
    Is the "Totals" Cell always next to last in the Column with the total Value the last cell?

    Personally, when developing a Spreadsheet, I always place the sums at the top of columns, below the Header, so they are always in the same Row, regardless of the depth of the columns.
    Hello, thanks for your reply. The quote spreadsheet is an .xlsx file that has many information. All I want is for the master spreadsheet (.xlsm) is to look for a cell that contains the word "Total" in the quote spreadsheet and report the value in the cell beneath it to the master spreadsheet. The cell that contains the word "Total" is always different depending on how big is the quote spreadsheet but the value that I'm interested in is always in the cell beneath it. I always open the quote spreadsheet to review it before reporting any value to the master spreadsheet, so it's always open. Also, let's just assume I have one quote spreadsheet in the same folder as the master spreadsheet. Let's keep it simple. Hope this explains my situation.

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,704
    Location
    Let's keep it simple.
    Yeah. That's why I asked the questions I did. You know? The ones you ignored.

    I see what you are trying to accomplish. I see that you already know the solution, and just want someone else to implement your solution.

    There are #42# ways to accomplish what you need, your "Solution" is one, not necessarily the best.
    MyTotal = Workbooks(Book Name here).Sheets(sheet Name here).Cells.Find("Total").Offset(1, 0)
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

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