Format & find issues in Excel VBA

Programming
,
1

I am trying to find the row number in column D with a value that contains a percent sign (%). The code I have written produces a runtime error 91 and I am not sure how to resolve it.

Dim a As Integer
Dim b As Integer
Dim i As Integer

a = 4
b = 5

Worksheets(1).Range("D4") = (a / b) * 100
Worksheets(1).Range("D4").NumberFormat = "0.00\%"

' run time error 91 occures in the following line    
i = Worksheets(1).Range("D:D").Find(What:="%", After:=Range("D1"), LookAt:=xlPart, SearchDirection:=xlNext).Row
' result of i
Range("A1")=i

Can anyone help me identify why the code is producing a runtime error 91 and how I can fix it?

2

The runtime error 91 occurs because the Range.Find method returns Nothing when it doesn’t find a match. In your case, it means that the code couldn’t find a cell in column D that contains a percent sign (%).

To fix the error, you can add a check to see if the Range.Find method returned a valid range before trying to access its Row property. Here’s the modified code:

Dim a As Integer
Dim b As Integer
Dim i As Integer
Dim foundCell As Range

a = 4
b = 5

Worksheets(1).Range("D4") = (a / b) * 100
Worksheets(1).Range("D4").NumberFormat = "0.00\%"

Set foundCell = Worksheets(1).Range("D:D").Find(What:="%", After:=Range("D1"), LookAt:=xlPart, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
    i = foundCell.Row
    Range("A1") = i
Else
    Range("A1") = "No cell with '%' found"
End If

This modified code checks if the foundCell variable is not Nothing before accessing its Row property. If a cell with a percent sign is found, it assigns its row number to i. Otherwise, it displays a message indicating that no cell with a percent sign was found.