Who Gives a .... Anyway

I wanted to store Row and Column in a VBA macro, and could not really find a good source for the how to anywhere so thought I would document how I did it:   Dim actRow As Integer   Dim actCol As Integer   Dim actRange As String   actRow = ActiveCell.Row   actCol = ActiveCell.Column    ' I actually want 2 rows up in the same colum.   actRange = Chr$(Asc("A") + actCol) + CStr(actRow - 2)   Range(actRange).Select You can change the row and col by just adding 1.

I recently needed to get the next biggest value in a table of values, and after surfing the net and looking at a few examples that where a little confusing this is how it is done: Use VLookup ( value, TableRange , 1, TRUE ) - true means approximate value, which means that in a sorted table it will choose the value equal or less than Then using Match ( theVLookupAbove , TableRange ) - This will return the row number of the value Then take the result from that use Index ( TableRange , theMatchAbove + 1 ) This will give you the result you are looking for. Example: 2^N VALUE 100 1 2 Formula Value Formula in Text 4 VLOOKUP 64 =VLOOKUP(D1,A2:A10,1,TRUE) 8 MATCH 7 =MATCH(D4,A2:A10) 16 INDEX 128 =INDEX(A2:A10,D5+1) 32 AllInOne 128 =INDEX(A2:A10,MATCH(VLOOKUP(D1,A2:A10,1,TRUE),A2:A10)+1) 64 128 256

Excel Sum of from a cell to the RowAbove, or the next row above I got the idea and concept here: Three Ways to Reduce Errors In Your Excel SUM Formulas Essentially; what I wanted to do was prevent the Insert Row just above the total line to always include the row above. So after playing a bit I found the site above. So here is the process: In the workbook you want to have the sum click on any cell Depending on the version of Excel find where you name a range, for 2007 Formulas, Define Name A new name dialog box is displayed Make Name "RowAbove" Scope: Workbook Add a comment the next row up Delete the sheet name and enter in the row aboves address, in my case I clicked on =MySheet!$B$4, so I replace that with "=!B3" Then anywhere I want to add a sum or subtotal I just use sum(A3:RowAbove), then that will sum from A3 to the row above Now even if a insert row above on the formula it will add it the value to the sum.