Excel VBA Loops to analyze data
Doing mathematical calculations in VBA to analyse and manipulate a spreadsheet is easy to learn. The real powerful skill is learning how to loop through a range of cells to manipulate the data. Below are two examples of looping through a range of cells to find the maximum number.
Notes
- The second Example is more robust. Selection range could be replaced by any range, such as a range of cells that you have names in excel.
- In the first example thisworkbook and the relevant sheet is referenced. This prevents the code from accidentally manipulating another opened workbook.
Example 1:
Dim maximum as double
Dim rowNumber as integermaximum = 0
For rowNumber = 3 to 100
If thisworkbook.sheets("Sheet 1").cells(rowNumber, 2) > maximum then
Maximum = Thisworkbook.sheets("Sheet 1").cells(rowNumber, 2)
End if
Next rowNumberMsgbox "The maximum number found is : " & maximum
Example 2
Dim maximum as doubleDim cell as range
maximum = 0
For each cell in selection
If cell.value > Maximum then Maximum = cell.value
Next cellMsgbox "The maximum number found is :" & maximum