Question
If a macro intends to modify cells found within the `Selection` using `Find`, how should error handling be structured to prevent errors if no cells are found or if the selection is invalid, ensuring the macro doesn't try to operate on `Nothing`?
Asked by: USER3917
245 Viewed
245 Answers
Answer (245)
When modifying cells, it's critical to ensure the `Range` object returned by `Find` is valid before attempting any operations. This involves a nested check: first, ensure the `Selection` is a `Range`, and then, ensure `Find` returned a non-`Nothing` result. Any modification code should only execute within the scope of these successful checks.
```vba
Sub ModifyFoundInSelection()
Dim targetValue As String
Dim foundCell As Range
targetValue = "OLD_DATA"
If TypeName(Selection) = "Range" Then
Set foundCell = Selection.Find(What:=targetValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCell Is Nothing Then
' Value found, proceed with modification
foundCell.Value = "NEW_DATA"
foundCell.Font.Color = vbRed
MsgBox "Value '" & targetValue & "' at " & foundCell.Address & " has been updated to 'NEW_DATA'."
Else
' Value not found, inform user
MsgBox "'" & targetValue & "' was not found in the selected range. No changes made.", vbInformation
End If
Else
' Selection is not a range, inform user and exit
MsgBox "Please select a range of cells to perform this operation.", vbExclamation
End If
End Sub
```