+ Reply to Thread
Results 1 to 7 of 7

Code quit working - will not format key text

  1. #1
    Registered User
    Join Date
    11-28-2013
    Location
    Texas
    MS-Off Ver
    365
    Posts
    4

    Code quit working - will not format key text

    Many thanks to those who manage this forum - you provide a great service. Here are the details of my issue:
    System: Mac OS X, Macbook pro, Office 2011
    Spreadsheet: 5 columns, variable rows but always a header row. Column C holds key words. Column E holds concatenated text that includes at least one instance of the key word in Column C.
    Objective: Search in Column E for the key word that is in Column C and format all instances of the key word only in Column E with bold, italic and underline.
    The sub below ran OK several times but quit working now and will not format the words.
    Any and all help would be greatly appreciated. Thank you.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Strepho63; 01-21-2015 at 10:52 AM. Reason: Rule violation and provide more info.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,673

    Re: Finding and formatting one word in a cell

    deleted 1/20: code tags applied by mod
    Last edited by protonLeah; 01-20-2015 at 03:30 PM.
    Ben Van Johnson

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,969

    Re: Finding and formatting one word in a cell

    Please take a few minutes to read through our rules, specifically the 1 regarding code tags. I have added them for you - this time
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,673

    Re: Code quit working - will not format key text

    The only problem I found is:
    PosF = InStr(PosT, Cells(i, 5), aWord, vnTextcompare)

    should be:
    vbTextCompare

    Otherwise the code ran without error (Windows machine).

  5. #5
    Registered User
    Join Date
    11-28-2013
    Location
    Texas
    MS-Off Ver
    365
    Posts
    4

    Re: Code quit working - will not format key text

    Thank you so much for your reply. I fixed the typo and the sub runs with no error - but it does not apply the formatting. It actually ran fine +/- 3 times before i made the forum post. I copied and pasted the code into a larger sub and after that it would not apply the formatting. I have closed/restarted Excel and rebooted the puter but no luck. Maybe something to do with the Mac version. Any ideas?

  6. #6
    Registered User
    Join Date
    11-28-2013
    Location
    Texas
    MS-Off Ver
    365
    Posts
    4

    Re: Code quit working - will not format key text

    No solution yet but i extracted the following code to act on a single selected cell. The code below successfully formats the key word (aWord) in the selected cell. BUT, this will not work in a loop like the code above. The code runs without error - also a "step through" shows no error. However, the "Characters" statement does not change the formatting. Also, I ran a different code that has a loop and does cleanup as a test and after running the cleanup the single cell code below would not work either. Any ideas about what might be happening in a loop that would do this? I read that the Characters method only works if the object supports rich text. Could the loop effect this? Thank you.

    Please Login or Register  to view this content.
    Last edited by Strepho63; 01-21-2015 at 10:47 AM.

  7. #7
    Registered User
    Join Date
    11-28-2013
    Location
    Texas
    MS-Off Ver
    365
    Posts
    4

    Re: Code quit working - will not format key text

    Resolved! Operator error! The first code above works OK. The problem was with another code that performed the concatenation and was supposed to copy-paste special-values. The seeming erratic behavior resulted because sometimes the above code was run on cells that had the formulas in them and sometimes the copy-paste special operation had been done manually. The Characters method will not work on cells that have formulas even though one is seeing text. I pray this might help someone avoid this long walk. Thank you.

+ 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. Macro to change colour of cell based on finding a specific word in the cell
    By Shelby761 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-24-2014, 09:46 AM
  2. Finding text to the right of a word in a cell.
    By Hyflex in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-11-2014, 12:47 PM
  3. Finding a specific word within cell
    By Vespasian in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 01-26-2013, 05:57 AM
  4. Finding certain word in cell....then split text
    By Lynx2x in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-22-2012, 08:09 AM
  5. Finding longest word in a cell
    By contaminated in forum Excel General
    Replies: 1
    Last Post: 06-10-2009, 05:15 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