Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vba macro to cut and shift text65 char
Different approach .... try this:
Sub CopyRows() Dim LastRow As Long Dim i As Long Dim x As Integer Dim NewRows As Integer Application.ScreenUpdating = False LastRow = Range("H65536").End(xlUp).Row For i = LastRow To 1 Step -1 If Len(Range("H" & i)) 65 Then NewRows = Int(Len(Range("H" & i)) / 65) For x = NewRows To 1 Step -1 Range("H" & i).Offset(1, 0).EntireRow.Insert Range("A" & i & ":G" & i).Copy _ Range("A" & (i + 1)) Range("H" & (i + 1)) = _ Mid(Range("H" & i), (x * 65) + 1, 65) Next ' NewRows Range("H" & i) = Left(Range("H" & i), 65) End If Next ' i Application.ScreenUpdating = True End Sub Regards Trevor "Nat1" wrote in message ... Hi, Can someone please help me with a macro which was kindly written by another member ). What I am trying to accomplish is to cut text from col H 65 char and inserting into the next row in col H. Lyford's code does just that, but what I also need is cols A:G (which are primary keys) to be copied and inserted onto the next row. This should only happen when col H is cut and shift down. I'm working with an oracle database (relational) and the field character limit is 65. My spreadsheet has over 10,000 records, so this macro would be invaluable. Can someone tell me what I need to change in this macro: Sub TrimTo65() myRow = 1 Range("H" & myRow).Select myString = ActiveCell.Value While myString < "" ' Walk the column, as long as you don't encounter empty cells. While Len(myString) 65 ' If the string is fewer than 65 characters, no work is required. If more, we split it up... ' and shove the next 65 characters into the current cell. mySubString = Left(myString, 65) ActiveCell.Value = mySubString ' ...and adjust the string myString = Right(myString, Len(myString) - 65) ' shift everything down, to open a new cell for the remainder of this text myRow = myRow + 1 Range("H" & myRow).Select Application.CutCopyMode = False Selection.Insert Shift:=xlDown If Len(myString) < 65 Then ' OK - the remainder is fewer than 65 characters. Stick it into the opened cell, and move on. ActiveCell.Value = myString End If Wend ' advance to the next cell myRow = myRow + 1 Range("H" & myRow).Select myString = ActiveCell.Value Wend End Sub Any help, suggestions would be greatly appreciated. I'm at my wits end trying to figure out what I'm doing wrong. I will be honest I'm a novice with visual basic, I have only just started to write in this language. Regards, |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I return text in between char(10) | Excel Discussion (Misc queries) | |||
cut-off text in cell.. I want excel to cut-off the text after # of char. | Excel Discussion (Misc queries) | |||
8500 cells with phone number(7 char.), wishing to add area code (10 char.) | Excel Discussion (Misc queries) | |||
text (3750 char.)truncates with text wrap and row heigh adjusted? | Excel Discussion (Misc queries) | |||
TEXT, CHAR and CODE | Excel Programming |