Posts by Ed Nerd

  • [Solved] VBA: Entering formula with relative row ref?

    When I would activate the macro to enter the formula, that cell would be clicked on and activated. That cell reference would be showing in the top left area.


    One thing I tried was:


    Dim thisRow As Integer


    thisRow = ActiveCell.Row


    ActiveCell.Formula = "=LEFT(B" & thisRow & "& "".0.0."" ",FIND("$",SUBSTITUTE(B" & thisRow & "& "".0.0."",""."","$",3))-1)


    trying to pick up the Row reference and use it in the formula. But I keep getting errors - either I've got parentesis in the wrong place, too many or not enough quotes, or Expected End of Statement.


    I was reluctant to post this code, because I didn't want someone to think I was stuck on making *that* work, when I don't know enough yet to know if that's even the right way to do it.


    If you can help at all, I would be very grateful.


    Ed

  • [Solved] VBA: Entering formula with relative row ref?

    I have a formula as follows:
    =LEFT(A2&".0.0.",FIND("$",SUBSTITUTE(A2&".0.0.",".","$",3))-1)


    This takes a long string of numbers in A formats into something meaningful to what I'm doing. Naturally, when I enter this in B2 and run it to the bottom, the cell reference changes and picks up the adjacent cell data al the way down.


    I'm trying to create a macro that will enter this in any cell and pick up the ActiveCell row reference. So when entering the formula in B2003, the cell reference would be "A & ActiveCellRow", and it would pick up the data in A2003.


    I've tried several ways, but get lost in parenthesis, quotes and
    double-quotes, and VBA Expected End of Statement errors.


    Any assistance is greatly appreciated.


    Ed

  • Word text search from within Excel?

    Andy, this is *AWESOME*!! Beats the heck out of what Iwas trying to do! (My best solution was to code the FileSearch in Word, put it in a document as a Doc_Open event, the hyperlink to that doc in my spreadsheet.)


    Three questions, if I may:


    (1) How do I make it search for "this" AND "that" AND "the other", rather than OR?


    (2) What does the Macro 1 in Module 2 do?


    (3) If I take this sheet, Select All, Copy, and Paste into my existing workbook, will all the code come with it? (I assume I would have to move the code modules from This Workbook into Sheet1.)


    Again, my most spectacular thanks! :tumble::spin:


    Ed

  • Word text search from within Excel?

    Thanks for replying, Andy.


    I'm working with engineering reports - we're up to about 7900 and growing. I've created a spreadsheet that tracks these reports. Using the results of queries to our database, I extract several parameters and list them in the columns of the spreadsheet to allow the engineers to filter for specific items with specific characteristics.


    I have also extracted the narrative portion of each of these reports, saved each as a separate Word document, and provided a link to the corresponding Word file in my spreadsheet.


    Often, though, the parameters I've included on my spreadsheet don't provide enough information to filter. For instance, a certain engineer may be concerned about a high failure rate on shafts made of a certain metal compound. To find that, he would have to filter the spreadsheet several different times to include all possible systems using that type of shaft, then read each narrative to see if it included his item of interest.


    It would be easier to go to Word and, using the Open>Tools>Find dialog box, search all the narratives for "shaft", "steel-graphite", "failed"; that returns all the documents of interest regardless of the item parameters I've included - or not - on my spreadsheet.


    But most of these people are not Word-savvy, not to the point of using that type of search. I'd like to provide it to them in a macro from within my Excel spreadsheet, since that's what they'll have open.


    I thought of one possible way:
    -- Set up certain cells in which the engineers would enter their search terms
    -- Create a command button that would dim those cell values as strings and open a Word document
    -- The Word doc would have an On_Open event File Search macro that would use my dimmed strings as search parameters.


    That would put them into Word with a list of all files meeting their criteria.


    Is this feasable? Is there a better way? If this is good, how do I get my strings dimmed in Excel across to the Word macro?


    All help is greatly appreciated.
    Ed

  • Word text search from within Excel?

    In my spreadsheet, I have links to many Word docs. There is often a need to
    search through the Word docs for those containing certain words or phrases.
    This is easily done from within Word. But can I call that Word function up
    from within Excel and use it? More specifically, can I "capture" this
    within my spreadsheet, and allow my users to access this search function,
    regardless of their individual Word and Excel settings? (Assume macros ON,
    Word and Excel 2000 on Win2000.)


    Thanks for any and all help.


    Ed