0

I am write an excel VBA code to italicize part of string, using find and find next, Instr, and characters objects. It runs perfect for the first 12 line, and all the lines below - full strings are italicized. I have checked my ranges and looks they are all fine.

Any idea what has caused this? Thanks.

The following is the code:

Option Explicit

Dim MySearch As Variant
Dim SearchLine As Range
Dim SearchResult As Range
Dim FirstAddress As String
Dim i As Long

Sub Copy_Column_U()

Worksheets("Reading List").Activate

Range("Full_Reference_In_Harvard_Format_Formula").Copy
Range("Full_Reference_In_Harvard_Format").PasteSpecial xlPasteValues

Application.CutCopyMode = False

End Sub

Sub Format_Harvard_Reference()

ThisWorkbook.Worksheets("Reading List").Activate

Call Copy_Column_U

Range("S5").Select
MySearch = ActiveCell.Value
Set SearchLine = Range("Full_Reference_In_Harvard_Format")

For Each MySearch In Range("Journal_Title")
    Set SearchResult = SearchLine.Find(What:=MySearch, MatchCase:=False, LookAt:=xlPart)

    If Not SearchResult Is Nothing Then
        FirstAddress = SearchResult.Address
        Do
            i = InStr(1, SearchResult, MySearch, vbTextCompare)
            SearchResult.Characters(i, Len(MySearch)).Font.Italic = True
            Set SearchResult = SearchLine.FindNext(SearchResult)
        Loop While Not SearchResult Is Nothing And SearchResult.Address <> FirstAddress

    End If

    ActiveCell.Offset(1, 0).Select
Next MySearch

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
Vivian
  • 1
  • 1
  • Difficult to know without seeing any data – CallumDA Mar 18 '18 at 13:47
  • You'd use `.characters`. Here's a hint: https://www.excel-pratique.com/en/vba_tricks/format_characters_in_a_cell.php and https://stackoverflow.com/q/2192730/8112776 – ashleedawg Mar 18 '18 at 15:41
  • @ashleedawg, she is already using `characters`... – DisplayName Mar 18 '18 at 15:57
  • @Disp - not correctly, apparently :) – ashleedawg Mar 18 '18 at 16:03
  • @ashleedawg, to me she's using it correctly. And I don't even see the question as a duplicate... – DisplayName Mar 18 '18 at 16:07
  • @Jeeped, I can't see why this question is a duplicate of the one you linked. While it certainly lacks data/examples to work on – DisplayName Mar 18 '18 at 16:14
  • The first thing to consider is the first thing that everyone sees on this question (and will for eternity if the question is left open, unedited): the title. It pretty clearly states what the issue is. Others shouldn't have to analyze the question or read between the lines to determine what's "actually" being asked. – ashleedawg Mar 18 '18 at 16:14
  • It can still be re-opened if it's edited to follow the site guidelines and tips that are readily available. Example: [mcve] – ashleedawg Mar 18 '18 at 16:18
  • @ashleedawg, it's a rude and ugly world – DisplayName Mar 18 '18 at 16:38
  • My original data is very long. I have tried my above code on simpler data, and it worked fine. Possibly the data in the MySearch range don't tally with the SearchLine data... Still I am puzzled. – Vivian Mar 18 '18 at 16:44
  • @Vivian, you may want to try and edit the question title so as not to have people think its content is a duplicate of another one .. And, more important to me, add data examples (screenshots may do) – DisplayName Mar 18 '18 at 16:51
  • I found the reason. The original data is italicised in excel. After I put it straight then the code is running perfect. Thanks! Enjoy the weekend! – Vivian Mar 18 '18 at 16:55
  • @DisplayName, thanks a lot. Here is the simple data: – Vivian Mar 18 '18 at 16:56
  • ToBeItalicised EndResult Tom Tom is not at home. Andy Andy is not at home. Alice Alice is not at home. – Vivian Mar 18 '18 at 17:00
  • @Vivian, glad you solved it. I couldn't actually see any flaw in your code, that's why I asked for a screenshot! Enjoy (what is last of) the weekend, too! – DisplayName Mar 18 '18 at 17:02
  • @Jeeped, I'd be glad you could take your time and answer my question. thanks – DisplayName Mar 18 '18 at 17:02
  • @DisplayName - thanks! Sorry I do not know how to post the screenshot here. Basically the code works if you want to find the string of column A in column B and italicise it in column B. Cheers! – Vivian Mar 18 '18 at 17:05
  • @Vivian, I got that from the beginning. What I didn't have was the actual look of your data. But now you explained it: some of them were already completely italicized from the start, and that was the issue – DisplayName Mar 18 '18 at 17:08
  • @DisplayName, exactly. It looks excel formatting sometimes overwrite VBA formatting code... – Vivian Mar 18 '18 at 17:57
  • 1
    @ashleedawg Comment read. I've approved the robots.txt edit, but I'll leave the tag excerpt edit to others. Note that people reviewing will only see your last edit revision description (for tags, they always get merged even later than within 5 minutes), so the edit description is currently less clear than it was before. Sorry Vivian for squatting your question, I'll delete my comment soon after. – Cœur Mar 19 '18 at 02:58

0 Answers0