Excel VBA Loops to analyze data

< Back to home

< Back to development tips

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 integer
maximum = 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 rowNumber
Msgbox "The maximum number found is : " & maximum

 

Example 2

Dim maximum as double
Dim cell as range
maximum = 0
For each cell in selection
If cell.value > Maximum then Maximum = cell.value
Next cell
Msgbox "The maximum number found is :" & maximum

 

< Back to home

< Back to development tips