PDA

View Full Version : Rounding numbers with user defined increment



stenlake1
06-15-2007, 05:47 AM
Hi all,

I have the following script which was kindly given to me yesterday:

Public Sub ProcessData()
Const TEST_COLUMN As String = "C"
Dim i As Double
Dim j As Long
Dim iLastRow As Long
Dim nStart As Double
Dim nEnd As Double
Dim nIncrement As Double

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
nStart = .Cells(2, TEST_COLUMN).Value
nEnd = .Cells(iLastRow, TEST_COLUMN).Value
If IsNumeric(nStart) And IsNumeric(nEnd) Then

nIncrement = InputBox("How many shot points per trace?")
If IsNumeric(nIncrement) Then

j = iLastRow + 1
For i = nStart To nEnd Step nIncrement

.Cells(j, TEST_COLUMN).Value = i
j = j + 1

Next i
Else

MsgBox "Invalid Step value"
End If
Else

MsgBox "Invalid start/end value"
End If
End With

End Sub


I would like to add a part to it where all the numbers in the "Space" (see attached spreadsheet) column are rounded to the nearerst nincrement (user defined). So for example if nincrement = 0.5, then "C6" would be rounded from 81.9 to 82 (i.e. to the nearest 0.5). And then in the same cell to replace 81.9 to 82 - is this possible?

Many thanks for your help :)

Chris

Bob Phillips
06-15-2007, 06:05 AM
That is exactly what that codes does. The user is asked to supply the increment, so they supply any number.

stenlake1
06-15-2007, 06:12 AM
Thank you xld but the current script creates a list below. I also want to use the increment to round any numbers above it - in this case to the nearest 0.5 (e.g. 81.9 to 82) and replace them in the same cell. Does that make sense?

Bob Phillips
06-15-2007, 06:16 AM
No it doesn't, because the numbers above are not step incremnents, so is 81.9 changed to 80.5 or 82 or what?

stenlake1
06-15-2007, 06:21 AM
Sorry, I am confusing things. Say the user decideds on an increment of 0.25.

and say the black numbers at the top are:

24.1
25.6
27.7

With the script you wrote yesterday the list would go...

24
24.25
24.5
24.75
25
25.25
25.5
25.75
etc

Basically I want 24.1, 25.6 and 27.7 to be rounded to the nearest 0.25. So... 24.1 would become 24, 25.6 would become 25.5 and 27.7 would become 27.75. I basically want excel to round my original numbers to the nearest increment (as defined by the user).

I hope this makes it clearer? :(

Bob Phillips
06-15-2007, 07:49 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "C"
Dim i As Double
Dim j As Long
Dim iLastRow As Long
Dim nStart As Double
Dim nEnd As Double
Dim nIncrement As Double

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
nStart = .Cells(2, TEST_COLUMN).Value
nEnd = .Cells(iLastRow, TEST_COLUMN).Value
If IsNumeric(nStart) And IsNumeric(nEnd) Then

nIncrement = InputBox("How many shot points per trace?")
If IsNumeric(nIncrement) Then

j = 2
For i = Round(nStart / nIncrement, 0) * nIncrement To _
Round(nEnd / nIncrement, 0) * nIncrement Step nIncrement

.Cells(j, TEST_COLUMN).Value = i
j = j + 1

Next i
Else

MsgBox "Invalid Step value"
End If
Else

MsgBox "Invalid start/end value"
End If
End With
End Sub

MrRhodes2004
12-27-2007, 10:10 AM
This is the way that I have solved rounding to a particular fraction:

Function Rnd2Frac(Number2Round, Optional Fraction_Number As Integer = 4, Optional Round_Direction As Byte)
Dim lInputNo
Dim iIntInput
Dim lRemainder, lNewRemndr
lInputNo = Number2Round
iIntInput = Int(lInputNo)
lRemainder = lInputNo - iIntInput
If Round_Direction = 1 Then
Debug.Print "up"
lNewRemndr = Application.WorksheetFunction.Ceiling(lRemainder * Fraction_Number, 1) / Fraction_Number

ElseIf Round_Direction = 0 Then
Debug.Print "down"
lNewRemndr = Application.WorksheetFunction.Floor(lRemainder * Fraction_Number, 1) / Fraction_Number


ElseIf Round_Direction = 2 Then
Debug.Print "near"
lNewRemndr = Round(lRemainder * Fraction_Number, 0) / Fraction_Number
End If
Rnd2Frac = (iIntInput + lNewRemndr)

End Function

What do you think? Will this work?