Results 1 to 8 of 8

MIN & MAX VBA commands not seeming to work

Threaded View

  1. #1
    Registered User
    Join Date
    11-11-2009
    Location
    Columbus, Ohio; USA
    MS-Off Ver
    Excel 2002
    Posts
    70

    Wink MIN & MAX VBA commands not seeming to work

    Could someone check out the VBA code here?

    I would like someone to explain the reason why when the program is run, the maximum value in column S successfully is selected by being colored in pink, but the minimum in column R between those pink max's (indicated by the entire row outlined in red) is not the minimum.

    In other words, look at the numbers in column R. Only the 14.08 rows are supposed to be boxed in red. I can't understand why the 30.08 and the 46.08 are boxed in red??? And puzzling to me is why the 46.08 near the top are boxed, yet the 46.08 near the bottom are not boxed.

    Thank you!

    Sub x()
        Dim DstRng As Range
        Dim R As Long
        Dim RngEnd As Range
        Dim rData       As Range
        Dim cell        As Range
        Dim dMin        As Double
    
     Application.ScreenUpdating = False
     Application.Calculation = xlCalculationManual
    
      With Worksheets("INPUT")
       'Set the first row of the destination
        Set DstRng = Range("A11:S11")
       'Find any rows below the first that contain data
        Set RngEnd = .Cells(Rows.Count, DstRng.Column).End(xlUp)
       'Clear the cells
        Set DstRng = IIf(RngEnd.Row < DstRng.Row, DstRng, .Range(DstRng, RngEnd))
        DstRng.Clear
      End With
      
        With Worksheets("Details")
            .UsedRange.Borders.LineStyle = xlNone
            Set rData = .Range("R5", .Range("R604"))
    
            For Each cell In rData
                If cell.Offset(, 1).Value = WorksheetFunction.Max(rData.Offset(, 1)) Then
                    If dMin = 0 Or cell.Value <= dMin Then
                        dMin = cell.Value
                        With Intersect(.UsedRange, cell.EntireRow)
                            .BorderAround LineStyle:=xlContinuous, ColorIndex:=3, Weight:=xlMedium
                            .Copy
                            DstRng.Offset(R, 0).PasteSpecial xlPasteValues
                            R = R + 1
                        End With
                    End If
                End If
            Next cell
        End With
        
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
        
        Application.CutCopyMode = False
    End Sub
    Attached Files Attached Files
    Last edited by Danexcel; 12-08-2009 at 03:42 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1