UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA That Wraps Text, Office 2013    
 
   
Dexter
post Nov 14 2017, 02:18 PM
Post#1



Posts: 625
Joined: 5-November 07



I have the following code and it works great but I want to apply the same thing to range H:H but with intsplit that equals 50 instead of 142. How do I get that to work? Thanks

CODE
Dim strTarget As String, lngLen As Long, intRep As Integer, i As Integer
    Const intSplit As Integer = 142
    If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub
    If Len(Target.Value) > intSplit Then
    strTarget = Target.Value
    lngLen = Len(strTarget)
    intRep = Int(lngLen / intSplit) + 1
    For i = 1 To intRep
    Target.Offset(i - 1, 0).Value = Mid(strTarget, (i - 1) * intSplit + 1, intSplit)
    Next i
    
End If
Go to the top of the page
 
Doug Steele
post Nov 14 2017, 02:36 PM
Post#2


UtterAccess VIP
Posts: 21,497
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Would it not be as simple as changing

CODE
Const intSplit As Integer = 142

to

CODE
Const intSplit As Integer = 50

and

CODE
   If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub

to

CODE
   If Intersect(Target, Range("H:H")) Is Nothing Or Target.Count > 1 Then Exit Sub

?

--------------------
Go to the top of the page
 
Dexter
post Nov 14 2017, 04:05 PM
Post#3



Posts: 625
Joined: 5-November 07



I tried what you posted and it did not work. I have both instances on my sheet. I want it to wrap in cell A and in H. Not sure what I am doing wrong.
Go to the top of the page
 
Doug Steele
post Nov 14 2017, 04:16 PM
Post#4


UtterAccess VIP
Posts: 21,497
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What does "it did not work" mean? If you got an error, what was the exact wording of the error? If you didn't get an error, what did you get?

If you want to wrap both columns, you'd either need to write a sub that wraps a given column and call it twice, passing it the different parameters each time, or do something like

CODE
Dim strTarget As String, lngLen As Long, intRep As Integer, i As Integer
  
    Const intSplitA As Integer = 142
    Const intSplitH As Integer = 50

    If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub
    If Len(Target.Value) > intSplitA Then
      strTarget = Target.Value
      lngLen = Len(strTarget)
      intRep = Int(lngLen / intSplitA) + 1
      For i = 1 To intRep
        Target.Offset(i - 1, 0).Value = Mid(strTarget, (i - 1) * intSplitA + 1, intSplitA)
      Next i
    End If
  
    If Intersect(Target, Range("H:H")) Is Nothing Or Target.Count > 1 Then Exit Sub
    If Len(Target.Value) > intSplitH Then
      strTarget = Target.Value
      lngLen = Len(strTarget)
      intRep = Int(lngLen / intSplitH) + 1
      For i = 1 To intRep
        Target.Offset(i - 1, 0).Value = Mid(strTarget, (i - 1) * intSplitH + 1, intSplitH)
      Next i
    End If

Note that I'm assuming your original code works!

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:48 AM