VBA相当于Excel的"Search()" [英] VBA equivalent to Excel's "Search()"

问题描述

我有一项作业需要帮助.一共有三列:A列(用户名)，B列(类别)和C列(注释).我必须创建一个Sub，根据类别(例如Admin)检查B列下方的每个单元格.例如，如果在B2中找到字符串"Admin"，则在A2中搜索:"john.deer"，"BES"或"mars".如果找到"john.deer"，则C2表示域管理员帐户"，如果找到"BES"，则C2表示"BES管理员帐户"等.

I have an assignment I need some help with. There are three columns: Column A(username), Column B(Category), and Column C(note). I have to create a Sub that checks each cell down Column B according to category (for example Admin). If the string, "Admin", is found in B2 for example, then the A2 is searched for either:"john.deer", "BES", or "mars". If "john.deer" is found then C2 is to say "Domain Admin Account", if "BES" is found then C2 is to say "BES Admin Account" etc.

但是，如果在列C中未找到"Admin"，则将在C2中搜索"SQL".如果找到"SQL"，则在A2中搜索"SQL Server"，"SQL Engine"和"SQL Cluster".与上一段完全相同的任务，只是在B和A列中搜索了不同的字符串.不同的字符串也输出到了C列.

However if "Admin" is not found in Column C then "SQL" is searched in C2. If "SQL" is found then "SQL Server", "SQL Engine", and "SQL Cluster" is searched in A2. The exact same task as the previous paragraph except different strings are searched in Column B and A. Different strings are outputted to Column C as well.

我有一个完美的方程式，只需要为VBA创建一个等效项即可.

I had an equation that was perfect and just needed to create an equivalent for VBA:

=IF(NOT(ISERROR(SEARCH("admin",B3))),IF(NOT(ISERROR(SEARCH("john.deer",A3))),"Domain Admin Account",if(not(iserror(search("bes",a2))),"BES  Admin Account", if(not(iserror(search("mars",a2))),"Cisco Admin Account","no category"),IF(NOT(ISERROR(SEARCH("SQL",B3))),IF(NOT(ISERROR(SEARCH("sqlserver",A3))),"SQL Server",IF(NOT(ISERROR(SEARCH("sqlengine",B3))),"SQL Engine Account"," ")))

您可以说它是一团糟，这就是为什么我希望创建VBA等效项.但是，VBA中没有Search()对象，只有.Find.

As you can tell its a mess, which is why I wish to create a VBA equivalent. However, there is no Search() object in VBA, only .Find.

这是我在尝试搜索之前无法使用VBA的尝试:

Here is my attempt at VBA before realizing Search did not work:

    Private Sub CommandButton21_Click()

If Not IsError Then
    If Search("ADMIN", "B2") Then 'Searches Col. F for "Admin", then Col. B for type of admin before
                                'populating Notes Col. with specific Note

    If Not IsError Then
        Search("john.deer", "A2") = "Domain Admin Account"
    ElseIf Not IsError Then
        Search("BES", "A2") = "BES Admin Account"
    ElseIf Not IsError Then
        Search("mars1", "A2") = "Admin Account for Cisco Phone System"
    Else
  End If

If Search("admin", "B2") Then
    If Not IsError Then
        Search("uccxadmin", "b2") = "Admin Account for Cisco phone system"

End If
end if
end sub

推荐答案

Instr 是VBA等效版本

dim Pos as long
Pos=Instr(Range("B2"),"ADMIN")
if pos>0 then 'code if found
else 'notfound
end if

Instr具有比搜索更多的选项:请参见文档

Instr has more options than search: see documentation here.

这篇关于VBA相当于Excel的"Search()"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！