In the example I have in column A cells with text, which should be printed on a label. The label has a maximum of 40 characters for each line.
So if the total characters of this cell is more than 40, it should be split in 2 columns.
I've managed to split the text, but as you'll see in this example the split is mostly in the middle of a word.
This in not very readable if it is printed on a label.
Can anyone help me to find out how the split will be at the maximum of 40 char. and at the end of a word?
According to your attachment a VBA beginner starter :
PHP Code:
Sub Demo1() Const M = 40 Dim V, S$(), R&, C% With Blad1.Range("A2:A" & Blad1.UsedRange.Rows.Count) V = .Value2 ReDim S(1 To UBound(V), 1) For R = 1 To UBound(V) If Len(V(R, 1)) > M Then C = InStrRev(V(R, 1), " ", M + 1) S(R, 0) = Left(V(R, 1), C - 1) S(R, 1) = Mid(V(R, 1), C + 1) Else S(R, 0) = V(R, 1) End If Next .Offset(, 2).Resize(, 2).Value2 = S End With End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !
As you already have noticed, I am a beginner I am Always surprised how the experts in this forum solve an issue in a few minutes, while I have spent hours on "google search" and still can't figure it out.
Can you explain me the "Dim line" please? I recognize dat S$() declares an array, but where stands & and % for?
Can you explain me the "Dim line" please? I recognize dat S$() declares an array, but where stands & and % for?
First thanks for the rep' !
Those characters are declaring the variables types as you can see during the execution in the VBE Locals window or
just in the VBA help for String for example. Without any character the variable is Variant but not for a constant
as VBA choose a default type according to the constant value …
It's just a legacy from the VBA grand father : the BASIC !
Try a little experiment : in cell A3 add a space between the dot and "B"
Then compare your result in cell C3 with mine
so you must see your pSpc allocation just needs InstrRev like I used in my demonstration for the variable C …
Bookmarks