Results 1 to 2 of 2
  1. #1
    snax500
    Guest

    Combining tests in Excel VBA

    In Excel2000, I have the following macro:

    Dim cell, sls, cell2
    Application.ScreenUpdating = False
    For Each cell In Range("b33:b580")
    If cell.Value Like "*Sales*" Then
    sls = Application.Sum(Range(cell.Offset(0, 1), cell.Offset(0, 10)))
    If IsError(sls) = True Then
    Range(cell.Offset(-2, 0), cell.Offset(3, 0)).EntireRow.Hidden = True
    End If
    End If
    Next
    For Each cell2 In Range("b33:b580")
    If Not cell2.EntireRow.Hidden = True Then
    If cell2.Value Like "*Sales*" Then
    sls = Application.Sum(Range(cell2.Offset(0, 1), cell2.Offset(0, 10)))
    If sls < 1 Then
    Range(cell2.Offset(-2, 0), cell2.Offset(3, 0)).EntireRow.Hidden = True
    End If
    End If
    End If
    Next

    How would I combine these two tests into one line.
    1-If IsError(sls) = True Then
    2-If sls < 1 Then

    Thanks

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

    Re: Combining tests in Excel VBA

    The following should work:

    <pre>Dim cell As Range, sls As Variant
    Application.ScreenUpdating = False
    For Each cell In Range("b33:b580")
    If cell.Value Like "*Sales*" Then
    sls = Application.Sum(Range(cell.Offset(0, 1), cell.Offset(0, 10)))
    If IsError(sls) = True Then
    Range(cell.Offset(-2, 0), cell.Offset(3, 0)).EntireRow.Hidden = True
    Else
    If sls < 1 Then
    Range(cell.Offset(-2, 0), cell.Offset(3, 0)).EntireRow.Hidden = True
    End If
    End If
    End If
    Next
    </pre>

    Legare Coleman

Posting Permissions

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