Hi everyone, I am looking for some help I am attempting to split the following text into multiple rows based on a 70 character count. and also give each line that it splits into the adjacent rows number. Below is an example line that I am attempting to split. I have 500 or so lines to do. There is also a code I have partially working and what that code outputs. There are several things I could use some help with. ( I got this code from this FORUM on another thread and members user name is Scott Huish, so by no means am a good at VBA.) Any help would be appreciated.
1) The code Run-Time error '1004' every time and will only work to line 72 in my spreadsheet
2) I don't know how to get the code to add the adjacent row # to every line it splits into.
3) Not as big of a deal but some of the lines split oddly see "DOES NOT
COMPLY"
4) Also if it could convert everything into capital letter that would be a bonus.
Sub splitup70()
Dim c As Range, z As String, tmp As String, j As Long, k As Integer
k = -1
For Each c In Range("B1", Range("B65536").End(xlUp))
z = Trim(c)
Do
tmp = Trim(Left(z, 70))
j = 70
If Right(tmp, 1) <> " " And (Mid(z, 71, 1) <> " " And Len(z) > 70) And Right(tmp, 1) <> "." And Right(tmp, 1) <> "," Then j = InStrRev(tmp, " ")
tmp = Left(tmp, j)
If tmp <> "" Then Range("D65536").End(xlUp).Offset(1 + k, 0) = tmp
k = 0
z = Trim(Mid(z, j + 1))
Loop Until tmp = ""
k = 1
Next
End Sub
1) The code Run-Time error '1004' every time and will only work to line 72 in my spreadsheet
2) I don't know how to get the code to add the adjacent row # to every line it splits into.
3) Not as big of a deal but some of the lines split oddly see "DOES NOT
COMPLY"
4) Also if it could convert everything into capital letter that would be a bonus.
| 10080920 | hsg-std-15063584002-rev10 under construction Design and format changes, BF12, Keystone Prince, **”-222-789-805-#, CL125, Lugged, Butterfly, Lever, , Gear for 8" and larger, SSG Number(s) 15.063. 501, 502, 504, 579, Special Designation(s): # = 001 for lever & 002 for gear operator. Valve shall have lockable lever and shall be open position during install. Valve has molded in O-rings for flange to flange connection. No other gasket required for sealing DOES NOT COMPLY WITH LEAD FREE REQUIREMENTS FOR POTABLE WATER, Materials: Body/Bonnet: CI, Seats: Molded-in EPDM, Ball/Disc/Plug/Etc: 304 SS, Stem: 416 SS, Body Bolts & Nuts: , Gland Bolts & Nuts: , Belleville Washers: , Packing: , Body/Bonnet Seals: , Other: Bushing: Acetal, Notes: |
| hsg-std-15063584002-rev10 under construction Design and format | |
| changes, BF12, Keystone Prince, **”-222-789-805-#, CL125, Lugged, | |
| Butterfly, Lever, , Gear for 8" and larger, SSG Number(s) 15.063. 501, | |
| 502, 504, 579, Special Designation(s): # = 001 for lever & 002 for | |
| gear operator. Valve shall have lockable lever and shall be open | |
| position during install. Valve has molded in O-rings for flange to | |
| flange connection. No other gasket required for sealing DOES NOT | |
| COMPLY WITH LEAD FREE REQUIREMENTS FOR POTABLE WATER, Materials: | |
| Body/Bonnet: CI, Seats: Molded-in EPDM, Ball/Disc/Plug/Etc: 304 SS, | |
| Stem: 416 SS, Body Bolts & Nuts: , Gland Bolts & Nuts: , Belleville | |
| Washers: , Packing: , Body/Bonnet Seals: , Other: Bushing: Acetal, | |
| Notes: |
Sub splitup70()
Dim c As Range, z As String, tmp As String, j As Long, k As Integer
k = -1
For Each c In Range("B1", Range("B65536").End(xlUp))
z = Trim(c)
Do
tmp = Trim(Left(z, 70))
j = 70
If Right(tmp, 1) <> " " And (Mid(z, 71, 1) <> " " And Len(z) > 70) And Right(tmp, 1) <> "." And Right(tmp, 1) <> "," Then j = InStrRev(tmp, " ")
tmp = Left(tmp, j)
If tmp <> "" Then Range("D65536").End(xlUp).Offset(1 + k, 0) = tmp
k = 0
z = Trim(Mid(z, j + 1))
Loop Until tmp = ""
k = 1
Next
End Sub
