-
May 24th, 2017, 09:22 AM
#1
Thread Starter
Junior Member
[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)
-
May 24th, 2017, 09:27 AM
#2
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
-
May 24th, 2017, 09:38 AM
#3
Thread Starter
Junior Member
Re: Need help writing some VB code
Ohk thanks,
I didnt really know just wasnt sure where to put it.
Thanks
-
May 24th, 2017, 11:02 AM
#4
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).
-
May 24th, 2017, 01:26 PM
#5
Re: Need help writing some VB code
Originally Posted by YodaBadger
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 24th, 2017, 03:28 PM
#6
Thread Starter
Junior Member
Re: Need help writing some VB code
Originally Posted by Siddharth Rout
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
-
May 24th, 2017, 05:53 PM
#7
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 Posts • VS.NET on Vista • Multiple .NET Framework Versions • Office Primary Interop Assemblies • VB/Office Guru™ Word SpellChecker™.NET • VB/Office Guru™ Word SpellChecker™ VB6 • VB.NET Attributes Ex. • Outlook Global Address List • API 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
-
May 25th, 2017, 03:01 AM
#8
Thread Starter
Junior Member
Re: Need help writing some VB code
-
May 25th, 2017, 03:05 AM
#9
Thread Starter
Junior Member
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
-
May 25th, 2017, 04:51 AM
#10
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 25th, 2017, 05:12 AM
#11
Thread Starter
Junior Member
Re: Need help writing some VB code
Originally Posted by Siddharth Rout
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?
-
May 25th, 2017, 05:27 AM
#12
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 25th, 2017, 05:49 AM
#13
Thread Starter
Junior Member
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
-
May 25th, 2017, 10:09 AM
#14
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 03:10 AM
#15
Thread Starter
Junior Member
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
-
May 26th, 2017, 03:31 AM
#16
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 07:00 AM
#17
Thread Starter
Junior Member
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
-
May 26th, 2017, 08:54 AM
#18
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 09:38 AM
#19
Thread Starter
Junior Member
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
-
May 26th, 2017, 09:45 AM
#20
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 09:57 AM
#21
Thread Starter
Junior Member
Re: Need help writing some VB code
Yes but only on the ones that the numbers are the same afterwards
-
May 26th, 2017, 10:01 AM
#22
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
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 10:06 AM
#23
Thread Starter
Junior Member
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
-
May 26th, 2017, 10:12 AM
#24
Re: Need help writing some VB code
Originally Posted by Siddharth Rout
Ah I see.. So you want to type "HCI or ERL or MSRS" and it should highlight the duplicate for those?
Originally Posted by YodaBadger
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 10:14 AM
#25
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 10:14 AM
#26
Thread Starter
Junior Member
Re: Need help writing some VB code
It changes per document but it works as this, Thanks alot of the help!
-
May 26th, 2017, 10:16 AM
#27
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 10:17 AM
#28
Thread Starter
Junior Member
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)"
-
May 26th, 2017, 10:29 AM
#29
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 10:55 AM
#30
Thread Starter
Junior Member
Re: Need help writing some VB code
I want to add the word (partial) in brackets next to any duplicated IDs
-
May 26th, 2017, 12:09 PM
#31
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 12:36 PM
#32
Thread Starter
Junior Member
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
-
May 26th, 2017, 12:40 PM
#33
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
May 26th, 2017, 12:42 PM
#34
Thread Starter
Junior Member
Re: Need help writing some VB code
Originally Posted by Siddharth Rout
>>>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
-
May 26th, 2017, 12:50 PM
#35
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
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|