CODEWITHSUNDEEP
arraysvbaexcel
johndoe253
johndoe253

Reputation: 237

VBA Array extract data

When the "Not" in "And Not rng.Characters(iEnd, 1).Font.Italic" is left out the code is able to pull italicized and underlined words but when I place a "Not" in the code to pull out the underlined and not italicized words it doesn't work. I'm not exactly sure why the "Not" wouldnt work. The pictures below are the source sheet and the new sheet with the extracted data. Im able to use the code to get underlined and italicized but I'm adding the Not so I can get underlined but not italicized. The data that is supposed to come out is line 5 from the picture labeled 1.

enter image description here

enter image description here

Sub extract()        
    Dim dataRng As Range, cl As Range
    Dim marr As Variant
    
    Set dataRng = Worksheets("Sheet1").Range("C1:C10")
    
    With Worksheets("Sheet2")
        For Each cl In dataRng
            marr = GetUnderlines(cl)
            If IsArray(marr) Then .Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize(UBound(marr) + 1) = Application.Transpose(marr)
        Next
    End With
    
End Sub
Function GetUnderlines(rng As Range) As Variant
    Dim strng As String
    Dim iEnd As Long, iIni As Long, strngLen As Long

    strngLen = Len(rng.Value2)
    iIni = 1
    
    Do While iEnd <= strngLen
        Do While rng.Characters(iEnd, 1).Font.Underline And Not rng.Characters(iEnd, 1).Font.Italic
            If iEnd = strngLen Then Exit Do
            iEnd = iEnd + 1
        Loop
        If iEnd > iIni Then strng = strng & Mid(rng.Value2, iIni, iEnd - iIni) & "|"
        iEnd = iEnd + 1
        iIni = iEnd
    Loop
    
    If strng <> "" Then GetUnderlines = Split(Left(strng, Len(strng) - 1), "|")

End Function

Upvotes: 1

Views: 876

Answers (3)

user6432984
user6432984

Reputation:

Your code is truncating the last character.

Function GetUnderlines(rng As Range) As Variant
    Dim strng As String
    Dim iEnd As Long, iIni As Long, strngLen As Long

    strngLen = Len(rng.Value2)
    iIni = 1

    Do While iEnd <= strngLen
        Do While rng.Characters(iEnd, 1).Font.Underline = xlUnderlineStyleSingle And Not rng.Characters(iEnd, 1).Font.Italic And rng.Characters(iEnd, 1).Text <> " "
            iEnd = iEnd + 1
            If iEnd > strngLen Then Exit Do
        Loop

        If iEnd > iIni Then
            strng = strng & Mid(rng.Value2, iIni, iEnd - iIni) & "|"

        End If
        iEnd = iEnd + 1
        iIni = iEnd
    Loop

    If strng <> "" Then GetUnderlines = Split(Left(strng, Len(strng) - 1), "|")

End Function

Upvotes: 1

cyboashu
cyboashu

Reputation: 10433

Change while condition to this :

Underline is not boolean.

 Do While (rng.Characters(iEnd, 1).Font.Underline = -4142 Or _
                 (rng.Characters(iEnd, 1).Font.Underline = 2 And _
                 Not rng.Characters(iEnd, 1).Font.Italic))

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149277

.Font.Underline doesn't return a Boolean(True/False) ;) It returns an Long

If you add a Watch you can see it for yourself.

enter image description here

Change your code to 

Do While rng.Characters(iEnd, 1).Font.Underline = xlUnderlineStyleSingle _
And Not rng.Characters(iEnd, 1).Font.Italic

Upvotes: 4

Related Questions