Excel - A macro to increase a cell reference

October 2016


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.

Related :

This document entitled « Excel - A macro to increase a cell reference » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.