0
Thanks

A few words of thanks would be greatly appreciated.

Excel - A macro to increase a cell reference




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.
0
Thanks

A few words of thanks would be greatly appreciated.

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.

Related

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).

0 Comments