Increment specific value in record

Posted on 2012-09-05
Last Modified: 2012-09-07
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
Question by:66chawger
    LVL 23

    Accepted 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


    Author Comment


    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, "*"))
    LVL 23

    Expert Comment

    No, the 30 represents the value increment.

    Author Comment

    Ok, thanks, just needed to change that to a 3.  Worked great!

    Author Comment

    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.
    LVL 23

    Expert Comment

    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
    LVL 23

    Expert Comment

    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)

    Author Closing Comment

    Sorry, I hope this gives you the points.  This was my intention... my apologies
    LVL 23

    Expert Comment

    It's okay, oversights can happen...thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    Title # Comments Views Activity
    Excel Formula 6 51
    Excel scrolling very slow 9 35
    How to highlight an Excel graph lines crossing? 9 28
    Excel 2010 question 3 23
    Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
    Outlook Free & Paid Tools
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now