Auto enter in Excel

[akira01]

Hi All,
I need some help and guide for excel experts here. Is there a way that Excel can automatically enter to next row once the typing has reach the end.

Example: I have 5 columns (A to E) and I am typing at column A. While typing, the text will go along to cell B, C, D and E. The problem is, if I don't manually go to next row and continue typing, the text will go to column F, G and so on. Is there a way where excel can automatically jump to next row if the text has reach column E?

Another problem is, say I have 3 rows full of text (column A to E). If I edit one of the row to exter some new texts, the whole sentence will go along to column F, G and so on. What I can do now is, re-edit all the rows to adjust them back.

Please advise. Thanks in advance.

 

[AnalyticsGuy7]

I think I did it!!!!

Go ahead and try it...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim c, d As Range
Set d = Intersect(Target, Range("A:A"))
If d Is Nothing Then
Else
If Len(Target) > 49 Then
'First we add the spillover to the second cell and then truncate the first cell
 
Target.Offset(1, 0) = Right(Target, Len(Target) - 49) & Target.Offset(1, 0)
Target = Left(Target, 49)
 
'Now we have to add spillover to the second cell = the last bit of a word
'then we will remove that word from the first cell
 
Target.Offset(1, 0) = Right(Target, 49 - Application.WorksheetFunction.Find("^^", Application.WorksheetFunction.Substitute(Target, " ", "^^", Len(Target) - Len(Application.WorksheetFunction.Substitute(Target, " ", ""))))) & Target.Offset(1, 0)
Target = Left(Target, Application.WorksheetFunction.Find("^^", Application.WorksheetFunction.Substitute(Target, " ", "^^", Len(Target) - Len(Application.WorksheetFunction.Substitute(Target, " ", "")))))
End If
End If
End Sub

 

[akira01]

I think I did it!!!!

That's exactly what I meant. YES, you really did it. Wow you are genius. Thank you very very much. You solve my problem that I've faced for years. I hope I can find this forum earlier.

Once again, thank you for all your time and help. Thanks

 

[xieyrawk]

That's exactly what I meant. YES, you really did it. Wow you are genius. Thank you very very much. You solve my problem that I've faced for years. I hope I can find this forum earlier.

Once again, thank you for all your time and help. Thanks

hai akira, can you please teach me where did i suppose to write the code?

 

[akira01]

hai akira, can you please teach me where did i suppose to write the code?

Right-click on the tab and select "View Code", another window will show up. You just have to paste the code in the empty space there and close the window to go back to excel.

 

[RosieRose]

Hi Erik, I was hoping you could elaborate a little more on this solution, as I have a very similar situation. For each Row, I have column H-W to write in, once I get to X, I would like the text to go into the following row.
Using shrink to size or wrap text is not an option for me as I need to keep my row heights consistent, as well as my text size. For now, I am using Merge & Centre and then left Align, however if I need to go back to add more text I need to reformat all the other rows below.
To elaborate a little more: Columns E,F,G have a title and Columns H-W have the description, sometimes I require more rows. I have rows 10-23 as my area, and need to keep the formatting/size as is.
Thank you for any and all help

 

[erik.van.geit]

Hi, RosieRose

You replied to a 6 year old thread; perhaps you didn't notice because of the two more recent posts just before yours.
Not sure if I understand your question exactly; I see some ambiguity. Can you clearly explain perhaps using a real example?

kind regards,
Erik