VBA: If number found in column B:C in excel sheet reduce numbers by -1

[ucan]

Please help me with a VBA: If number found in column B:C in excel sheet reduce numbers by -1
Input:
(5 - 9)
(19 - 23)


apple m(5 - 9)w


apple u(19 - 23)u
Output:
(4 - 8)
(18 - 22)


apple m(4 - 8)w

apple u(18 - 22)u

Thanks in advance

[snb]

Sub M_guesswhat()
cells.replace 5, 4
End Sub

[georgiboy]

Maybe something like the test sub below:


Sub test()
Dim rCell As Range, tmpVal As Variant

For Each rCell In Range("B2:C" & Range("B" & Rows.Count).End(xlUp).Row).Cells
tmpVal = Split(Application.Trim(GetNumeric(rCell.Value)))
For x = 0 To UBound(tmpVal)
rCell.Replace tmpVal(x), tmpVal(x) - 1
Next x
Next rCell
End Sub


Function GetNumeric(CellRef As String)
Dim StringLength As Integer

StringLength = Len(CellRef)
For i = 1 To StringLength
Do While IsNumeric(Mid(CellRef, i + a, 1))
Result = Result & Mid(CellRef, i + a, 1)
a = a + 1
Loop
Result = Result & " "
Next i
GetNumeric = Result
End Function

Again there will always be other ways to do this, hope it helps.

[ucan]

Thank you. Worked perfectly. Only replaced all the + with -


Maybe something like the test sub below:


Sub test()
Dim rCell As Range, tmpVal As Variant

For Each rCell In Range("B2:C" & Range("B" & Rows.Count).End(xlUp).Row).Cells
tmpVal = Split(Application.Trim(GetNumeric(rCell.Value)))
For x = 0 To UBound(tmpVal)
rCell.Replace tmpVal(x), tmpVal(x) - 1
Next x
Next rCell
End Sub


Function GetNumeric(CellRef As String)
Dim StringLength As Integer

StringLength = Len(CellRef)
For i = 1 To StringLength
Do While IsNumeric(Mid(CellRef, i + a, 1))
Result = Result & Mid(CellRef, i + a, 1)
a = a + 1
Loop
Result = Result & " "
Next i
GetNumeric = Result
End Function

Again there will always be other ways to do this, hope it helps.