View Full Version : [SOLVED:] VBA: If number found in column B:C in excel sheet reduce numbers by -1
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
Sub M_guesswhat()
cells.replace 5, 4
End Sub
georgiboy
03-08-2022, 09:05 AM
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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.