VBA - Split String closest to 300 char with the delimiter fullstop

[omnivl]

Hi

I need to split a string closest to 300 characters where the last character is a full stop...woah hope that makes sense .

The text keeps concatenating depending on the values in other cells for example:

If Sheets("control").Range("B15").Value = "AS2124" Then
letterString = letterString & "Please submit the above documentation via email to xxx@xxx.com by close of business on " & Format(Date + 7, "Long Date") & ".  Once this documentation has been received and approved, a Contract Entry Meeting will be scheduled prior to the Certificate for Possession of Site of being issued." & vbNewLine & vbNewLine & "Please be advised that no work is to commence until contracts have been signed by yourself and by the Department of Works; and until you are in receipt of a Certificate for Possession of Site issued by the Department of Works." & vbNewLine & vbNewLine & "The Project Supervisor for this contract is " & Sheets("control").Range("B16").Value & vbNewLine & vbNewLine
End If

I need to split the string after the last full stop closest to 300 characters in length. Once it has been split the strings need to go to

Sheets("macro 4").Range("B23").Value = letterString1
Sheets("macro 4").Range("B29").Value = letterString2

 

[tonyyy]

omnivl,

The following might get you started...

Sub SplitString_1062501()
Dim letterString As String, letterString1 As String, letterString2 As String
Dim k As Long

If Sheets("control").Range("B15").Value = "AS2124" Then
    letterString = letterString & "Please submit the above documentation via email to xxx@xxx.com by close of business on " & Format(Date + 7, "Long Date") & ".  Once this documentation has been received and approved, a Contract Entry Meeting will be scheduled prior to the Certificate for Possession of Site of being issued." & vbNewLine & vbNewLine & "Please be advised that no work is to commence until contracts have been signed by yourself and by the Department of Works; and until you are in receipt of a Certificate for Possession of Site issued by the Department of Works." & vbNewLine & vbNewLine & "The Project Supervisor for this contract is " & Sheets("control").Range("B16").Value & vbNewLine & vbNewLine
End If

If Len(letterString) > 300 Then
    k = InStrRev(letterString, ".", 300)
    letterString1 = Left(letterString, k)
    letterString2 = Mid(letterString, k + 1, Len(letterString) - k)
End If

Sheets("macro 4").Range("B23").Value = letterString1
Sheets("macro 4").Range("B29").Value = letterString2
End Sub

Cheers,

tonyyy

 

[omnivl]

Awesome thanks

 

[tonyyy]

You're very welcome...