Macro to increase a cell reference by 11 [Solved/Closed]

Report
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010
-
 YaneyYan -
Hi everyone
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
is there a way to do this?
SLKG

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010

Thanks rizvisa1
that works great. There are actually more rows than just C18 to change. How would I update it to do the same to rows C20, C22, C24, C26, C28, C30, C32, C34, C36, C38 without duplicating the whole formula?
SLKG
Hello! What if the cell reference is a formula? example C18 has =Sheet2!H2+Sheet2!J2 then when it opens it will change to =Sheet2!H13+Sheet2!J13 ?
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Sub Auto_Open()
Dim targetCells As Variant
Dim targetCell As Variant

    targetCells = Array("C18", "C20", "C22", "C24", "C26", "C28", "C30", "C32", "C34", "C36", "C38")
    
    For Each targetCell In targetCells
    
        Call increaseAddress(CStr(targetCell), 11)

    Next
    
End Sub

Sub increaseAddress(targetRange As String, increaseBy As Integer)
Dim currentRow As String
Dim sTemp As String

    sTemp = Range(targetRange).Formula
    currentRow = ""
     
    If sTemp <> "" Then
    
        Do While (IsNumeric(Right(sTemp, 1)))
     
            currentRow = Right(sTemp, 1) & currentRow
            sTemp = Mid(sTemp, 1, Len(sTemp) - 1)
     
        Loop
     
        currentRow = CLng(currentRow) + increaseBy
     
        Range(targetRange).Formula = sTemp & currentRow
    End If
End Sub
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010

Thank you! As ever the genius! Works a treat!
SLKG
Could this be down for an arbitrary range. I'd like the same result without listing every cell I want to do upfront