i've written large set of code, that was working, so me as dumbass decided to rewrite it to be prettier. As im asking question here, you could already figure out that there were errors everywhere.

Anyway, i used this part of code to find some text string, where STRING_I_NEED is Dim as String

If ( Sheet2.Columns(2).Find(STRING_I_NEED, lookAt:=xlWhole).Value = "") Then
              'Do something
    
    Else ' Do something else
    End If

Also i used another IF to find same string in another column (3).

If string exists in column (in any of them) code works. If it doesnt, i get Error code '91'. I've googled everywhere, tried to replace sheet.columns.find.value with variable and used "Set" before variable name.

Only question here is: Why it works in previous version of saved file, but not in new "rewritten" version of file. (New one is save of old file with different name with old code deleted and new one copied inside) Mentioned parts of code are the same

CodePudding user response：

Try removing ".Value = """ from the formula and rerunning the macro.

CodePudding user response：

Using the Find Method

Using a Variable

• The Find method will return a one-cell range reference or Nothing. To control this, using a variable is of great importance.

Dim fCell As Range
Set fCell = Sheet2.Columns(2).Find(What:=STRING_I_NEED, LookAt:=xlWhole)
If fCell Is Nothing Then ' string not found
    ' Do something
Else ' string found
    ' Do something else
End If

' or if you want to switch the order of the statements:

Dim fCell As Range
Set fCell = Sheet2.Columns(2).Find(What:=STRING_I_NEED, LookAt:=xlWhole)
If Not fCell Is Nothing Then ' string found
    ' Do something
Else ' string not found
    ' Do something else
End If

A Ton of Argumets

• Then there is the issue of additional arguments, in this case, LookIn and SearchDirection being the important ones (the latter being self-explanatory). Since you're using xlWhole, with the LookIn argument's parameter xlFormulas you will prevent the Find method from failing when cells (rows, columns: usually rows) are hidden. Visit the above link to further study the material (I've visited it dozens of times).