Excel - A macro to increase a cell reference


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.

Ask a question
CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jean-François Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.
This document, titled « Excel - A macro to increase a cell reference », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).