-
Apr 10th, 2021, 10:35 AM
#1
Thread Starter
Lively Member
[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.
-
Apr 11th, 2021, 02:00 AM
#2
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
-
Apr 11th, 2021, 08:40 AM
#3
Thread Starter
Lively Member
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.
-
Apr 13th, 2021, 05:24 AM
#4
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|