Consulting

Results 1 to 18 of 18

Thread: Problem with OR condition

  1. #1
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location

    Unhappy Problem with OR condition

    Hi,
    I am trying to create a macro which will search for space before comma and space before full stop (period).
    If i check one condition at a time like , only it works perfectly fine. However when i apply or condition it does not work properly.

    "If crn_txt <> "," Or crn_txt <> "." Then

    If crn_txt = "," Or crn_txt <> "," And old_text = " " Then"



    Given below is the code that i have prepared for reference. Checking one by one in loop like .(period) then comma it is taking lots of time.

    Please help it is very important for me.



    Sub spc_bfr_comma()
    fnt_nme = False
    fnt_clr = False
    Dim oSld As Slide
    Dim now_end As Boolean
    Dim x As Long
    Dim m As Double
    Dim R As Double

    shape_cont = 1
    err_num = 0
    now_end = False
    slide_num = 1
    m = 0
    fnt_clr_chck = False

    For Each oSld In ActivePresentation.Slides
    With Application.ActiveWindow
    ViewType = ppViewNormal
    On Error Resume Next
    .View.GotoSlide slide_num
    End With
    For Each oShp In oSld.Shapes
    If oShp.HasTextFrame Then
    If oShp.TextFrame.HasText Then
    With oShp.TextFrame.TextRange
    'For x = .Runs.Count To 1 Step -1
    Text = .Runs(x).Text
    On Error Resume Next
    ActiveWindow.Selection.SlideRange.Shapes(shape_cont).Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
    whole_text = ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Text
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Words
    whole_text_len = Len(whole_text)
    'crn_txt = ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=x). Characters
    cnrvsn_text_dbl = CDbl(whole_text_len)
    end_loop = cnrvsn_text_dbl
    For k = 1 To end_loop

    crn_txt = ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(start:=k). Characters
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(start:=k). Select
    If crn_txt <> "," Or crn_txt <> "." Then
    old_text = Empty
    End If
    If crn_txt = "," Or crn_txt <> "," And old_text = " " Then
    d = k - 1
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(start:=d). Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(start:=d). Cut
    k = k - 5
    'If k > end_loop Then
    'shape_cont = shape_cont + 1
    'GoTo a:
    'Exit Sub
    'End If

    'MsgBox "Error"
    old_text = Empty
    crn_txt = Null

    End If

    If crn_txt = " " Then
    old_text = crn_txt
    End If

    Next k


    'Next x
    End With
    End If 'has text
    End If 'has textframe
    a:
    shape_cont = shape_cont + 1
    Next oShp
    slide_num = slide_num + 1
    shape_cont = 1
    Next oSld
    slide_num = 1
    'period_chk
    'MsgBox "SPACE BEFORE comma DONE"
    'UserForm1.Visible = False
    End Sub


    Regards
    Rathish

  2. #2
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    Does this run faster?

    [VBA]Sub spacecomma()
    Dim osld As Slide
    Dim oshp As Shape
    For Each osld In ActivePresentation.Slides
    For Each oshp In osld.Shapes
    If oshp.HasTextFrame Then
    If oshp.TextFrame.HasText Then
    Call replaceme(oshp, " ,", ",")
    Call replaceme(oshp, " .", ".")
    End If
    End If
    Next oshp
    Next osld
    End Sub
    Sub replaceme(oshp As Object, ReplaceThis As String, ReplaceWith As String)
    Dim oTxtR As TextRange
    Dim oTmpR As TextRange
    Set oTxtR = oshp.TextFrame.TextRange
    Set oTmpR = oTxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, WholeWords:=False)
    Do While Not oTmpR Is Nothing
    Set oTmpR = oTxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, _
    After:=oTmpR.Start + oTmpR.Length, _
    WholeWords:=False)
    Loop

    End Sub
    [/VBA]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  3. #3
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location

    Problem with OR condition Reply to Thread

    Thank for a quick reply. However code stucks on the given below part:

    Do While Not oTmpR Is Nothing
    Set oTmpR = oTxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, _
    After:=oTmpR.Start + oTmpR.Length, _
    WholeWords:=False)
    Loop
    Thanks a lot for the help....

    Regards
    Radish

  4. #4
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    I can't see any problem with the code.
    Did you copy paste it?
    What version of PowerPoint?
    Do you get an error message or are you saying the loop doesn't terminate.
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  5. #5
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    Thank you very much for the help......

  6. #6
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location

    find and replace option

    Hi,
    Your code drastically changed the timings it is like a magic. within 2 minutes it is checking almost 20 slides. Thanks.

    Just wanted to check if i want do the change as per user input for e.g.

    >>If i get "&" sign i want to replace with and.

    >>A userform will appear (have got the user form).

    >>Based on the user's input "change" or "No change" it work.

    In the code that you provided. After getting the changing option; if changing option is equal to "&" then call the userform. I tried doing it but messed up.

    The reason for giving this option is in company names sometimes we have to retain & sign as it is and in the flowing text we have to change to and.

  7. #7
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    Not trivial to do that properly. If you want an addIn written to do it properly we will happily quote you. It won't be very expensive and will save you hours.
    john AT pptalchemy.co.uk
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  8. #8
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    Thanks John Really appreciate your help. However my boss will not pay me for this. Will get back to you once i discuss.

  9. #9
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    Bosses are like that, can'yt see the wasted time!

    For a quick 'dirty' fix try

    [VBA]Sub spacecomma()
    Dim osld As Slide
    Dim oshp As Shape
    For Each osld In ActivePresentation.Slides
    For Each oshp In osld.Shapes
    If oshp.HasTextFrame Then
    If oshp.TextFrame.HasText Then
    Call replaceme(oshp, " ,", ",")
    Call replaceme(oshp, " .", ".")
    If InStr(oshp.TextFrame.TextRange, "&") > 0 Then
    ActiveWindow.View.GotoSlide (osld.SlideIndex)
    If MsgBox(oshp.TextFrame.TextRange & vbCrLf & _
    "REPLACE '&' with 'and?'", vbYesNo) = vbYes Then Call replaceme(oshp, "&", "and")
    End If
    End If
    End If
    Next oshp
    Next osld
    End Sub
    Sub replaceme(oshp As Object, ReplaceThis As String, ReplaceWith As String)
    Dim oTxtR As TextRange
    Dim oTmpR As TextRange
    Set oTxtR = oshp.TextFrame.TextRange
    Set oTmpR = oTxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, WholeWords:=False)
    Do While Not oTmpR Is Nothing
    Set oTmpR = oTxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, _
    After:=oTmpR.Start + oTmpR.Length, _
    WholeWords:=False)
    Loop

    End Sub
    [/VBA]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  10. #10
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    Thanks a ton.
    For not only giving me the option but making me learn a new thing..... Today...

  11. #11
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    Hi Got an issue in the code.
    This doesn't check anything in the table. Please help me....

  12. #12
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    Hey John,
    I tried to fix the issue; given below is the code.
    Can you please have a look if i haven't done any blunder mistake.

    Thanks
    Rathish

    Sub spacecomma()
    Dim osld As Slide
    Dim oshp As Shape
    For Each osld In ActivePresentation.Slides
    For Each oshp In osld.Shapes
    If oshp.HasTextFrame Then
    If oshp.TextFrame.HasText Then
    Call replaceme(oshp, " ,", ",", txt_fld, iRow, iColumn)
    Call replaceme(oshp, " .", ".", txt_fld, iRow, iColumn)
    End If
    End If

    If oshp.HasTable Then
    oshp.Select

    With ActiveWindow.Selection.ShapeRange.Table

    rcont = .Rows.Count
    clcnt = .Columns.Count
    For iRow = 1 To rcont

    For iColumn = 1 To clcnt
    If .Cell(iRow, iColumn).Shape.TextFrame.HasText Then
    txt_fld = .Cell(iRow, iColumn).Shape.TextFrame.TextRange
    Call replaceme(oshp, " ,", ",", txt_fld, iRow, iColumn)
    Call replaceme(oshp, " .", ".", txt_fld, iRow, iColumn)
    End If
    Next iColumn
    Next iRow
    End With
    End If

    Next oshp
    Next osld
    End Sub
    Sub replaceme(oshp As Object, ReplaceThis As String, ReplaceWith As String, txt_fld_1, iRow, iColumn)
    Dim oTxtR As TextRange
    Dim oTmpR As TextRange
    'Set oTxtR = oshp.TextFrame.TextRange
    'Set oTxtR = oshp.TextFrame.TextRange
    Set oTxtR = ActiveWindow.Selection.ShapeRange.Table.Cell(iRow, iColumn).Shape.TextFrame.TextRange
    Set oTmpR = oTxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, WholeWords:=False)
    Do While Not oTmpR Is Nothing
    Set oTmpR = oTxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, _
    After:=oTmpR.Start + oTmpR.Length, _
    WholeWords:=False)
    Loop

    End Sub

  13. #13
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    I would have a different search replace routine called for tables.

    have a look at shyam's page here
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  14. #14
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    one question in your code.
    Condition is
    Lets say we have 2 "&" in a text box; now for the first "&" i clicked cancel what the code is actually doing is it move to next text box rather than searching the second "&" in the same text box.

    I got only this problem left with me. Please help....

  15. #15
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    one question in your code.
    Condition is
    Lets say we have 2 "&" in a text box; now for the first "&" i clicked cancel what the code is actually doing is it move to next text box rather than searching the second "&" in the same text box.

    I got only this problem left with me. Please help....

  16. #16
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    You would need to do something like this:

    NOTE This is top of head code and may need a little work!

    [VBA]Sub spacecomma()
    Dim osld As Slide
    Dim oshp As Shape
    Dim otempR As TextRange
    Dim newStart As Long
    For Each osld In ActivePresentation.Slides
    For Each oshp In osld.Shapes
    newStart = 1
    If oshp.HasTextFrame Then
    If oshp.TextFrame.HasText Then
    Call replaceme(oshp, " ,", ",")
    Call replaceme(oshp, " .", ".")

    Set otempR = oshp.TextFrame.TextRange
    Do While InStr(otempR.Text, "&") > 0
    newStart = InStr(otempR.Text, "&") + 1
    ActiveWindow.View.GotoSlide (osld.SlideIndex)
    If MsgBox("'" & otempR & "'" & vbCrLf & vbCrLf & _
    "REPLACE first '&' with 'and?'", vbYesNo) = vbYes Then
    otempR.Replace "&", "and"
    Set otempR = otempR.Characters(newStart - 2, Len(oshp.TextFrame.TextRange))
    Else
    Set otempR = otempR.Characters(newStart, Len(oshp.TextFrame.TextRange))
    End If

    Loop
    End If
    End If
    Next oshp
    Next osld
    End Sub
    Sub replaceme(oshp As Object, ReplaceThis As String, ReplaceWith As String)
    Dim otxtR As TextRange
    Dim oTmpR As TextRange
    Set otxtR = oshp.TextFrame.TextRange
    Set oTmpR = otxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, WholeWords:=False)
    Do While Not oTmpR Is Nothing
    Set oTmpR = otxtR.Replace(FindWhat:=ReplaceThis, _
    Replacewhat:=ReplaceWith, _
    After:=oTmpR.Start + oTmpR.Length, _
    WholeWords:=False)
    Loop

    End Sub[/VBA]
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

  17. #17
    VBAX Regular
    Joined
    Apr 2012
    Posts
    35
    Location
    Hi,
    Sorry It didn't worked. Concept is right however it is replacing the first "&".
    Condition is: Ra & Tez &
    in this when code gets first "&" after Ra i cancelled.
    then it searched for "&" in rest of the text.
    however when i say yes to second "&" the first one is getting changed.

    Please help....

  18. #18
    VBAX Master
    Joined
    Feb 2007
    Posts
    2,095
    Location
    I told you it was "top of head" and you may need to play with it BUT it doesn't do that for me

    See http://screencast.com/t/hHhfHj8jj
    John Wilson
    Microsoft PowerPoint MVP
    Amazing Free PowerPoint Tutorials
    http://www.pptalchemy.co.uk/powerpoi...tutorials.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •