PDA

View Full Version : Cutting a loop short...



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?

mdmackillop
07-06-2007, 05:41 AM
When you post code, please select it and click the VBA button. It makes it more readable

stenlake1
07-06-2007, 05:43 AM
Ok, sorry - I apologise

Bob Phillips
07-06-2007, 05:44 AM
Public Sub ProcessData()
Const TEST_COLUMN As String = "A"
Dim i As Long
Dim iLastRow As Long
Dim BigAns As Variant
Dim ans As Variant

With ActiveSheet

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
BigAns = InputBox("Supply an overall SP spacing for all lines " & vbNewLine & _
"or Cancel to get individual prompts")
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
If BigAns <> "" Then
ans = BigAns
Else
ans = InputBox("What is the SP spacing for line " & .Cells(i, TEST_COLUMN).Value & " ?")
End If
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