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 Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Published by . Latest update on 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 (https://ccm.net/).

0 Comments