Find minimum value
JL1949
Posted messages
3
Status
Member
-
ThauTheme Posted messages 1564 Status Member -
ThauTheme Posted messages 1564 Status Member -
Hello,
I would like to know how to do in VBA in a Macro to find the minimum value in a Range column with a variable, and when this value is found replace the content of another column on the same row at the first line and then delete the other rows.
Example
Line A B
1 12 2017
2 6 2018
3 72 2019
Result
Line A B
1 2018 2017
Thanks!
I would like to know how to do in VBA in a Macro to find the minimum value in a Range column with a variable, and when this value is found replace the content of another column on the same row at the first line and then delete the other rows.
Example
Line A B
1 12 2017
2 6 2018
3 72 2019
Result
Line A B
1 2018 2017
Thanks!
2 answers
Hello JL, hello forum,
perhaps like this:
--
See you,
ThauTheme
perhaps like this:
Sub Macro1() Dim O As Worksheet 'declare variable O (Sheet) Dim TV As Variant 'declare variable TV (Value Array) Dim VM As Integer 'declare variable VM (Minimum Value) Dim I As Integer 'declare variable I (Increment) Dim TR(1) As Variant 'declare variable TR (Result Array) Set O = Worksheets("Feuil1") 'set the sheet O TV = O.Range("A1").CurrentRegion 'set the table of values TV VM = Application.WorksheetFunction.Min(Application.Index(TV, , 1)) 'set the minimum value VM For I = 1 To UBound(TV, 1) 'loop through all rows I of the value table TV If TV(I, 1) = VM Then 'if the data at row I column 1 of TV equals the minimum value VM TR(0) = TV(I - 1, 2) 'retrieve into TR(0) the data at row I - 1 column 2 of TV TR(1) = TV(I, 2) 'retrieve into TR(1) the data at row I column 2 of TV End If 'end if Next I 'next line of the loop O.Range("A1").CurrentRegion.Clear 'clear the data O.Range("A1").Resize(1, 2).Value = TR 'send back in A1 the resized array TR End Sub --
See you,
ThauTheme