Dear Roberto Mensa
First, many thanks for your valuable help. (sorry for the late reply).
The code was really helpful but with one issue. The code successfully splits
the multi line test to individual texts. But there was some issue with the
text replacement. I solved it with the following code.
----------------------------------------------------------------------------------------------
If Right(oTxtRng1, 1) = Chr(10) Or Right(oTxtRng1, 1) = vbLf Then
oTxtRng = Mid(oTxtRng1, 1, Len(oTxtRng1) - 1)
Else
oTxtRng = oTxtRng1
End If
----------------------------------------------------------------------------------------------
Thanks a lot.
Wish you and all a Merry Christmas and Happy New Year
Regards
Chandru
On Fri, Dec 17, 2010 at 5:15 AM, roberto mensa <robb....@gmail.com> wrote:
> I'm not sure of having understood,
> try:
>
> Sub MFR_Textbox()
> Dim Wks As Worksheet
> Dim rngSrch As Range
> Dim rngFind As Worksheet
> Dim rngFound As Range
> Dim oShp As Shape
> Dim oTotalRng As Variant
> Dim oTxtRng As Variant
> Dim oTxtRng1 As Variant
> Dim oTxtRng2 As Variant
> Dim intLength As Integer
> Dim intLineCount As Integer
> Dim intCnt As Integer
> Dim s As String
> Dim v
> On Error Resume Next
> Set Wks = Sheets("Sheet1")
> Set rngFind = Sheets("Glossary")
>
> For Each oShp In Wks.Shapes
> s = oShp.TextFrame.Characters.Text
> v = Split(s, Chr(10))
> For intCnt = 0 To UBound(v)
> oTxtRng2 = v(intCnt)
> oTxtRng1 = Application.WorksheetFunction.Trim$(oTxtRng2)
> If Right(oTxtRng1, 1) = " " Then
> oTxtRng = Mid(oTxtRng1, 1, Len(oTxtRng1) - 1)
> Else
> oTxtRng = oTxtRng1
> End If
> Cells(intCnt, 6).Value = oTxtRng
> Cells(intCnt, 7).Value = Len(oTxtRng)
> With rngFind
> Set rngFound = .Range("A:A").Find(What:=oTxtRng,
> LookIn:=xlValues, LookAt:= _
> xlPart, SearchOrder:=xlByRows, MatchCase:=False,
> SearchFormat:=False)
> oShp.TextFrame.TextRange.Lines(intCnt).Text =
> rngFound.Offset(0, 1)
> End With
> Next intCnt
> Next oShp
> Set rngSrch = Nothing
> Set rngFind = Nothing
> Set rngFound = Nothing
> End Sub
>
> in the Excel object model, there are many problems for text into shape.
> regards
> r
>
Dear Experts
I am trying to find and replace the text from various textboxes (part of
shape object) in sheet1 from the glossary in sheet2. The code works fine if
there is single line of text in textbox.
I am getting an additional carriage return when reading the text in case the
textbox has multiple lines. This happens even after trimming the text. You
can see that by running the code in the procedure named "MFR_textbox". The
problem is when there is an extra carriage return from the text picked, it
does not return a match from the glossary.
Any ideas on how to trim them additional carriage return. Any alternate
suggestions are also welcome.
I have attached the file along with the samples.
Regards
Chandru
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
