Consulting

Results 1 to 11 of 11

Thread: Macro to reformat text not working - help

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Macro to reformat text not working - help

    Hi all, I have some text in a table in Word, which I want to get from this format:

    Domain1Fruits and Vegetables of the Day
    Sub-domain1A5Types of Fruits and Vegetables
    Task Statement1Eating fruits that determine whether you will be healthier.

    To this format:

    Domain: 1 - Fruits and Vegetables of the Day
    Sub-domain: 1A5 - Types of Fruits and Vegetables
    Task Statement: 1 - Eating fruits that determine whether you will be healthier.

    Syntax is effectively:
    Domain: # - DomainDesc
    Sub-domain: #XY - SubdomainDesc
    Task Statement: F - TaskStatementDesc

    Where '#' is going to be a Single-digit Number
    Where 'X' is going to be a Single Letter
    Where 'Y' is going to be an Integer/Number
    Where 'F" is going to be an Integer/Number
    Where 'DomainDesc' is going to be a sentence describing the Domain
    Where 'SubdomainDesc' is going to be a sentence describing the Sub-domain
    Where 'TaskStatementDesc' is going to be a sentence describing the Task Statement



    Effectively I have a 4x900 table in Microsoft Word, and the example text above is all in one column (i.e. so there's lots of similar examples in the same format above), but it's not formatted nicely (as per above), and I need it to look like the 'To' format above. I don't want to have to do this formatting manually for all 900 rows in the column so thought VBA would be great for this.

    I have tried something like this based on collating various snippets of code online and modifying it to my usage, but it is simply deleting my selected text (it thinks 'elements' is empty) and I am not sure what's going wrong or if there's an easier way to do this.


    Sub FormatTextBeforeToAfter()
        Dim selectedRange As Range
        Dim formattedText As String
        Dim lines() As String
        Dim resultText As String
        Dim i As Integer
        
        ' Check if text is selected
        If Not Selection.Text = "" Then
            ' Get the selected range
            Set selectedRange = Selection.Range
            formattedText = selectedRange.Text
            
            ' Split the formattedText into lines
            lines = Split(formattedText, vbCrLf)
            
            ' Initialize the result text
            resultText = ""
            
            ' Loop through each line and format it
            For i = LBound(lines) To UBound(lines)
                ' Check if the line is not empty
                If Trim(lines(i)) <> "" Then
                    Dim elements() As String
                    elements = Split(lines(i), " - ")
                    
                    If UBound(elements) >= 1 Then
                        ' Extract parts
                        Dim domainPart As String
                        Dim subdomainPart As String
                        Dim taskStatementPart As String
                        
                        domainPart = "Domain: " & elements(1) & " - " & elements(0)
                        
                        If UBound(elements) >= 2 Then
                            subdomainPart = "Sub-domain: " & Mid(elements(2), 1, 2) & " - " & Mid(elements(2), 3) ' Adjusted to capture the numeric part.
                        End If
                        
                        If UBound(elements) >= 3 Then
                            taskStatementPart = "Task Statement: " & Mid(elements(3), 1, 2) & " - " & Mid(elements(3), 3) ' Adjusted to capture the numeric part.
                        End If
                        
                        ' Add the formatted parts to the result
                        resultText = resultText & domainPart & vbCrLf & subdomainPart & vbCrLf & taskStatementPart & vbCrLf
                    End If
                End If
            Next i
            
            ' Replace the selected text with the formatted result text
            selectedRange.Text = resultText
        Else
            MsgBox "No text is selected. Please select the text you want to format."
        End If
    End Sub

    Any help would be greatly appreciated thank you!
    Last edited by Restricted; 09-08-2023 at 09:44 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •