Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Column Cells Value Scanning Using VBA (2003)

    What's the code for scanning the "Flow" column in the attached file and setting to zero all values which are below zero or above 0.860?
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Column Cells Value Scanning Using VBA (2003)

    Sub SetToZero()
    Const Lo = 0
    Const Hi = 0.86
    Dim r As Long
    Dim n As Long
    n = Range("C65536").End(xlUp).Row
    For r = 2 To n
    If Range("C" & r) < Lo Or Range("C" & r) > Hi Then
    Range("C" & r) = 0
    End If
    Next r
    End Sub

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Cells Value Scanning Using VBA (2003)

    Like this:

    <pre>Sub SetToZero()
    With Range("A1").CurrentRegion
    'First sort column on "Flow", avoids bug with SpecialCells method
    'when more than 8192 areas are present
    .Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _
    xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    'Filter the table
    .AutoFilter 3, "<0", xlOr, ">0.86"
    'Now set all visible cells in col 3 of the table to 0
    .Offset(1).Resize(.Rows.Count - 1).Columns(3).SpecialCells(xlCellTypeVisible).Valu e = 0
    .AutoFilter
    'Sort back
    .Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:= _
    Range("B2"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
    MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
    DataOption2:=xlSortNormal
    End With
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Cells Value Scanning Using VBA (2003)

    Two wonderful Dutch friends and two pretty different ways to skin the same <img src=/S/kitty.gif border=0 alt=kitty width=256 height=48> <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Thank you Hans

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Column Cells Value Scanning Using VBA (2003)

    Thank you Jan, very clever of you to use the visible property, which makes it a tad more convoluted than Hans's solution <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
    Just out of curiosity, what happens when more than 8192 areas are present?

Posting Permissions

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