TechArena Community

TechArena Community (http://forums.techarena.in/)
-   Windows Software (http://forums.techarena.in/windows-software/)
-   -   Macro to find the min value of the rows (http://forums.techarena.in/windows-software/1409463.htm)

Farzana 27-05-2011 07:27 AM

Macro to find the min value of the rows
 
I want the code that will find the min value in the particular coloum and once it gets the min value should clear the contents of the other cells.For example if i have the dates in the coloum and i want to find the minimum dates and then want to clear other cells based on the results
For example the dates are
200906
200905
200808
200801
200710
200705
200702
I want to find the min of these dates and clear the cells for the min value only

Badrunath 27-05-2011 07:28 AM

Re: Macro to find the min value of the rows
 
For your problem :

his is the macro but it is in the VBeditor,you must first delete the coloum and then again run the macro "min" in the VBeditor

Code:

Sub TotalTableCellValues()
  Dim cTable1Cell As Cell
  Dim cTable2Cell As Cell
  Dim cSumCell As Cell
  Dim iCellTotal As Long
  ' Set variable equal to cell A1 of table 1.
  Set table1Cell = ActiveDocument.Tables(1).Cell(Row:=1, Column:=1)
  ' Set variable equal to cell A1 of table 2.
  Set table2Cell = ActiveDocument.Tables(2).Cell(Row:=1, Column:=1)
  ' Set variable equal to cell A2 of table 2.
  Set cSumCell = ActiveDocument.Tables(2).Cell(Row:=2, Column:=1)
  ' Calculate totals
  iCellTotal = Val(table1Cell.Range.Text) + Val(table2Cell.Range.Text)
  ' Insert result of calculation into cell A2 of Table 2.
  cSumCell.Range.Text = iCellTotal
End Sub


MahaGuru 27-05-2011 07:28 AM

Re: Macro to find the min value of the rows
 
I f u have a spreadsheet and you want to find the lowest value and highlight the lowest number in each row then use this macro
Code:

Sub HTH()
Dim r As Range
i = Application.WorksheetFunction.Min(ActiveCell.EntireRow)
ActiveCell.EntireRow.Find(What:=i, LookIn:=xlValues).Select
End Sub

hope this will help you

sergioKomic 27-05-2011 07:28 AM

Re: Macro to find the min value of the rows
 
I am writing a code that will find the lowest value of each row and then it will change the background color of the particular row,it will only change the color of the lowest cells,I have written a long VBA code it is working but the code is not portable and if some more coloums are added then the changes that must be made to the "if statements" are long . The most basic example for the same you can test by using conditional formatting which does not need scripting.

CitricAcid 27-05-2011 07:29 AM

Re: Macro to find the min value of the rows
 
If you have the rows of data and want to highlight them then the easiest way to do this is by using the conditional formatting
first highlight the row/range of the data then after that go to format menu and select conditional formatting
cell value =MIN(highlight the row/range)
after then choose the pattern color
this should help you,but if you have a lt of rows then it is very complicated but it works


All times are GMT +5.5. The time now is 03:25 PM.