Macro to increase a cell reference by 11

Solved/Closed
SLKG
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010
- Apr 26, 2010 at 04:41 PM
 YaneyYan - Mar 7, 2017 at 03:53 PM
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

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 26, 2010 at 09:27 PM
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
0
SLKG
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010

Apr 26, 2010 at 10:07 PM
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
0
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 ?
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
Apr 27, 2010 at 12:14 PM
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
0
SLKG
Posts
6
Registration date
Friday April 16, 2010
Status
Member
Last seen
April 27, 2010

Apr 27, 2010 at 05:48 PM
Thank you! As ever the genius! Works a treat!
SLKG
0
Could this be down for an arbitrary range. I'd like the same result without listing every cell I want to do upfront
0