[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 571
  • Last Modified:

Increment specific value in record

I have the need to locate all the instances of a certain value in a record with a file and increment that value

Example of record is SE*30*SOMETHING~

Below I know how to find the record and do simple replace functions, but cannot figure out how to increment the value. In this case I would want to increment the value 30 by 3.

Below is code I have written to do simple find and replace on the record string.  Can this be modified to increment the value immediately following the "SE*" ?

'With ActiveDocument.Range.Find
'        .MatchWildcards = True
'        .Text = "\SE(\*[0-9]{2})\*[0-9A-z]{1,50}"
'        .Replacement.Text = "SE\*30\*2"
'        .Execute Replace:=wdReplaceAll
'End With
  • 5
  • 4
1 Solution
Try the following code to see if works for you:
Dim p As Long
Dim strRecord As String

'set focus to start of document
Selection.HomeKey wdStory

'initialize find
With Selection.Find
    .Text = "\SE(\*[0-9]{2})\*[0-9A-z]{1,50}"
    .Replacement.Text = ""
    .Forward = True
    .Wrap = wdFindStop              'stop find when end of document reached
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchAllWordForms = False
    .MatchSoundsLike = False
    .MatchWildcards = True
    'perform the search
    'while records are found
    While .Found
        'increment the found record value by 30
        strRecord = Selection.Range.Text
        strRecord = Left(strRecord, 3) & Trim(Str(Val(Mid(strRecord, 4, InStrRev(strRecord, "*") - 4) + 30))) & Mid(strRecord, InStrRev(strRecord, "*"))
        'replace the found text with the revised text
        Selection.Range = strRecord
        'set the postion to the end of the revised text
        p = Selection.Range.Start + Len(strRecord) + 1
        ActiveDocument.Range(p, p).Select
        'perform the search again
End With

Open in new window

66chawgerAuthor Commented:

For the line below, does the 30 represent the value "30" I had in my original example for the SE record?  If so, just want to clarify that this value can be 1-99.

strRecord = Left(strRecord, 3) & Trim(Str(Val(Mid(strRecord, 4, InStrRev(strRecord, "*") - 4) + 30))) & Mid(strRecord, InStrRev(strRecord, "*"))
No, the 30 represents the value increment.
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

66chawgerAuthor Commented:
Ok, thanks, just needed to change that to a 3.  Worked great!
66chawgerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for 66chawger's comment #a38369691

for the following reason:

Worked great, fast solution, excellent comments in the code example.
Glad to hear it worked for you...though, I'm not sure why you'd close the question out via accepting your comment as the solution as apposed to my comment http:#a38369469
I'd recommend accepting comment http:#a38369469 as the the answer...notwithstanding that the coded solution used an increment of 30 instead of 3 (due to me misreading the question)
66chawgerAuthor Commented:
Sorry, I hope this gives you the points.  This was my intention... my apologies
It's okay, oversights can happen...thanks!

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now