Results 1 to 8 of 8

Thread: Marking keywords in big string data, problem

  1. #1

    Marking keywords in big string data, problem



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Excel VBA People!

    This is my first post, and it's first time Im looking for help officialy on this forum. I've been reading many good posts here before, and thank's to all of you.

    I have Workbook and inside realy specific column A, every cell contains a lot of string data (news, TV transcript etc...). Workbook can contain 1000 2000 rows, maybe 10.000, no matter, no limit....
    I wrote VBA code for marking specific keywords Array (1000)) which means 'Green color', 'Bold' and 'Underline', and everything working fine, but after that command (Run VBA code), program (Excel), not working properly, actually, working, but slowly. What I need is next option, for other operators in my team: simply F2 (edit cell - column A) and quickly view of marking words.
    What can be problem, some format options, or something else (full clipboard)?
    How to make Excel to work same as it does before running VBA and marking keywords?

    Random exampe is in attach...
    Attached Files Attached Files

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,061
    Articles
    0
    Excel Version
    365
    I copied the cells with text in, in your file to a new sheet, added similar highlighting of words, and although it slowed Excel up quite a bit, it was nowhere near as slow as your sheet.
    2 Points:
    1. Excel isn't really a word processor
    2. What is the code that places the higlights (markings) of words - perhaps it can be simplified?

  3. #3
    Yes, I've tried the same trick, copy to new sheet/wb, formating column A as text or other options, but problem is still here.
    I know, it's pretty unusual to use excel for text analysis but it works great for me and my team except this.
    VBA code is not too much complicated, I've been used Search function:
    .......
    KWordPos = Application.WorksheetFunction.Search(keyWords(j), Cell.Value, i)

    On Error GoTo 0
    If KWordPos > 0 Then
    Cell.Characters(Start:=KWordPos, Length:=textLen).Font.Color = RGB(51, 204, 51)
    Cell.Characters(Start:=KWordPos, Length:=textLen).Font.Bold = True
    Cell.Characters(Start:=KWordPos, Length:=textLen).Font.Underline = True
    ......
    etc...

    I'm thinking about run marking keywords only if operator select cell in column A
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    runMarkingKeyWords
    End Sub
    and if selection is anywhere in the sheet (not in Column 1 (A), run other "undo Sub" - unmark - that could be solution (If there is no other option).

  4. #4
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    Use:

    Code:
    Sub M_snb()
       Columns(1).WrapText = True
    End Sub

  5. #5
    Good idea, but after Wrap text, content in Column A becoming unreadable, actually, it's not in good direction anymore, so F2 (quick look) loosing sense.

  6. #6
    Conjurer snb's Avatar
    Join Date
    May 2013
    Posts
    376
    Articles
    0
    Excel Version
    2020
    In that case you use another file than you uploaded here.

  7. #7
    Quote Originally Posted by snb View Post
    In that case you use another file than you uploaded here.
    No, file is the same. (I cannot post print screen yet, sorry... )
    Text in column A is marked, but after F2, it's oriented in vertical alignment, not horizontal. I need better solution.

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    2,061
    Articles
    0
    Excel Version
    365
    Your file doesn't do that here.

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
  •