CODEWITHSUNDEEP
htmlvbaexcel
Shantanu Mahajan
Shantanu Mahajan

Reputation: 199

Excel VBA For Adding HTML Tag

I have got the following code for Appending the text for Cell. But it is not appending the HTML Tag.

for example:

9/23/99 10:37am We're not going. I'm on disablity. Columbus is not the thriving place. It's mostly retired military. She called to ask why she is the only one. Wanted her partner on, too. I explained that happens in the meeting. 9/10/99 4:15p Rick Ludwig referred $995 Wants Essex Funding & Associates Inc. WY 4262-3607-0011-9582 8/01 8/23/99 9:08am Want a NV corporation. Need to do a little bit of finance change around. A couple of the programs. MU and mentors. Parters and I...I am 60. I maxxed out my cards yesterday.

It Should be Like

"HTML Paragraph Tag"9/23/99 10:37am We're not going. I'm on disablity. Columbus is not the thriving place. It's mostly retired military. She called to ask why she is the only one. Wanted her partner on, too. I explained that happens in the meeting. "End of HTML Paragraph Tag" "HTML Paragraph Tag" 9/10/99 No Need to worry

Following is the code which I have got

Option Explicit

Sub main()
    Dim newStrng As String
    Dim word As Variant
    Dim strngToBeAppended As String

    strngToBeAppended = Application.InputBox("Input string to be appended", 1)

    With Worksheets("TextSheet") '<-- change "TextSheet" to your actual sheet with text name
        For Each word In Split(.Range("A1").Text, " ") '<-- assuming that the text to be splitted is in cell "A1" of the referenced worksheet
            If Len(word) - Len(Replace(word, "/", "")) = 2 Then
                newStrng = newStrng & " " & strngToBeAppended & word
            Else
                newStrng = newStrng & " " & word
            End If
        Next word
        .Range("A2").Value = LTrim(newStrng)
    End With
End Sub

Upvotes: 0

Views: 1976

Answers (1)

user3598756
user3598756

Reputation: 29421

try this:

Option Explicit

Sub main()
    Dim newStrng As String
    Dim word As Variant
    Dim parTag As String, endParTag As String
    Dim dateCounter As Long

    parTag = "<p>" '<-- change this to whatever your "HTML Paragraph Tag" may be
    endParTag = "</p>" '<-- change this to whatever your "End of HTML Paragraph Tag" may be
    With Worksheets("TextSheet") '<-- change "TextSheet" to your actual sheet with text name
        For Each word In Split(.Range("A1").Text, " ") '<-- assuming that the text to be splitted is in cell "A1" of the referenced worksheet
            If Len(word) - Len(Replace(word, "/", "")) = 2 Then
                dateCounter = dateCounter + 1
                If dateCounter > 1 Then newStrng = newStrng & endParTag
                newStrng = newStrng & parTag & word
            Else
                newStrng = newStrng & " " & word
            End If
        Next word
        If dateCounter > 1 Then newStrng = newStrng & endParTag
        .Range("A2").Value = LTrim(newStrng)
    End With
End Sub

Upvotes: 1

Related Questions