EXCEL-VBA-TIPS - Colorado State University
EXCEL-VBA-TIPS
Custom VBA macro to find the Max or Min of a dataset EXCEL has functions to find the maximum or minimum value in a range of data. To understand how these functions work, this tip presents a code to find the maximum or minimum value in a column of data. The companion spreadsheet shows an example dataset and the macros used to make the calculations. The VBA coding shown below contains a macro to find the maximum value, a macro to find the minimum value, a macro to find both the maximum and minimum values, and a macro that finds the maximum of only the values in the dataset that satisfy a specified condition:
Sub MaxValue() ' This sub finds the maximum value in the range A1:A4 ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables ' Set the initial candidate for the max to the first ' cell in the dataset max_val = Cells(1, 1).Value ' Loop over all the values and test them For ctr = 1 To 4 x = Cells(ctr, 1).Value ' Update the maximum value If x > max_val Then max_val = x End If Next ' Output the maximum value found Range("C5").Value = max_val End Sub
Sub MinValue() ' This sub finds the minimum value in the range A1:A4 ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables ' Set the initial candidate for the min to the first ' cell in the dataset min_val = Cells(1, 1).Value ' Loop over all the values and test them For ctr = 1 To 4 x = Cells(ctr, 1).Value ' Update the minimum value If x < min_val Then min_val = x
End If Next ' Output the minimum value found Range("C6").Value = min_val End Sub
Sub MaxMinValue() ' This sub finds both the maximum and minimum ' values in the range A1:A4 ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables ' Set the initial candidate for the max & min to the first ' cell in the dataset max_val = Cells(1, 1).Value min_val = Cells(1, 1).Value ' Loop over all the values and test them For ctr = 1 To 4 x = Cells(ctr, 1).Value ' Test the current maximum value If x > max_val Then ' Update the maximum value max_val = x End If ' Test the current minimum value If x < min_val Then ' Update the minimum value min_val = x End If Next ' Output the maximum and minimum values found Range("C5").Value = max_val Range("C6").Value = min_val End Sub
Sub Cond_MaxValue() ' This sub finds the conditional maximum value in the range A1:A4 ' for all values that are less than the threshold value ' ' Make sure this sub runs on the Data page Sheets("Data").Select ' Initialize variables ' Set the initial candidate for the max to the first ' cell in the dataset max_val = Cells(1, 1).Value threshold = 3 ' Loop over all the values and test them For ctr = 1 To 4
x = Cells(ctr, 1).Value ' Test to see if the value of x is < threshold If x < threshold Then ' Update the maximum value If x > max_val Then max_val = x End If End If Next ' Output the maximum value found Range("C7").Value = max_val End Sub
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- excel vba tips
- excel vba for loop dynamic range
- complete excel vba secrets tips for professionals
- excel vba find row number of matching value
- vba4 using cell formulas in vba performing the same
- excel macro record and vba editor
- excel vba notes for professionals
- automation of 1dcutx in excel by vba
- excel vba select last row with value
- learn vba for excel
Related searches
- colorado state university calendar
- colorado state university calendar 2019 2020
- colorado state university single audit
- colorado state university cost breakdown
- colorado state university tuition fee
- colorado state university tuition 2020
- colorado state university tuition cost
- colorado state university price
- colorado state university tuition 2021
- colorado state university aar
- colorado state university career fair
- colorado state university 2019 schedule