Solved: value of cell continues to a cell below?

[lehgzil]

gud day everyone,

i would like to ask if is it possible add a value to a cell which when full, continues to its value to a cell below?
example

**original value of a1:e1
Thirty Three thousand three hundred thirty three dollars

**if i cut the range to a1:c1
Thirty Three thousand three hundred

**i want the continuations to be at a2:b2 which would look something like
thirty three dollars.

or if i typed and fill a1:c1 it will directly continue to a2:b2.

hoping for suggestions, thanks

[Simon Lloyd]

I really don't understand what your asking can you provide a workbook to explain better?

[lehgzil]

hi sir simon,

here is the file, well, without code.
its just to show what i mean.

a1:c1 were merged as well as a2:b2

. now what i would like to do is to cut the value if it reach the end of the cell, like if merged cell -a1:c1 was full, excess value will pe outputed to the merged cell a2:b2.

i would like to know if this is possible and how will it be done?

thanks

[lehgzil]

another explanation,
its like limiting the value of merged cells a1:c1(or simply a1) to lets say 44 characters and then move the excess value to merged cells a2:b2(or simply b2).
so if i have 57 inputed characters on cell a1
it will only show upto the 44th of the inputed characters on a1(a1:c1) and the
45th character to 57th outputed on a2(a2:b2).
<original input>
A1
thequickbrownfoxjumpsovertheheadofthelazydogthequickbrown
<processed output>
A1
thequickbrownfoxjumpsovertheheadofthelazydog
A2
thequickbrown

if its possible to also counts spaces eventually..


really hoping for suggestions...

[lehgzil]

solved it via
http://www.mrexcel.com/forum/showthread.php?p=2485819#post2485819
thanks

[mdmackillop]

Here's some code I wrote (long time ago) to fit text to the cell width, moving the excess to the following cells.


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRatio As Double
On Error GoTo Exits:
Application.EnableEvents = False
MyRatio = 0.175 '< Modify to suit font
If Len(Target.Text) / Target.Width > MyRatio Then
SplitText MyRatio
End If
Exits:
Application.EnableEvents = True
End Sub

'Default ratio for splitting; adjust to suit font size
Sub SplitText(SplitRatio)
Dim MyText As String
Dim WrapLength As Long, StrLen As Long, j As Long
Dim NextCell As Range
Application.EnableEvents = False
'Return to previous cell
If Application.MoveAfterReturnDirection = xlToRight Then
Set NextCell = ActiveCell
ActiveCell.Offset(0, -1).Select
Else
ActiveCell.Offset(-1).Select
End If
WrapLength = Int(ActiveCell.Width) * SplitRatio
'Analyse text for space preceding cell width and split text
Do
MyText = ActiveCell.Text
StrLen = Len(MyText)
If StrLen > WrapLength Then
For j = WrapLength To 0 Step -1
If j = 0 Then Exit For
If Mid(MyText, j, 1) = " " Then
ActiveCell.Formula = Left(MyText, j)
ActiveCell.Offset(1).EntireRow.Insert
ActiveCell.Offset(1, 0).Formula = Right(MyText, StrLen - j)
Exit For
End If
Next
ActiveCell.Offset(1, 0).Select
End If
Loop Until Len(ActiveCell) <= WrapLength
ActiveCell.Offset(1, 0).Select
'Move to right based on MoveAfterEnter
If Not NextCell Is Nothing Then NextCell.Select
Application.EnableEvents = True
End Sub

[lehgzil]

thanks sir mdmackillop, that will come in handy in the future.
thanks