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
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