How can I find a minimum value in a range of data in excel

Thu, 02 Jul 2009 14:47:46 Z

How can I find a minimum value in a range of data in excel, select the select first to see where it is and then omit that value from the range and then do this process again for the next minimum value? so that in the end I can omit for example 100 minimum values from a range of 1000 values

How can I find a minimum value in a range of data in excel

Thu, 02 Jul 2009 15:11:04 Z

Hi, not exactly sure what you are trying to do but he below might give you some ideas:

Public Sub test(rng As Range)
Dim minValue
Dim lngCounter As Long

lngCounter = 0
While lngCounter < 100
    minValue = Application.WorksheetFunction.Min(rng)

    Debug.Print minValue
    For Each c In rng
        If c.Value = minValue And lngCounter < 100 Then
            c.ClearContents
             lngCounter = lngCounter + 1
        End If
    Next
Wend
End Sub

How can I find a minimum value in a range of data in excel

Fri, 03 Jul 2009 02:06:39 Z

you can try something like

c.copy destination:=Range("L2")

Cheers
Shasur

http://www.vbadud.blogspot.com

How can I find a minimum value in a range of data in excel

Fri, 03 Jul 2009 11:47:44 Z

Hi again Shasur, thanks for your help yesterday, I changed your code to the following but it does not work, I'm foolishly playing with it but it does not understand: cell.select

what I want to do is to find for example 5 minimums in the range D1:D8761. then cut this value and the values in the same row in the B and C columns and paste them all in another sheet.

Sub Claim()
Dim minval As Single, alfa As Range, a, b As Integer

Set alfa = ThisWorkbook.Sheets("Energy Profit").Range("D1:D8761")

a = 1

lngCounter = 0

While lngCounter < 5
minval = Application.WorksheetFunction.min(alfa)

For Each cell In alfa
If cell.Value = minval And lngCounter < 5 Then
cell.Select

cell.Offset(0, -2).Select
ActiveCell.Interior.ColorIndex = 34
ActiveCell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 1).Select
ActiveSheet.Paste

cell.Offset(0, -1).Select
ActiveCell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 2).Select
ActiveSheet.Paste

cell.Select
cell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 3).Select
ActiveSheet.Paste

a = a + 1

lngCounter = lngCounter + 1
End If
Next
Wend

End Sub

How can I find a minimum value in a range of data in excel

Sun, 12 Jul 2009 02:19:52 Z

Hi

Instead of

cell.Select

cell.Offset(0, -2).Select
ActiveCell.Interior.ColorIndex = 34
ActiveCell.Cut
ThisWorkbook.Sheets("D2").Cells(a, 1).Select
ActiveSheet.Paste

You can try the following

cell.Offset(0, -2).Cut Destination:=ThisWorkbook.Sheets("D2").Cells(a, 1)

Cheers
Shasur

http://www.vbadud.blogspot.com