Excel - A macro to increase a cell reference

Ask a question



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.
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team