Worksheet_Change Private Sub only affecting part of the worksheet, and not the whole range specified in my code.

[LauraJane]

I have some code that isn’t working right. On the Student report page, I’ve written a sub that should change all cells’ formulas to the row number I specified. It successfully changes the formulas in cells A3:A6, but doesn't for any other cells. It’s not throwing any errors, the error handler doesn't trap anything, and it's compiling without issue, so I think it’s an issue with the logic. The code is in a private sub for the worksheet that it applies to: 'Student Report'.

Private Sub Worksheet_Change(ByVal Target As Range)
'Changes the row value in the worksheets' formulas to match the row value shown in B2 of the record the listbox(A2)


On Error GoTo ErrHandler
Dim lRow As String

If Target.Address = Range("A2").Address Then 'If the value of A2 changed, then
lRow = Range("B2").Value 'store the value of lRow - the line row value of the name that was selected in A2
Worksheets("Student Report").Range("A3:Q104").Replace What:="$*", Replacement:=lRow, LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False 'Replace any part of a cell that contains a $ and any number with the value in lRow
End If
Exit Sub


ErrHandler:
MsgBox Err.Number & "; " & Err.Description
Err.Clear

End Sub

 

[StephenCrump]

Welcome to MrExcel!

If your code is running, and successfully changing A3:A6, then there is no reason it shouldn't be changing A3:Q104.

Can you give an example of a cell in this range that you think should change, but isn't?

Replacing using "$*" sounds dangerous. It implies that you're sure the only "$" in the sheet are in simple references like:

='Sheet Name'!A$10

Instead of using VBA to hard-code all row references every time the row number changes, a more conventional approach would be to use the row number in an Excel formula, e.g. INDEX() so that your formulae always point dynamically to the right row.