# vba- round then convert number

#### bloodybrit90

##### Board Regular
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.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
MyVal = "0.2"
SolverOk SetCell:=MySet, MaxMinVal:=3, ValueOf:=MyVal, ByChange:=MyChange
SolverSolve UserFinish:=True

End If
Next cp
Application.ScreenUpdating = False
End Sub

Any ideas?

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

#### Rosen

##### Active Member
Try this.
Code:
``````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

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

#### Rosen

##### Active Member
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.

Code:
``````Sub MultiSolve()
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
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.ScreenUpdating = False
End Sub``````

1,101,846
Messages
5,483,276
Members
407,390
Latest member
jenniferjohns

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...