Find verse number break and bold/superscript

gailb
3StarLounger
Posts: 203
Joined: 09 May 2020, 14:00

Find verse number break and bold/superscript

Post by gailb »

At this point, I've pull in every bible verse within Excel so I can do searches for the scriptures I need versus going out to a website. I know, kind of rebuilding the wheel, but this is also giving me some practice in Excel building things. here's where I am stuck right now. I've using this code and it produces two outputs. Not sure which one I want right now, but they are options. One pulls each scripture with a line break between, and the second one is just a continuous verse by verse in one paragraph.

What I would like to do now is bold the first line and then bold/superscript all of the verse numbers. Also, as you can see from the 2nd picture and the red mark, how can I get rid of that leading space on the 2nd line? I know it's due to the Chr(10) in the last line, but not sure how to work around it.

Code: Select all

Sub PullOutScripture()

    Dim strRange    As String
    Dim strRange2   As String
    Dim rngcell     As range
    Dim numScp      As Long: numScp = Sheet1.range("J16") + Sheet1.range("K17")
    Dim myRow       As range
    Dim wsDest      As Worksheet: Set wsDest = Sheet5
    Dim wsSrc       As Worksheet: Set wsSrc = Sheet1
    
    With wsDest
        With .range("F2:F" & .Cells(.Rows.Count, "F").End(xlUp).Row)
            Set myRow = .Find(Sheet1.range("J17"), LookIn:=xlValues)
        End With
    End With
    
    For Each rngcell In wsDest.range("G" & myRow.Row).Resize(numScp)
        strRange = strRange & Chr(10) & rngcell.Offset(, -2) & " " & rngcell
        strRange2 = strRange2 & " " & rngcell.Offset(, -2) & " " & rngcell
    Next rngcell
    
    wsDest.range("I5").Value = wsSrc.range("I17") & strRange
    wsDest.range("I18").Value = wsSrc.range("I17") & Chr(10) & strRange2
    
End Sub
You do not have the required permissions to view the files attached to this post.

gailb
3StarLounger
Posts: 203
Joined: 09 May 2020, 14:00

Re: Find verse number break and bold/superscript

Post by gailb »

I happened to run across a post by Chip Peason and with a few mods, it seems to work out except for the first part. I haven't figured out a way to isolate the John 11:1-5 part. That's the part I would like to Bold and then this macro will take care of the rest.

John 11:1-5
1 Now a certain man was sick, Lazarus of Bethany, the village of Mary and her sister Martha.
2 It was the Mary who anointed the Lord with ointment, and wiped His feet with her hair, whose brother Lazarus was sick.
3 So the sisters sent word to Him, saying, "Lord, behold, he whom You love is sick."
4 But when Jesus heard this, He said, "This sickness is not to end in death, but for the glory of God, so that the Son of God may be glorified by it."
5 Now Jesus loved Martha and her sister and Lazarus.

Code: Select all

Sub ChipPearson()
    Dim c As String
    Dim r As range
    Dim n As Long
    Dim i As Long
    
    For i = 1 To 2
    
        If i = 1 Then Set r = range("M3") Else Set r = range("N3")
    
        For n = 1 To Len(r.Value)
            c = Mid(r.Value, n, 1)
            If c Like "[0-9]" Then
                With r.Characters(n, 1).Font
                    .Bold = True
                    .Superscript = True
                    .Size = 12
                End With
            End If
        Next n
        
    Next i
    
End Sub

gailb
3StarLounger
Posts: 203
Joined: 09 May 2020, 14:00

Re: Find verse number break and bold/superscript

Post by gailb »

As the saying goes, sometimes you just need to get up, walk away, and come back latter. Oh yeah, I guess brushing your teeth can help also.

Here's what I can up with. I add the x variable

Code: Select all

Sub ChipPearson()
    Dim c As String
    Dim r As range
    Dim n As Long
    Dim i As Long
    Dim x As Long: x = Len(Sheet1.range("I17"))
    
    For i = 1 To 2
    
        If i = 1 Then Set r = range("M3") Else Set r = range("N3")
        
        r.Characters(1, x).Font.Bold = True
    
        For n = 1 + x To Len(r.Value)
            c = Mid(r.Value, n, 1)
            If c Like "[0-9]" Then
                With r.Characters(n, 1).Font
                    .Bold = True
                    .Superscript = True
                    .Size = 12
                End With
            End If
        Next n
        
    Next i
    
End Sub

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 13284
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: Find verse number break and bold/superscript

Post by ChrisGreaves »

gailb wrote:
01 Jan 2022, 03:04
... I haven't figured out a way to isolate the John 11:1-5 part.
Gail, you might want to take a look at my strSplitAt function in my Excel example; this was one of ther first functions I wrote in Word97. It splits a string into two parts at a specified delimiter.

If your text is "<book><space><chapterverse><space><rest of text>" then your delimiter might be <space>, and two calls to strSplitAt will give you the leading part to be bolded, and the remainder is the trailing part not bolded.

If the chr$(10) is your delimiter, then it is a single split.

You can then use the LEN()gth of the leading part(s) to implement the formatting.

Cheers
Chris
If you think you’re too small to make a difference, try sleeping with a mosquito (Dalai Lama)

gailb
3StarLounger
Posts: 203
Joined: 09 May 2020, 14:00

Re: Find verse number break and bold/superscript

Post by gailb »

Thanks Chris and Happy New Year! Yes, I isolated it by using the Len()