Excel - A macro to increase a cell reference

March 2017




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


Published by aakai1056.
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).