Excel .Find throws an exception when it doesnt find a specific variable. How Do I stop it from break-CodePudding

Good morning/afternoon all,

I am currently creating a program to automate a couple of excel sheets.

While this may be trivial to some. I have no clue how to stop .find throwing an exception when it doesnt find the result it is looking for.

The .find function works as intended if it can find what it is after.

AreaFindRow = ExportExcelWs.Range(AreaFind).Find(AreaArray).Row

system.nullreferenceexception

Error message: 'Object reference not set to an instance of an object.'

I have tried:

Try
    AreaFindRow = ExportExcelWs.Range(AreaFind).Find(AreaArray).Row
Catch ex As Exception
    MsgBox("Cannot find this value in the worksheet")
End Try

This however, still does not work.

Any suggestions will be tested!

Thanks in advance.

CodePudding user response：

Try this

If Not ExportExcelWs.Range(AreaFind).Find(AreaArray) Is Nothing Then
   AreaFindRow = ExportExcelWs.Range(AreaFind).Find(AreaArray).Row
Else
   AreaFindRow = 0
End if

You can then handle your code depending on whether AreaFindRow is 0 or >0