Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Speedy Routine (xl2000)

    I modified a procedure that Mr. Walkenbach used in the Microsoft Excel 2000 Bible. I would appreciate anyone reviewing this procedure to see if there is anything that I can do programmatically to reduce the duration time of this procedure. Thanks in advance ... Brent
    <pre>Sub AGCDataValidation()
    '-- Procedure to validate "schedule = actual flag" based upon final schedule load.
    '... declare variables
    Dim VRange As Range, cell As Range

    '... set variable
    Set VRange = Range("W3:W4467")

    '... error handler
    On Error GoTo AGCerr

    '... check each cell value
    For Each cell In VRange
    If Union(cell, VRange).Address = VRange.Address Then

    '... 0 = off AGC or not available for control
    If cell = 0 Then
    '... move to final schedule load for check
    cell.Offset(0, 3).Select
    '... if load good for AGC adjust flag cell to reflect unit state
    If ActiveCell.Value > 70 / 6 Then
    cell.Value = "1"
    End If
    GoTo here
    End If

    '... 1 = on AGC or available for control
    If cell = 1 Then
    '... move to final schedule load for check
    cell.Offset(0, 3).Select
    '... if load not good for AGC adjust flag cell to reflect unit state
    If ActiveCell.Value < 70 / 6 Then
    'cell.Offset(0, -3).Select
    cell.Value = "0"
    End If
    GoTo here
    End If
    End If
    here:
    Next cell
    Exit Sub

    '... error handler
    AGCerr:
    MsgBox "The error number is: " & Err.Number & _
    vbCrLf & vbCrLf & "The error is: " & Err.Description
    Resume Next
    End Sub
    </pre>


  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Speedy Routine (xl2000)

    Unless there is something that I don't understand, the "If Union(..." statement in that code does not appear to do anything since the comparison should always be true. The code also does a lot of selecting of cells that is not necessary. I believe that the following code does the same thing and should be faster:

    <pre>Sub AGCDataValidation()
    '-- Procedure to validate "schedule = actual flag" based upon final schedule load.
    '... declare variables
    Dim cell As Range

    '... error handler
    On Error GoTo AGCerr

    '... check each cell value
    For Each cell In Range("W3:W4467")
    With cell
    '... 0 = off AGC or not available for control
    If .Value = 0 Then
    '... if load good for AGC adjust flag cell to reflect unit state
    If .Offset(0, 3).Value > 70 / 6 Then
    .Value = "1"
    End If
    Else

    '... 1 = on AGC or available for control
    If .Value = 1 Then
    '... if load not good for AGC adjust flag cell to reflect unit state
    If .Offset(0, 3).Value < 70 / 6 Then
    cell.Value = "0"
    End If
    End If
    End If
    End With
    Next cell
    Exit Sub

    '... error handler
    AGCerr:
    MsgBox "The error number is: " & Err.Number & _
    vbCrLf & vbCrLf & "The error is: " & Err.Description
    Resume Next
    End Sub
    </pre>

    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Speedy Routine (xl2000)

    Great! Thank you very much for your time and response. I will try this out.

    Brent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •