What I'm looking for is a macro that will replace the row number in a
formula with a new number that I designate through an input box.
Below is the code I have thus far. The problem lies in how I'm
defining the integer portion of the formula that I want to replace -
vbInteger (or vbLong) don't seem to work. Then finally I need to set
Section 2 in a loop through Column CN. Any advice would be greatly
appreciated!
---------------------------------------------------------------------------------------------------------------------------
Sub UpdateFormulas_2()
Dim LRowNumber As Long
LRowNumber = Application.InputBox _
(prompt:="Please enter the row number to update the formulas.",
_
Title:="Update Formulas", Type:=1)
If LRowNumber = False Then Exit Sub
Sheets("Review").Select
'Section 1 - Works just fine
ActiveCell.Select
If IsEmpty(Range("Input!A" & LRowNumber - 2).Value) Then
ActiveCell.Value = ""
Else
ActiveCell.Formula = "=CELL(""contents"",Input!A" & LRowNumber
- 2 & ")"
End If
'Section 2 - Needs help
ActiveCell.Offset(0, 1).Activate
ActiveCell.Select
ActiveCell.Formula = Replace(ActiveCell.Formula, vbInteger,
LRowNumber)
End If
Range("A1").Select
MsgBox ("The formulas were successfully updated to row " &
LRowNumber & ".")
End Sub
---------------------------------------------------------------------------------------------------------------------------
Bookmarks