CODEWITHSUNDEEP
vbaexcelloops
aaron2295
aaron2295

Reputation: 27

IF FIND function doesn't find anything in vba then

I'm currently automating a manual process that does the following steps:

1.Prompts the user to open a data file and opens the file

2.Inserts 4 columns

3.Using the data that was already in the file creates a unique string in the format "DD/MM/YYYY TEXT" where text is a variable

4.Uses an if statement to determine if a row exists within the Master Data File

5.If the value in column d = "Exists" then find the string in the Master data file and use the vlookup function to transfer the data from the data file to the Master Data File

6.Else (If the value = anything else) then Insert a new row name it and then use the vlookup function to transfer the data from the data file to the master file

My issue lies with the fact that the Unq.String sometimes/rarely isn't an EXACT match so by using the FIND function it will return the message: "We can't find what you are looking for. Click options for more ways to search"

My question to you guys is, is there a way that upon receiving this error message, that the data is then pasted on another tab called "mop up" before moving onto the next row? I'm using a LOOP within my code for steps 5 and 6 as I will need to jump down each row but I wouldn't know where to start this without breaking the loop.

Thanks for your help in advance guys and let me know if you need any more information regarding the context.

Aaron

Thanks

EDIT Comments explain how I plan to use this:

Dim ColumnD As String
Dim SearchValue As String

If ColumnD = "Exists" Then

'Find SearchValue in Master data sheet and vlookup
'ELSE Insert new row, add UNQ.String to New row then do the vlookup
'Loop untill using Rows.count + 1 and lastRow (Already declared)

Upvotes: 1

Views: 35882

Answers (1)

nbayly
nbayly

Reputation: 2167

This question was already explored here: How to detect whether VBA Excel found something.

Basically you set a range variable to the resulting .find range. If that range is Nothing then nothing was returned and you can respond to the user in the manner that best suits you.

EDIT: Per OP's added inquiry

Sub findsomething()
    Dim foundsomething As Range
    Dim searchterm As String
    searchterm = "Search Term"

    Set foundsomething = Application.ActiveSheet.Find(What:="search term", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If (Not foundsomething Is Nothing) And columnD = "Exists" Then
        ' Do something
    End If
End Sub

Cheers,

Upvotes: 3

Related Questions