stenlake1
07-06-2007, 05:20 AM
I have the following code (many thanks xld).
Public Sub ProcessData()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim iLastRow As Long
Dim ans As Variant
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1, TEST_COLUMN).Value Then
.Cells(i, "K").Formula = "=(RC2-R[-1]C2)*" & ans & "/1000"
ElseIf .Cells(i, TEST_COLUMN).Value = .Cells(i + 1, TEST_COLUMN).Value Then
.Cells(i, TEST_COLUMN).Select
ans = InputBox("What is the SP spacing for line " & .Cells(i, TEST_COLUMN).Value & " ?")
If ans = "" Then
If MsgBox("Continue and skip this line (OK), or quit (Cancel)?", vbOKCancel) = vbCancel Then
Exit For
Else
Do
i = i + 1
Loop Until .Cells(i, TEST_COLUMN).Value <> .Cells(i - 1, TEST_COLUMN).Value
End If
End If
End If
Next i
End With
End Sub
It basically scrolls through some blocks of values, asking the user for a value every time the value in the cell changes. Well I would like to put an option in at the beginning asking the user if they would like the “SP spacing” to be the same for every value. If they click yes, the user is prompted for a value which then applies to all the blocks. If they click no, they are taken through each block (as the current script is written).
Anybody help?
Public Sub ProcessData()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim iLastRow As Long
Dim ans As Variant
With ActiveSheet
iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 2 To iLastRow
If .Cells(i, TEST_COLUMN).Value = .Cells(i - 1, TEST_COLUMN).Value Then
.Cells(i, "K").Formula = "=(RC2-R[-1]C2)*" & ans & "/1000"
ElseIf .Cells(i, TEST_COLUMN).Value = .Cells(i + 1, TEST_COLUMN).Value Then
.Cells(i, TEST_COLUMN).Select
ans = InputBox("What is the SP spacing for line " & .Cells(i, TEST_COLUMN).Value & " ?")
If ans = "" Then
If MsgBox("Continue and skip this line (OK), or quit (Cancel)?", vbOKCancel) = vbCancel Then
Exit For
Else
Do
i = i + 1
Loop Until .Cells(i, TEST_COLUMN).Value <> .Cells(i - 1, TEST_COLUMN).Value
End If
End If
End If
Next i
End With
End Sub
It basically scrolls through some blocks of values, asking the user for a value every time the value in the cell changes. Well I would like to put an option in at the beginning asking the user if they would like the “SP spacing” to be the same for every value. If they click yes, the user is prompted for a value which then applies to all the blocks. If they click no, they are taken through each block (as the current script is written).
Anybody help?