MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return smallest values


Posted by Jaime on June 07, 2000 10:40 AM

HI,

I HAVE A SHEET THAT LOKS LIKE THIK THIS

COLUMN A COLUMN B
CANADA 40
US 70
IRELAND 20
FRANCE 10
GERMANY 0
RUSSIA 60

I WANT EXCEL TO GIVE ME IN COLUMN C THE THREE COUNTRIS WITH THE SMALLEST VALUES.
IN ORDER(EXCLUDING THOSE COUNTRIES WITH 0 VALUES.

SO IT WOULD LOOK LIKE THIS:

COLUMN C
FRANCE
IRELAND
CANADA


HOPE U CAN HELP ME
TIA


Posted by Ryan on June 11, 0100 4:54 PM

This is pretty specific to what you wanted, if you need it altered, let me know.

' (Declarations)
Type RangeCellInfo ' stores all changes made by the macro
CellContent As Variant
CellAddress As String
End Type

Public OrgWB As Workbook
Public OrgWS As Worksheet
Public OrgCells() As RangeCellInfo

' (End Declarations)
Sub SmallestValues
Application.ScreenUpdating = False
Dim CurrentRow As Integer
Dim NumofEntries As Integer
Dim Max As Boolean
Dim NumberEntered As Integer
Dim FillRow As Integer
Dim i As Integer, cl As Range
If TypeName(Selection) <> "Range" Then Exit Sub

Range("A1:B6").Select
ReDim OrgCells(Selection.Count)
Set OrgWB = ActiveWorkbook
Set OrgWS = ActiveSheet
i = 1
For Each cl In Selection
OrgCells(i).CellContent = cl.Formula
OrgCells(i).CellAddress = cl.Address
i = i + 1
Next cl

Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


NumofEntries = Application.WorksheetFunction.CountA(Range("A:A"))
CurrentRow = 0
Max = False
NumberEntered = 0

Do While Max = False
CurrentRow = CurrentRow + 1
If Cells(CurrentRow, 2) <> 0 Then
FillRow = FillRow + 1
Cells(FillRow, 3).Value = Cells(CurrentRow, 1).Value
NumberEntered = NumberEntered + 1
End If
If NumberEntered = 3 Then Max = True
Loop
Call UndoEditRange
Application.ScreenUpdating = True
End Sub

Sub UndoEditRange()
Dim i As Integer
On Error GoTo NoWBorWS
OrgWB.Activat