Help with macro to split a line of text

[zoog25]

Hello all,

I know this is really an easy macro but I'm not quite sure how to get it written, still quite new to macros.

Anyways here is the situation, I have a cell on one sheet, C15 that contains several lines of text. On another sheet I want to transfer this text so it fits in range("J15:Q31"). This is what I have as a base.

Sub text()
Sheet2.Range("C15").Copy
 
With Sheet1.Range("J16:Q16")
.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.horizontalalignment = xlCenterAcrossSelection.
End With
End Sub

Now here is the problem, the above code works great across the cells to but vertically i miss text.

Now I think i need to split the text cell C15 so i can spread it down several rows.

i'm not good at thinking of it but here is my idea. For each row, i discovered you can place 76 characters so i can split up the text in groups of 76. So somehow I would need it to count the letters in the string and once it it 76 it would look to see if the next character was a letter or space. If it was a letter then it would move backwards to the last space it past and cut the text there and place it in J16 which is Centered across J16:Q16. Then it would pick up from the the point it cut off and run down the next set of 76 characters and do the same with the next set of text and place it in J17 and so on until there was no more text to split. My range is from J16:Q31. If this is possible please help.

 

[zoog25]

Here is a thought but i don't know if this will work because i don't know the proper coding so here is my idea

Dim count as string
Dim point as string
Set text = sheet1.Range("C15")

count = 0
point = 76
While count = 0
If "Find the 76th character in the string or point equals " " and the 77th character does not equal " " Then
sheet2.Range("J16").Value = Left(Text,76)
point = point + 76
ElseIf "Find the 76th character in the string or point equals a character THEN
sheet2.Range("J16").Value = "starting for the 76th character we move back to the first blank space we have. Ie. 72th character is blank and pastes the value there.
point = 73 'on the first run but on the next run it would start at this point and count over 76 characters
Else
'If both the 76th and 77th character equals blank, it would find the last character in that group and paste it from the starting point of the group to the end of the line.

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] Split_Lines()
    
    [color=darkblue]Dim[/color] strAll [color=darkblue]As[/color] [color=darkblue]String[/color], strMax [color=darkblue]As[/color] [color=darkblue]String[/color], i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]Const[/color] MaxChars [color=darkblue]As[/color] [color=darkblue]Long[/color] = 76     [color=green]'Max number of characters per line[/color]
    
    strAll = Sheet2.Range("C15").Value
    
    [color=darkblue]Do[/color] [color=darkblue]While[/color] Trim(Len(strAll))
        
        strMax = Left(strAll, MaxChars + 1)
        [color=darkblue]If[/color] Len(strMax) = MaxChars + 1 And InStr(strMax, " ") [color=darkblue]Then[/color]
            strMax = Left(strMax, InStrRev(strMax, " "))
        [color=darkblue]End[/color] [color=darkblue]If[/color]
        
        [color=darkblue]With[/color] Sheet1.Range("J16:Q16").Offset(i)
            .Cells(1).Value = Trim(strMax)
            .HorizontalAlignment = xlCenterAcrossSelection
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        
        strAll = Trim(Replace(strAll, strMax, ""))
        i = i + 1
    [color=darkblue]Loop[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[zoog25]

Thanks you my friend, this coding is just what i needed it to be.