如果FIND函数在vba中找不到任何内容 [英] IF FIND function doesn't find anything in vba then

问题描述

我当前正在使执行以下步骤的手动过程自动化:

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

1.提示用户打开数据文件并打开文件

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

2.插入4列

3.使用文件中已有的数据创建一个唯一的字符串，其格式为"DD/MM/YYYY TEXT"，其中文本是变量

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.使用if语句确定主数据文件中是否存在行

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

5.如果d列中的值="Exists"，则在主数据文件中找到该字符串，并使用vlookup函数将数据从数据文件传输到主数据文件

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(如果值=其他)，则插入一个新行，将其命名，然后使用vlookup函数将数据从数据文件传输到主文件

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

我的问题在于，Unq.String有时/很少与EXACT完全不匹配，因此通过使用FIND函数，它将返回消息:我们找不到您想要的东西.单击选项更多搜索方式"

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"

我对大家的问题是，是否有办法在收到此错误消息后，将数据粘贴到另一个名为向上拖移"的选项卡上，然后再移动到下一行?我在代码的第5步和第6步中使用了LOOP，因为我需要跳下每一行，但是我不知道在不中断循环的情况下从哪里开始.

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.

亚伦

谢谢

编辑评论说明了我打算如何使用它:

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)

推荐答案

此问题已经在这里进行了探讨:

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

基本上，您将范围变量设置为所得的 .find 范围.如果该范围是 Nothing ，则不会返回任何内容，您可以以最适合您的方式对用户进行回复.

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.

根据OP添加的查询

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

干杯

这篇关于如果FIND函数在vba中找不到任何内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！