Excel - A macro to increase a cell reference

December 2016




Issue


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.

Solution


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)   
       
    Loop   
       
    currentRow = CLng(currentRow) + 11   
       
    Range("C18").Formula = sTemp & currentRow   
       
End Sub

Note


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.