VBA Array extract data

Question

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.

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

Not sure as to your main question about why Not is being weird. ...but does it work as you expect if you replace that with rng.Characters(iEnd, 1).Font.Italic = False? — BruceWayne
– BruceWayne, Commented Aug 10, 2016 at 16:27
@BruceWayne Yes even when I try setting it False it brings the same result. — johndoe253
– johndoe253, Commented Aug 10, 2016 at 16:30

Siddharth Rout · Accepted Answer · 2016-08-10 16:59:00Z

4

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

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

Change your code to

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

answered Aug 10, 2016 at 16:59

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

BruceWayne Over a year ago

So it wasn't the Not acting funky, but the first part? And I'm surprised you can't get a boolean value from .underline, kind of counter intuitive since you can with italics/bold

cyboashu Over a year ago

with only xlUnderlineStyleSingle in the loop condition, any entry without without underline will get skipped, whether or not its italic.

Siddharth Rout Over a year ago

@cyboashu: If my understanding is correct, OP wants to skip text which is not underlined

cyboashu Over a year ago

ah... OK,. he had first row in his expected data set, so i thought he is looking for w/o underlines too.:)

johndoe253 Over a year ago

Thanks for the help!

cyboashu · Accepted Answer · 2016-08-10 17:00:59Z

2

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))

answered Aug 10, 2016 at 17:00

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

1 Comment

johndoe253 Over a year ago

Thanks for the help!

user6432984 · Accepted Answer · 2016-08-10 18:01:29Z

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

Collectives™ on Stack Overflow

VBA Array extract data

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related