Loop through rows and copy to next column, cut text > 60 characters
Hi Everyone
I'm new to this forum so I'm just finding my way around.
I have a worksheet that in column A has thousands of rows of text. The text is only in column A and can be null or have up to and over 1000 characters. I need to run a macro that will loop through column A and copy each one into column B and if the text is > 60 characters it needs to chop it into chunks of 60 into columns. I have the following code which counts the characters and copies into columns if its over 60 but cannot find anything that will loop through each row or copy anything that under 60 character's.
Sub test()
Dim cLength As Long
Dim cLoop As Long
cLength = 60
For cLoop = 1 To (Len([A2]) \ cLength) +1
[A2].Offset(, Loop).Value = Mid([A2],((cLoop -1 * cLength) +1, c,Length)
Next
End Sub
Thanks in advance matt3436
(Excel 2007)
this resolves your looping
this resolves your looping through each row problem...
to resolve ur <60 chars problem, ul need to add a condition
if(len(cells(iRow,1))< 60 then
etc..
Sub test()
Dim cLength As Long
Dim cLoop As Long
cLength = 60
'#########################
dim EndRow as long
dim iRow as long
EndRow = cells(rows.count,end(xlup).row ' find the last row
for iRow = 2 to EndRow
'#########################
'For cLoop = 1 To (Len([A2]) \ cLength) +1
'[A2].Offset(, Loop).Value = Mid([A2],((cLoop -1 * cLength) +1, c,Length)
For cLoop = 1 To (Len(cells(iRow,1)) \ cLength) +1
cells(iRow,1).Offset(, Loop).Value = Mid(cells(iRow,1),((cLoop -1 * cLength) +1, c,Length)
Next
'#########################
next
'#########################