Pedro
asked on
variable replacement
In the code below I attempted to replace the fact that it 'looks' for a value in a cell with an input box that requests the value and all I got was an error message.
'xRun = Range("I1") replaced with
Let xRun = Application.InputBox("Mx Number of 'combinations' ", Type:=1)
I'd appreciate it if someone can properly edit the code so that it works as intended.
'xRun = Range("I1") replaced with
Let xRun = Application.InputBox("Mx Number of 'combinations' ", Type:=1)
I'd appreciate it if someone can properly edit the code so that it works as intended.
Sub Drop_Runs()
Dim i As Long
Dim j As Long
Dim xLast_Row As Long
Dim xResponse As Long
Dim xRun As Long
Dim xWork As Long
Dim xArray As Variant
'xRun = Range("I1")
Let xRun = Application.InputBox("Mx Number of 'combinations' ", Type:=1)
If xRun < 1 Or xRun > 3 Then
MsgBox ("Invalid ""Run"" (" & xRun & ") - run cancelled.")
Exit Sub
End If
xLast_Row = ActiveSheet.UsedRange.Cells(1, 1).Row + ActiveSheet.UsedRange.Rows.Count - 1
If xLast_Row < 18 Then
MsgBox ("No data found - run cancelled.")
Exit Sub
End If
xResponse = MsgBox("About to delete rows with runs > " & xRun & "." _
& Chr(10) & Chr(10) & "('OK' to confirm, 'Cancel' to go into Debug.)", vbOKCancel, "Drop Runs")
If xResponse = 2 Then
MsgBox ("Used cancelled run.")
Exit Sub
End If
Debug.Print Now()
Application.ScreenUpdating = False
xArray = Range("B1:F" & xLast_Row)
For i = xLast_Row To 18 Step -1
xWork = 0
For j = 2 To 5
If xArray(i, j) = xArray(i, j - 1) + 1 Then xWork = xWork + 1
Next
If xWork > xRun Then xArray(i, 1) = "XX DELETE XX" 'Cells(i, 1).EntireRow.Delete
Next
Range("B1:F" & xLast_Row) = xArray
Application.ScreenUpdating = True
Debug.Print Now()
MsgBox ("Run complete.")
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think it's basically a deprecated term:
http://msdn.microsoft.com/en-us/library/aa243390(v=vs.60).aspx
It's not required for variable assignment.
Rob.
http://msdn.microsoft.com/en-us/library/aa243390(v=vs.60).aspx
It's not required for variable assignment.
Rob.
ASKER
Excellent
ASKER