vba- round then convert number

[bloodybrit90]

Hey,

the below code uses Solver determine to determine what a number needs to be in order to make 20%. It works well, however after the macro run I need it to round to the nearest whole number then convert it to end in 990.

Example.

From 154,859.054548
To 154,990

Sub MultiSolve()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set MyTestRange = Range("C5")
For cp = 0 To 8 'gives column offsets C to K
If MyTestRange.Offset(0, cp).Value >= 0 Then
MySet = MyTestRange.Offset(53, cp).Address
MyChange = MyTestRange.Offset(11, cp).Address
MyVal = "0.2"
SolverOk SetCell:=MySet, MaxMinVal:=3, ValueOf:=MyVal, ByChange:=MyChange
SolverSolve UserFinish:=True

End If
Next cp
Application.DisplayAlerts = True
Application.ScreenUpdating = False
End Sub


Any ideas?

 

[Rosen]

Try this.

Function NumberConverterX(Value As Double) As Long
    Dim sValue As String: sValue = CStr(Value)
    Dim nPos As Long: nPos = Strings.InStr(1, sValue, ".")
    If nPos > 0 Then sValue = Strings.Left(sValue, nPos - 1)
    If Strings.Len(sValue) > 3 Then
        sValue = Strings.Left(sValue, Strings.Len(sValue) - 3)
        sValue = sValue & "990"
        NumberConverterX = CLng(sValue)
    Else
        NumberConverterX = 990
    End If
End Function

I tested in on the supplied number and it worked, but you might want to test it with other numbers.

Hope that helps!

 

[bloodybrit90]

How could i combine both of these to run after the prior page

 

[Rosen]

Unfortunately I am not familier with Solver, assuming that MySet is the location of the data output. You could picket up after running Solver and adjust it.

Sub MultiSolve()
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Set MyTestRange = Range("C5")
    For cp = 0 To 8 'gives column offsets C to K
       If MyTestRange.Offset(0, cp).Value >= 0 Then
           MySet = MyTestRange.Offset(53, cp).Address
           MyChange = MyTestRange.Offset(11, cp).Address
           MyVal = "0.2"
           SolverOk SetCell:=MySet, MaxMinVal:=3, ValueOf:=MyVal, ByChange:=MyChange
           SolverSolve UserFinish:=True
           Range(MySet).Value = NumberConverterX(Range(MySet).Value)
        End If
    Next cp
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False
End Sub