Calculating miles per gallon between fulls

I wanted to calculate the miles per gallon between full tanks, so I wrote a crap macro to do it. You use the macro by putting the macro in a Module and then calling the function with a formula


=calc_mpg_between_fulls(I45) passing it the cell where you're at (in this case column I, row 45) as a Range, so it knows where to access the cells in the sheet.

Basically it looks for the first row that has the string "to full" in column 7 (column G) and then calculates the mileage driven and gas used starting from that row.

Function calc_mpg_between_fulls(thiscell As Range) As Double  
orig_row = thiscell.Row
orig_col = thiscell.Column
'MsgBox Str$(orig_row) + " " + Str$(orig_col)
odometer_col = 1
gas_col = 2
full_col = 7
row_to = orig_row
While Cells(row_to, full_col) <> "to full": row_to = row_to - 1: Wend
row_from = row_to - 1
While Cells(row_from, full_col) <> "to full": row_from = row_from - 1: Wend
'MsgBox "  row_from" + Str$(row_from) + "  row_to:" + Str$(row_to)
milesdriven = Cells(row_to, odometer_col) - Cells(row_from, odometer_col)
For i = (row_from + 1) To row_to: gasused = gasused + Cells(i, gas_col): Next i
calc_mpg_between_fulls = milesdriven / gasused
'MsgBox " miles: " + Str$(milesdriven) + " gas used:" + Str$(gasused) + "   " + Str$(milesdriven / gasused)
End Function

The only thing is that it will only calculate the formula once due to the way Excel recalculates things. So if you want it to recalc, just fill copy the formula over the same cell again and it will recalculate it.

edit:

so I was thinking about recalculation, why not pass a dummy range as well so that it will recalculate if anything in that range is changed.

Function calc_mpg_between_fulls(thiscell As Range, dummyrange As Range) As Double 

and make the formula =calc_mpg_between_fulls(I45,A40:G45)