Macro to increase a cell reference by 11 Solved/Closed

Question

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

rizvisa1 · Answer

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

rizvisa1 · Answer

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

Macro to increase a cell reference by 11

2 responses

Subscribe To Our Newsletter!