VBA搜索类别 [英] VBA SEARCH FOR CATEGORY

问题描述

请帮助我，我是VBA中的那个菜鸟。我们教授给了我们一个项目。他希望我们搜索PRIME NO的类别。 RFQ类别 和描述。他在下面的代码中给我们只是向下滚动。例如，如果我搜索某个类别  LOS ANGELES LAKERS
。我将开始搜索整个单词，即LOS ANGELES LAKERS，如果我在类别中找不到LOS ANGELES LAKERS，我将搜索单词ANGELES如果我再也找不到类别，那么就是LOS。如果
再也没有发生，我会再次搜索，但这次我将从洛杉矶到洛杉矶开始到湖人队。拜托，我需要你回答代码。谢谢

Hi please help me I'm so noob in VBA. We had a project given to us by our professor. he wants us to search a category for PRIME NO. RFQ Category  and description. the he gives us in the code below just scroll down . for example if I Search a category  for LOS ANGELES LAKERS. I will start searching for the whole word which is the LOS ANGELES LAKERS then if I couldn't find LOS ANGELES LAKERS in the category I will search for the word ANGELES if I couldn't find again the category next is the LOS . if ever nothing happened again I will search again but this time I will start from Los To Angeles to Lakers. please I need you answer code please. thanks

推荐答案

不确定以下内容是否真的如此。屏幕片段显示了要搜索的数据，您将看到我更改了洛杉矶湖人队的单词，以确定它可以通过原始文本中的单个单词找到。

Not sure if the following is really what you are after. The screen snippet shows the data to be searched and you will see that I changed the words around for Los Angeles Lakers to establish that it can find by individual words in the original text to find.

代码首先搜索整个字符串，如果没有找到，则它使用字符串中的单词数组，然后搜索单个单词。在这个时间点它不会执行下一个查找，但是在我移动到找到Next（如果需要）之前，让我们看看我是否在正确的路径
之前。

The code first searches for the entire string and if not found then it uses an array of the words in the string and then searches for the individual words. At this point in time it does not perform find next but lets see if I am on the correct path for what you want before I move to find Next (if it is required).

以下搜索代码。

子搜索（）

    Dim ws As Worksheet

    Dim rngToSearch As Range

    Dim rngToFind作为范围

    Dim strToFind As String

    Dim arrSplit As Variant

    Dim i As Long

    Dim j As Long

   

   设置ws =工作表（" Sheet1"）       "编辑"Sheet1"到工作表名称

   

   使用ws

       设置rngToSearch = .Range（.Cells（5，" A"），.Cells（.Rows.Count，" A"）结束（xlUp）。）

         strToFind = .Cells（2，" A"）。Value        '开始用完整的字符串。编辑"A"至所需列

        arrSplit =拆分（strToFind，""）      '在空格处拆分并分配给阵列

   结束与$
   

   请按
       设置rngToFind = rngSearch（rngToSearch，strToFind）

           如果Not tngToFind Is Nothing Then        "不是没有，那么就是这样找到的东西了。
                Application.Goto rngToFind          '选择包含找到的数据的单元格。
               退出Do $ $ $ b           否则    '如果没有找到，则使用字符串数组

                i = i + j   '第一次迭代将为零

               如果我> UBound（arrSplit）然后    "阵列

&NBSP的过去端;                 &NBSP ; MsgBox strToFind& "没有发现与QUOT;

                  &NBSP ;退出Do $ $ $ b               结束如果

                strToFind = arrSplit（ⅰ）

                j = j + 1

           结束如果是
   循环

       

结束子

Sub Search()
    Dim ws As Worksheet
    Dim rngToSearch As Range
    Dim rngToFind As Range
    Dim strToFind As String
    Dim arrSplit As Variant
    Dim i As Long
    Dim j As Long
   
    Set ws = Worksheets("Sheet1")       'Edit "Sheet1"to your worksheet name
   
    With ws
        Set rngToSearch = .Range(.Cells(5, "A"), .Cells(.Rows.Count, "A").End(xlUp))
        strToFind = .Cells(2, "A").Value        'Commence with full string. Edit "A" to required column
        arrSplit = Split(strToFind, " ")      'Split at spaces and assign to array
    End With
   
    Do
        Set rngToFind = rngSearch(rngToSearch, strToFind)
            If Not rngToFind Is Nothing Then        'Not Nothing then is something so found
                Application.Goto rngToFind          'Select the cell containing the found data
                Exit Do
            Else    'If not found then use the array of strings
                i = i + j   'First iteration will be zero
                If i > UBound(arrSplit) Then    'Past end of array
                    MsgBox strToFind & " not found."
                    Exit Do
                End If
                strToFind = arrSplit(i)
                j = j + 1
            End If
    Loop
       
End Sub

函数rngSearch（作为范围，作为字符串）作为范围

   

    Dim rngToFind作为范围

   

   使用rng

       设置rngSearch = .Find（什么：= STR，_

                        后：=细胞（.Cells.Count），_

      群组。 ;                 LOOKIN：= xlFormulas，_

&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;注视：= xlPart，_ <登记/>
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP; SearchOrder：= xlByRows，_

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; SearchDirection：= xlNext，_

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;&NBSP;&NBSP;&NB属;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; MatchCase：=假_

&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP; SearchFormat：= False）

   结束与$
   

结束功能

Function rngSearch(rng As Range, str As String) As Range
   
    Dim rngToFind As Range
   
    With rng
        Set rngSearch = .Find(What:=str, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
    End With
   
End Function

这篇关于VBA搜索类别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！