Does anyone know how to make the row numbers dynamic in my VBA code? For example, Row62 would always reference that particular line item even if a row was inserted above it and is now technically Row63.
The code is currently as follows:
Sub HistoricalMacro()
Dim c As Long
c = Selection.Column
Application.ScreenUpdating = False
'Format cells
Cells(62, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"
Cells(72, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"
Cells(78, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"
Cells(79, c).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* "" - ""_)"
'Link cells Expense Summary sheet
Cells(55, c).FormulaR1C1 = "='Expense Summary'!R[-1]C"
Cells(59, c).Resize(5).FormulaR1C1 = "='Expense Summary'!R[-3]C"
Cells(68, c).Resize(5).FormulaR1C1 = "='Expense Summary'!R[-6]C"
Cells(76, c).Resize(4).FormulaR1C1 = "='Expense Summary'!R[-9]C"
'Update expense sum formula
Cells(64, c).FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
Cells(73, c).FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)"
Cells(80, c).FormulaR1C1 = "=SUM(R[-4]C:R[-1]C)"
'Clear grey tint in cell
Cells(55, c).Resize(25).Interior.ColorIndex = xlColorIndexNone
Application.ScreenUpdating = True
End Sub
question from:
https://stackoverflow.com/questions/65839608/dynamical-row-links-for-vba 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…