查找和FindNext中的VBA [英] Find and FindNext in VBA

问题描述

我工作的Excel宏。我需要从另外一个excel表中获取数据时，code应该先检查是否有与同FundName任何其他行，如果找到则条件。

I am working on Excel macro. What i need when getting data from another excel sheet, code should first check if there is any other row with the same FundName and if found then conditions apply.

我在这里只给Excel工作表的样本从中FundId要检查：

I am just giving the sample of Excel Sheet from which the FundId is to be checked :

S.No    Funds
1        A
2        B
3        C
4        D
5        A

code为如下：

Code is given below:

Set shtData = wbraw.Sheets(1) ' this line is correct

Set CCell = shtData.Cells.Find("Funds", LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0)

Set DCell = CCell.End(xlDown)

Dim SearchString as String
SearchString  = "A"  

Set FindRow = shtData.Range(CCell, DCell).Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

Set NextRow = shtData.Range(CCell, DCell).FindNext(After:=FindRow)

以上$ C $两条线C不工作，因为我想它应该是。我们说，如果SearchString设置为A，然后FindRow和NextRow两者都应该有值。如果SearchString设置为B，然后按给定的Excel工作表FindRow应有的价值，但NextRow返回Nothing让我能将我的条件。

Above two lines in code not working as i want it should be. Let say if SearchString is set to "A" then FindRow and NextRow both should have the value. And if SearchString is set to "B" then as per given excel sheet FindRow should have the value but NextRow returns Nothing so that I can apply my conditions.

请，如果任何人能帮助我。

Please if anyone can help me.

推荐答案

查找将使用范围内第一个单元格为在参数，如果没有指定，因此在搜索B2后开始，因此它找到的第一个单元格B6。 如果顺序是重要的，你再调用查找与作为在的最后一个单元格：

Find will use the first cell of Range for the After parameter, if it is not specified, therefore the search is started after B2, and thus the first cell it finds is B6. If the order is important for you then call Find with the last cell provided as After:

    Dim counter As Integer
    counter = 0

    With shtData.Range(CCell, DCell)
        Set c = .Find(SearchString, LookIn:=xlValues, LookAt:=xlWhole, After:=DCell)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                counter = counter + 1
                Debug.Print "The next match #" & counter & " is " & c.Address

                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

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