Consulting

Results 1 to 6 of 6

Thread: Mark minumums by columns in each segment

  1. #1
    VBAX Regular
    Joined
    Jun 2004
    Posts
    35
    Location

    Mark minumums by columns in each segment

    Hello all,

    In fixed 8 column data (unlimited rows) need to fınd minimum value and mark with color.
    And need to do this for each segment.(segments marked as borderlined
    numbers ie.7 8 9 10 11 12 13 and 14)
    Example worksheet attached.

    Current macro does what is supposed to do but takes nul values
    into consideration.
    Which not desired. Need only marked minumum values and avoid zero or nul values.

    Any help wellcome.
    Thank you

  2. #2
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    Why not use conditional formatting on your worksheet you can use this formula
     
    =IF(B4=MIN($B$4:$I$6),TRUE,FALSE)
    then choose a colour!

    Regards,
    SImon

    P.S see attached!
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    To avoid 0, needs a little more (courtesy of Shazam). You might want to make up some code to write these formulae into each segment.
    For the first block.
    =IF(C4=LARGE($B$4:$I$6,COUNTIF($B$4:$I$6,">0")),TRUE,FALSE)
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    VBAX Regular
    Joined
    Jun 2004
    Posts
    35
    Location
    thank you for your responses,

    But I am afraid I wasnt very clear on my question.
    Need mark min values column vise ..

    For the first segment :

    Column B B4:B6, Column C C4:C6, etc..till Column I
    and need to do this for each segment..

    Next segment would be:

    Column B B8:B10, Column C C8:C10 etc..

    Conditional formula not preferred , since I wouldnt know how many rows will be and dont know know how to conditionaly format for the given conditions above.

    And I think "mdmackillop" solution also address the finding minimum (avoiding nul values) on each segment but row vise.

    If you would run the macro on my original example you will see what I mean.
    Only problem there is marking nul values (blanks ) as minimum . Need correction there if possible.

    Thank you very much for your support

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Whilst I agree that Malcolm's idea is the preferred approach, as the data blocks are not uniform in size, I think that the formula would get enormous, so I offer more VBA.

    Sub minp()
    Dim iLastRow As Long
    Dim i As Long, j As Long
    Dim aryMin(1 To 10, 1 To 2)
    With ActiveSheet
        iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        'move down to first data row
        i = 1
        Do While .Cells(i, "A").Value = ""
            i = i + 1
        Loop
       'now process them
       Do While i <= iLastRow
            ReDim arydata(1 To 10, 1 To 2)
            Do While .Cells(i, "A").Value <> ""
                For j = 2 To 10
                     .Cells(i, j).Interior.ColorIndex = xlColorIndexNone
                     If .Cells(i, j).Value <> "" Then
                         If IsEmpty(arydata(j, 1)) Then
                             arydata(j, 1) = .Cells(i, j).Value
                             arydata(j, 2) = i
                         ElseIf .Cells(i, j).Value < arydata(j, 1) Then
                              arydata(j, 1) = .Cells(i, j).Value
                              arydata(j, 2) = i
                         End If
                     End If
                Next j
                i = i + 1
            Loop
            For j = 2 To 10
                If Not IsEmpty(arydata(j, 1)) Then
                    .Cells(arydata(j, 2), j).Interior.ColorIndex = 6
                End If
            Next j
            'move down to next data row
            Do While .Cells(i, "A").Value = "" And i <= iLastRow
                i = i + 1
            Loop
        Loop
    End With
    End Sub

  6. #6
    VBAX Regular
    Joined
    Jun 2004
    Posts
    35
    Location
    Problem solved by "XLD" 's solution.
    Worked as expected.

    Thank you all for your help

Posting Permissions

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