Results 1 to 4 of 4

Thread: [RESOLVED] Programatically Change Number Values In A String

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Resolved [RESOLVED] Programatically Change Number Values In A String

    I am using VS 2008, and coding in VB.Net for an Excel 2007 Add-In.

    I am looping through a range of cells and programatically changing the value within the cell. I have the code to accomplish this when the original value in the cell is a single number....
    Code:
    Dim rng As Range
    Dim cel As Range
    
    rng = ActiveSheet.Range("A1:A10").Cells
    
    For Each cel In rng
        cel.Value = cel.Value + 10
    Next cel
    My problem arises when the original value of the cell is a string like this: 16 - 18

    I am pretty sure I am going to have to add IF - Then - Else to this loop. But, I can't seem to figure out how to progamatically find and change the numeric values in this cell to: 26 - 28
    Last edited by fdegree; Apr 10th, 2021 at 11:34 AM.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Programatically Change Number Values In A String

    if that is the only string type then you could split the string on the "-" and add the +10 to both elements of the array, but if the strings could contain other characters you would need to loop through all the character to find numeric values within to add the increment to

    if it is the former you can try like
    For Each cel In Range("a1:a6")
    a = Split(cel, "-")
    For i = 0 To UBound(a)
    a(i) = a(i) + 10
    Next
    cel.Value = "'" & Join(a, " - ")
    Next
    i have tested this and it will work with any complete value or strings like 6-10 or 6 - 10
    to assign the string values to cells i have forced the values to strings else excel may try to force it to a date or calculate then as values eg 16 - 20 could be seen as -4 or 6 - 10 could be seen as a date value
    if your excel does not do this (mine does) you can delete the "'" & from cel.value =
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2012
    Posts
    87

    Re: Programatically Change Number Values In A String

    Thank you for that suggestion. It looks like it should work. But, after a lot of research, I learned about Regular Expression (regex) and created this code, where the Rnge was defined earlier in the code:
    Code:
           For Each cel In Rnge
    
                If IsNumeric(cel.Value) Then
                    cel.Value = cel.Value + 10
                Else
                    'If the cell value is a string because of the "-", this code finds the numbers in the cell
                    'and adds 10 to them, then places the new numbers back into the cell
                    int1 = 0
                    int2 = 0
                    Dim x As String = cel.Value
                    int1 = (Integer.Parse(Regex.Replace(x, "(?<=\s)\w+", "")))
                    int2 = (Integer.Parse(Regex.Replace(x, "\w+(?=\s)", "")))
                    cel.Value = int1 + 10 & " - " & int2 + 10
                End If
    
            Next cel
    This is my first attempt at regex. So, maybe this isn't the best approach. Or, perhaps the regex code could be improved. I'm not sure about any of that, but this is working for me.

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    3

    Re: Programatically Change Number Values In A String

    Code:
    Sub test()
        Dim rng As Range
        Dim cel As Range
        Set rng = ActiveSheet.Range("A1:A10")
        For Each cel In rng
            If IsNumeric(cel.Value) Then
                cel.Value = cel.Value + 10
            Else
                a = Split(cel, "-")
                a(0) = a(0) + 10: a(1) = a(1) + 10
                cel = Join(a, "-")
            End If
        Next cel
    End Sub

Posting Permissions

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



Click Here to Expand Forum to Full Width