Help with maths

[AlexanderBB]

My maths is lousy and I can't quite work this out!
If each line in a comment starts at position
51
98
145
213
How can I calculate each lines length ?

I got the start position with

For f = 1 To Len(comment.text)
 If Mid(comment.text, f, 1) = vbLf Then Debug.Print f + 1
Next

 

[Alex Blakenburg]

This might be a better option for you.

Sub LineLength()

Dim splitComment As Variant
Dim f As Long

splitComment = Split(Comment.Text, vbLf)

For f = LBound(splitComment) To UBound(splitComment)
    Debug.Print f, Len(splitComment(f))
Next i

 

[AlexanderBB]

Yes, thanks but I also need to know the starting position of each line in the comment'
I thought there may be a formula, once having the length of each row.

 

[Alex Blakenburg]

It would be easier if we knew how you are trying to use it.
You can do formula but you also need the length of the full string and the formula for the last item is slightly different to the other lines since your start position includes the previous vbLF

Book1
	A	B	C	D
1	Start	Line Feed Position	Line End of Text	Line Length Excl vbLF
2	1	50	49	49
3	51	97	96	46
4	98	144	143	46
5	145	212	211	67
6	213	300	299	87
7	300	Length End of String
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=IF(N(B3)=0,A3,A3-1)
C2:C6	C2	=B2-1
D2:D6	D2	=C2-A2+1

 

[AlexanderBB]

I'm changing the attributes (bold, non-bold) and colors of certain rows in a comment.
Using .characters(start, len). I'm splitting the comment on vblf and applying it to the rows that contain a colon.
Thanks for your example I'll try converting it to VBA.
PS I thought this would be easy but couldn't crack it!

 

[Alex Blakenburg]

Does this help:

Sub LineFormatting()

    Dim sComment As String
    Dim f As Long
    Dim sLine As String
    Dim StartPos As Long, EndPos As Long
    
    sComment = Comment.Text

    StartPos = 1
    For f = 1 To Len(sComment)
        If Mid(sComment, f, 1) = vbLf Then
            EndPos = f - 1
            sLine = Mid(sComment, StartPos, EndPos - StartPos + 1)
            If InStr(1, sLine, ":") Then
                ' Do something
                Debug.Print sLine, Len(sLine)
            End If
            StartPos = f + 1                            ' Technically not required when f = full length
        End If
    Next f
End Sub

 

[AlexanderBB]

Yep! Thanks very much Alex. I should have been able to nail this but StartPos was the bit I just couldn't get, even though it must be 1 more than the VBLF
Here's the full procedure, all working, having confirmed first that comment text exists. CleanEnd removes any unwanted leading or trailing LF chars.

Sub LineFormatting(theComment As Range)
    Dim sComment As String
    Dim f As Long
    Dim sLine As String
    Dim StartPos As Long, EndPos As Long
    With theComment
            sComment = CleanEnd(.Comment.Text)
            StartPos = 1
            With .Comment
                With .Shape
                    With .TextFrame
                        For f = 1 To Len(sComment)
                            If Mid(sComment, f, 1) = vbLf Then
                                EndPos = f - 1
                                sLine = Mid(sComment, StartPos, EndPos - StartPos + 1)
                                If InStr(1, sLine, ":") Then
                                    .Characters(StartPos, Len(sLine)).Font.Bold = True
                                    .Characters(StartPos, Len(sLine)).Font.Color = RGB(128, 0, 0) 'Brown
                                End If
                                StartPos = f + 1                            ' Technically not required when f = full length
                            End If
                        Next f
                    End With
                End With
            End With
    End With
End Sub

 

[Alex Blakenburg]

Thanks for sharing your final code. Glad I could help.