Hi
I have attached code below, I need help with modifying the same,
This code checks whether Range has formulas in it, if range does not have formulas, it exists but if it does have formulas, it does
Adds Rounup function with "" which is invalid (I want to remove that step)
secondly if Range/cells has Round with "" at end, it will change it to Round "0" which is correct and valid roundup function
and lastly it removes round up function completed keeping the formula as it was before wrapping them into round up
As mentioned above, I want to remove invalid wrapping of roundup function with "" and keep test only to Roundup to 0 or remove round up if roundup already exists in range or cells
Also How can I modify the function to round up by 1000 or 1000000 (by input box popup) and change it back to normal formula as it was...?
Thanks
Sub RoundupCells()
'
'PURPOSE: Add an ROUNDUP() Function around all the selected cells' formulas. _
Also handles if ROUNDUP is already wrapped around formula.
Dim Rng As Range
Dim cell As Range
Dim AlreadyROUNDUP As Boolean
Dim RemoveROUNDUP As Boolean
Dim TestEnd1 As String
Dim TestEnd2 As String
Dim TestStart As String
Dim MyFormula As String
Dim x As String
'Determine if a single cell or range is selected
If Selection.Cells.Count = 1 Then
Set Rng = Selection
If Not Rng.HasFormula Then GoTo NoFormulas
Else
'Get Range of Cells that Only Contain Formulas
On Error GoTo NoFormulas
Set Rng = Selection.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
End If
'Get formula from First cell in Selected Range
MyFormula = Rng(1, 1).Formula
'Create Test Strings To Determine if ROUNDUP formula has already been added
TestEnd1 = Chr(34) & Chr(34) & ")"
TestEnd2 = ",0)"
TestStart = Left(MyFormula, 9)
'Determine How we want to modify formula
If Right(MyFormula, 3) = TestEnd1 And TestStart = "=ROUNDUP(" Then
Beg_String = ""
End_String = "0)" '=ROUNDUP([formula],0)
AlreadyROUNDUP = True
ElseIf Right(MyFormula, 3) = ",0)" And TestStart = "=ROUNDUP(" Then
RemoveROUNDUP = True
Else
Beg_String = "=ROUNDUP("
End_String = "," & Chr(34) & Chr(34) & ")" '=ROUNDUP([formula],"")
AlreadyROUNDUP = False
End If
'Loop Through Each Cell in Range and modify formula
For Each cell In Rng.Cells
x = cell.Formula
If RemoveROUNDUP = True Then
cell = "=" & Mid(x, 10, Len(x) - 12) 'Removes Rounupformula if cell has rounup function
ElseIf AlreadyROUNDUP = False Then
cell = Beg_String & Right(x, Len(x) - 1) & End_String 'Adds =ROUNDUP([formula],"")
Else
cell = Left(x, Len(x) - 3) & End_String 'Adds =ROUNDUP([formula],0)
End If
Next cell
Exit Sub
'Error Handler
NoFormulas:
MsgBox "There were no formulas found in your selection!"
End Sub