How to wrap text to next row

[mthelm85]

I've been searching the internet for this for about 2 hours now and I cannot figure out how to do it. Due to the format of my worksheet, a textbox won't work, wrapping the text in the same cell won't work, etc. I need to be able to wrap my text from one row into the row directly below it. For example, if I'm typing in cell A18, I need the text to auto wrap to cell A19 after 50 characters or so. The ONLY thing I have been able to find is this code that doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim a As Integer, b As Integer
Dim Rng As Range

Set Rng = Range("A18")
a = 0

If Not Intersect(Target, Rng) Is Nothing Then

    If Len(Target) > 50 Then
        
        Do
        
            If a > 50 Then Exit Do
            b = a
            a = Application.Find(" ", Rng, a + 1)
            
        Loop Until a > 50            
        
        Target.Offset(1, 0) = Right(Target, Len(Target) - b)
        Target = Left(Target, b - 1)

    End If

End If
        
End Sub

PLEASE HELP ME!!