dcsimg
Results 1 to 35 of 35

Thread: [RESOLVED] Need help writing some VB code

  1. #1

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Resolved [RESOLVED] Need help writing some VB code

    Hi, this is my first post so I don't know how it works yet.

    If someone could write/assist me on some code that would be great, I'm looking for the code to:

    Search a word document looking for "_id#" (the # is any number) and if it duplicates anywhere in the document to chance them both to "_id#(partial)".

    Any help would be great as I am stumped...

    Thanks

    Adam, (a.k.a YodaBadger)

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,510

    Re: Need help writing some VB code

    No need to fill things out in triplicate... in fact, that's about the worst thing to do here... scattershot posts get scattered shot answers... you'll get some on each one, but they don't have the benefit of what's in the others. It also just confuses people.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Ohk thanks,

    I didnt really know just wasnt sure where to put it.

    Thanks

  4. #4
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,240

    Re: Need help writing some VB code

    Welcome to VBForums

    I've removed the duplicates of this thread, and left the forum as "Office Development" (as that is arguably the best forum for this question), but in terms of getting an answer it would help to know which variation of VB you are using (eg: VB 2015, or the VB Editor inside Excel, etc).

  5. #5
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Quote Originally Posted by YodaBadger View Post
    If someone could write/assist me on some code that would be great...
    I am ready to assist but I need to see efforts from your side. If you are doing this from VBA Word then I hope you know that you can record a macro and then amend the code to suit your need?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  6. #6

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Quote Originally Posted by Siddharth Rout View Post
    I am ready to assist but I need to see efforts from your side. If you are doing this from VBA Word then I hope you know that you can record a macro and then amend the code to suit your need?
    I am ready to do what is needed to get the program done, I am not very good at programming but am doing it for a friend and would definitely like to learn more. It is done in VBA Word and I can record a macro but I dont know how to amend it to make it do what I need.

    Thanks

  7. #7
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,697

    Re: Need help writing some VB code

    Welcome to the Forums!

    Record the macro doing the task you need. Stop it and examine the code that was generated. If it doesnt do all you need can you post the generated code and explain what part of the task is still needing to be done. We can then assist you in making the changes needed.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Ohk will do thanks

  9. #9

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Sub Macro2()
    '
    ' Macro2 Macro
    '
    '
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
    .Text = "_id1"
    .Replacement.Text = "_id1(partial)"
    .Forward = True
    .Wrap = wdFindAsk
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    End Sub

    _____________________________________________________

    That is my current macro, I just need to make it do every possible integer without copying and pasting thousands of times and not allow it replace on the same _id.

    Thanks

  10. #10
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    That is a good start. Now the next thing. What kind of numbers are expected? "_id1", "_id2","_id3" etc? Is there a trend that we can see. If yes, then identify the lowest number and the highest number and then use that in a For loop to execute that macro code after editing it. You will also have to take care of false positives. for example "_id1" and "_id11"
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  11. #11

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Quote Originally Posted by Siddharth Rout View Post
    That is a good start. Now the next thing. What kind of numbers are expected? "_id1", "_id2","_id3" etc? Is there a trend that we can see. If yes, then identify the lowest number and the highest number and then use that in a For loop to execute that macro code after editing it. You will also have to take care of false positives. for example "_id1" and "_id11"
    It needs to be able to cope with all integers because there are some 7/8 digit numbers that are ids. I don't know all the exact numbers, would that be a problem?

  12. #12
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    How many numbers are there? If there are few numbers (say around 20) then you can put them in an array and then loop through the array.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  13. #13

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    I dont know the exact amount of numbers, it changes per document. I would need something that tried every possible number for 1-1000. Secondly I'm not great at coding and its my first time with VBA and I dont really understand how to "use that in a For loop to execute that macro code after editing it" or "put them in an array and then loop through the array". If you could explain the code that would be great.

    Thanks

  14. #14
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Can i see a sample document? You can zip it and upload it here
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  15. #15

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Hey, Sorry for the long wait. Had lots of other things to do, I have advanced my code so it works slightly differently which works for somethings but not everything.

    What I specifically need it to do now is detect whether is has one of the strings "HCI-" "ERL-" "MSRS-" and look for the string and detect the numbers after it and if the numbers of 2 or more in the document are the same then highlight both of them.

    E.g.
    HCI-10001
    HCI-15002
    HCI-15153
    HCI-10001
    ERL-14122
    ERL-15002
    ERL-10001
    MSRS-51112
    MSRS-55555
    MSRS-10101

    Where only the bold ones would get highlighted because they are exactly the same.

    Is there a way to do this?

    Thanks

    Code:
    Option Explicit
    
    Sub Sample()
    
        Dim MyArray() As String
        Dim n As Long, i As Long, j As Long
        Dim Col As New Collection
        Dim itm
        Dim found As Boolean
        
        Dim arr4
        arr4 = Array("HCI-", "MSRS-", "ERL-")
    
        n = 0
        '~~> Get all the sentences from the word document in an array
        For i = 1 To ActiveDocument.Words.Count
            n = n + 1
            ReDim Preserve MyArray(n)
            MyArray(n) = Trim(ActiveDocument.Words(i).Text)
            found = False
            For j = 0 To 2
                        If InStr(1, MyArray(n), arr4(j), vbTextCompare) Then
                        found = True
                    End If
            Next
            If (Not found) Then
                 MyArray(n) = ""
            End If
            
        Next
    
        '~~> Sort the array
        SortArray MyArray, 0, UBound(MyArray)
    
        '~~> Extract Duplicates
        For i = 1 To UBound(MyArray)
            If i = UBound(MyArray) Then Exit For
            If (InStr(1, MyArray(i + 1), MyArray(i), vbTextCompare)) Then
                        On Error Resume Next
                        Col.Add MyArray(i), """" & MyArray(i) & """"
                        On Error GoTo 0
                   
            End If
        Next i
    
        '~~> Highlight duplicates
        For Each itm In Col
            Selection.Find.ClearFormatting
            Selection.HomeKey wdStory, wdMove
            Selection.Find.Execute itm
            Do Until Selection.Find.found = False
                Selection.Range.HighlightColorIndex = wdPink
                Selection.Find.Execute
            Loop
        Next
    End Sub
    
    '~~> Sort the array
    Public Sub SortArray(vArray As Variant, i As Long, j As Long)
      Dim tmp As Variant, tmpSwap As Variant
      Dim ii As Long, jj As Long
    
      ii = i: jj = j: tmp = vArray((i + j) \ 2)
    
      While (ii <= jj)
         While (vArray(ii) < tmp And ii < j)
            ii = ii + 1
         Wend
         While (tmp < vArray(jj) And jj > i)
            jj = jj - 1
         Wend
         If (ii <= jj) Then
            tmpSwap = vArray(ii)
            vArray(ii) = vArray(jj): vArray(jj) = tmpSwap
            ii = ii + 1: jj = jj - 1
         End If
      Wend
      If (i < jj) Then SortArray vArray, i, jj
      If (ii < j) Then SortArray vArray, ii, j
    End Sub
    Last edited by Siddharth Rout; May 26th, 2017 at 03:32 AM. Reason: Added Code Tags

  16. #16
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    I guess you are replicating my stackoverflow post? That logic will not work here.

    Ok quick question. What should happen to these numbers. I mean what output are you expecting?

    Before

    HCI-10001
    HCI-15002
    HCI-15153
    HCI-10001
    ERL-14122
    ERL-15002
    ERL-10001
    MSRS-51112
    MSRS-55555
    MSRS-10101

    After

    ????
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  17. #17

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Ok quick question. What should happen to these numbers. I mean what output are you expecting?

    Before

    HCI-10001
    HCI-15002
    HCI-15153
    HCI-10001
    ERL-14122
    ERL-15002
    ERL-10001
    MSRS-51112
    MSRS-55555
    MSRS-10101

    After

    ????[/QUOTE]

    After:

    ID's that are duplicated should be highlighted but only exact copies

  18. #18
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Like this?

    HCI-10001
    HCI-15002
    HCI-15153
    HCI-10001
    ERL-14122
    ERL-15002
    ERL-10001
    MSRS-51112
    MSRS-55555
    MSRS-10101
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  19. #19

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    No, only the ID's that look exactly the same:

    HCI-10001
    HCI-15002
    HCI-15153
    HCI-10001
    ERL-14122
    ERL-15002
    ERL-10001
    MSRS-51112
    MSRS-55555
    MSRS-10101

    But I need to be able to type "MSRS-", "ERL-", "HCI-" and it does all possible numbers

  20. #20
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Ah I see.. So you want to type "HCI or ERL or MSRS" and it should highlight the duplicate for those?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  21. #21

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Yes but only on the ones that the numbers are the same afterwards

  22. #22
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Is this what you are trying?

    Code:
    Option Explicit
    
    Sub Sample()
        Dim MyArray() As String
        Dim n As Long, i As Long
        Dim Col As New Collection
        Dim itm
        Dim Ret
        
        Ret = InputBox("Please enter HCI or ERL or MSRS")
        
        If Ret = "" Then Exit Sub
        
        n = ActiveDocument.Sentences.Count
        ReDim Preserve MyArray(n)
        
        '~~> Get all the sentences from the word document in an array
        For i = 1 To ActiveDocument.Sentences.Count
            MyArray(i) = Trim(ActiveDocument.Sentences(i).Text)
        Next
    
        '~~> Sort the array
        SortArray MyArray, 0, UBound(MyArray)
    
        '~~> Extract Duplicates
        For i = 1 To UBound(MyArray) - 1
            If Left(MyArray(i + 1), 4) = Ret & "-" Or Left(MyArray(i + 1), 5) = Ret & "-" Then
                If InStr(1, MyArray(i + 1), MyArray(i), vbTextCompare) Then
                    On Error Resume Next
                    Col.Add MyArray(i), """" & MyArray(i) & """"
                    On Error GoTo 0
                End If
            End If
        Next i
    
        '~~> Highlight duplicates
        For Each itm In Col
            Selection.Find.ClearFormatting
            Selection.HomeKey wdStory, wdMove
            Selection.Find.Execute itm
            Do Until Selection.Find.Found = False
                Selection.Range.HighlightColorIndex = wdPink
                Selection.Find.Execute
            Loop
        Next
    End Sub
    
    '~~> Sort the array
    Public Sub SortArray(vArray As Variant, i As Long, j As Long)
      Dim tmp As Variant, tmpSwap As Variant
      Dim ii As Long, jj As Long
    
      ii = i: jj = j: tmp = vArray((i + j) \ 2)
    
      While (ii <= jj)
         While (vArray(ii) < tmp And ii < j)
            ii = ii + 1
         Wend
         While (tmp < vArray(jj) And jj > i)
            jj = jj - 1
         Wend
         If (ii <= jj) Then
            tmpSwap = vArray(ii)
            vArray(ii) = vArray(jj): vArray(jj) = tmpSwap
            ii = ii + 1: jj = jj - 1
         End If
      Wend
      If (i < jj) Then SortArray vArray, i, jj
      If (ii < j) Then SortArray vArray, ii, j
    End Sub
    Screenshot
    Name:  Sample.jpg
Views: 68
Size:  20.4 KB
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  23. #23

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Yes but I want to just click a button and it does all of the HCI/ERL/MSRS duplicates

  24. #24
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Quote Originally Posted by Siddharth Rout View Post
    Ah I see.. So you want to type "HCI or ERL or MSRS" and it should highlight the duplicate for those?
    Quote Originally Posted by YodaBadger View Post
    Yes but I want to just click a button and it does all of the HCI/ERL/MSRS duplicates
    I thought you wanted to choose?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  25. #25
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Simply remove the Inputbox and change the line

    Code:
    If Left(MyArray(i + 1), 4) = Ret & "-" Or Left(MyArray(i + 1), 5) = Ret & "-" Then
    to

    Code:
    If Left(MyArray(i + 1), 4) = "HCI-" Or Left(MyArray(i + 1), 4) = "ERL-" Or Left(MyArray(i + 1), 5) = "MSRS-" Then
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  26. #26

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    It changes per document but it works as this, Thanks alot of the help!

  27. #27
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Great. If your query is solved then feel free to mark the thread "Resolved". If you do not know how then see the link in my signature
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  28. #28

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Last queury before I close the thread, how can I stop it highlighting and make it "HCI-###### (partial)"

  29. #29
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    You mean just highlight the text or the number?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  30. #30

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    I want to add the word (partial) in brackets next to any duplicated IDs

  31. #31
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    Sorry was having Dinner. Replace

    Code:
        '~~> Highlight duplicates
        For Each itm In Col
            Selection.Find.ClearFormatting
            Selection.HomeKey wdStory, wdMove
            Selection.Find.Execute itm
            Do Until Selection.Find.Found = False
                Selection.Range.HighlightColorIndex = wdPink
                Selection.Find.Execute
            Loop
        Next
    with this

    Code:
        '~~> Highlight duplicates
        For Each itm In Col
            Selection.Find.ClearFormatting
            Selection.HomeKey wdStory, wdMove
            itm = Replace(itm, Chr(13), "")
            Selection.Find.Execute FindText:=itm, ReplaceWith:=itm & " (Partial)", Replace:=wdReplaceOne
            
            Do Until Selection.Find.Found = False
                Selection.Range.HighlightColorIndex = wdPink
                Selection.MoveRight Unit:=wdCharacter, Count:=1
                Selection.Find.Execute FindText:=itm, ReplaceWith:=itm & " (Partial)", Replace:=wdReplaceOne
            Loop
        Next
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  32. #32

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Is there any way of removing the Input box so it can be added only if needed? As in putting the code in a comment?

    How can I make the word "Partial" be in lowercase?

    Thanks

  33. #33
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    >>>Is there any way of removing the Input box so it can be added only if needed? As in putting the code in a comment?

    Yes put a single quote before it

    Code:
    'This is a commented code
    >>>How can I make the word "Partial" be in lowercase?

    By typing in lowercase in the code
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  34. #34

    Thread Starter
    Junior Member
    Join Date
    May 2017
    Posts
    20

    Re: Need help writing some VB code

    Quote Originally Posted by Siddharth Rout View Post
    >>>Is there any way of removing the Input box so it can be added only if needed? As in putting the code in a comment?

    Yes put a single quote before it

    Code:
    'This is a commented code
    >>>How can I make the word "Partial" be in lowercase?

    By typing in lowercase in the code
    I tried putting a single quote before it and it didn't work atall, I think it still needs an input.

    The word is in lowercase but its pasting in caps

  35. #35
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: Need help writing some VB code

    You seemed to have missed what I mentioned in post #25?

    Is this what you are trying? I fixed the Caps issue as well.

    Code:
    Option Explicit
    
    Sub Sample()
        Dim MyArray() As String
        Dim n As Long, i As Long
        Dim Col As New Collection
        Dim itm
        Dim Ret
        
        n = ActiveDocument.Sentences.Count
        ReDim Preserve MyArray(n)
        
        '~~> Get all the sentences from the word document in an array
        For i = 1 To ActiveDocument.Sentences.Count
            MyArray(i) = Trim(ActiveDocument.Sentences(i).Text)
        Next
    
        '~~> Sort the array
        SortArray MyArray, 0, UBound(MyArray)
    
        '~~> Extract Duplicates
        For i = 1 To UBound(MyArray) - 1
            If Left(MyArray(i + 1), 4) = "HCI-" Or Left(MyArray(i + 1), 4) = "ERL-" Or Left(MyArray(i + 1), 5) = "MSRS-" Then
                If InStr(1, MyArray(i + 1), MyArray(i), vbTextCompare) Then
                    On Error Resume Next
                    Col.Add MyArray(i), """" & MyArray(i) & """"
                    On Error GoTo 0
                End If
            End If
        Next i
    
        '~~> Highlight duplicates
        For Each itm In Col
            Selection.Find.ClearFormatting
            Selection.HomeKey wdStory, wdMove
            itm = Replace(itm, Chr(13), "")
            Selection.Find.Execute FindText:=itm, MatchCase:=True, ReplaceWith:=itm & " (Partial)"
            
            Do Until Selection.Find.Found = False
                Selection.Range.HighlightColorIndex = wdPink
                Selection.MoveRight Unit:=wdCharacter, Count:=1
                Selection.Find.Execute FindText:=itm, MatchCase:=True, ReplaceWith:=itm & " (Partial)"
            Loop
        Next
    End Sub
    
    '~~> Sort the array
    Public Sub SortArray(vArray As Variant, i As Long, j As Long)
      Dim tmp As Variant, tmpSwap As Variant
      Dim ii As Long, jj As Long
    
      ii = i: jj = j: tmp = vArray((i + j) \ 2)
    
      While (ii <= jj)
         While (vArray(ii) < tmp And ii < j)
            ii = ii + 1
         Wend
         While (tmp < vArray(jj) And jj > i)
            jj = jj - 1
         Wend
         If (ii <= jj) Then
            tmpSwap = vArray(ii)
            vArray(ii) = vArray(jj): vArray(jj) = tmpSwap
            ii = ii + 1: jj = jj - 1
         End If
      Wend
      If (i < jj) Then SortArray vArray, i, jj
      If (ii < j) Then SortArray vArray, ii, j
    End Sub
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width