Excel - A macro to increase a cell reference

Ask a question


I want a macro that on open will increase the cell reference in a cell by 11 each time the book is opened i.e. Cell C18 has =Sheet2!H2. Then next time it opens I want it to change to =Sheet2!H13 then on the next open =Sheet2!H24 etc.


Assuming that the cell in which the formula is C18 (change the code to reflect the right cell)

Sub Auto_Open()   
Dim currentRow As String   
Dim sTemp As String   

    sTemp = Range("C18").Formula   
    Do While (IsNumeric(Right(sTemp, 1)))   
        currentRow = Right(sTemp, 1) & currentRow   
        sTemp = Mid(sTemp, 1, Len(sTemp) - 1)   
    currentRow = CLng(currentRow) + 11   
    Range("C18").Formula = sTemp & currentRow   
End Sub


Thanks to rizvisa1 for this tip on the forum.

Excel - How to sort and delete redundant value?
Excel - Skipping a cell when calculating a total