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
Assuming that the cell in which the formula is C18 (change the code to reflect the right cell)
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)
currentRow = CLng(currentRow) + 11
Range("C18").Formula = sTemp & currentRow
Thanks to rizvisa1
for this tip on the forum.
Published by aakai1056
Latest update on July 3, 2012 at 06:17 AM by aakai1056.