vb.net excel查找并计数 [英] vb.net excel find and count

问题描述

目前，我有一个应用程序，可以通过VB.NET应用程序将项目添加到Excel工作簿中，一切正常.
现在，我希望能够计算水果列表中的所有(在此示例中)所有苹果".
我找到了一些找到所有苹果"的代码，然后将其突出显示为红色.
有人可以帮助我计算苹果"并将其放入变量中，而不是突出显示它们.

Currently i have an application that adds items into an Excel workbook via a VB.NET Application, all that works just fine.
now i would like to be able to count all (in this example) all the "Apples" in a list of fruits.
i have found a bit of code that finds all the "apples" and then highlights then in red.
Could someone help me count the "apples" and put it into a variable instead of highlighting them.

' finds the word "apple" and then marks text as red 
Private Sub DemoFind()

'*Declares Excel.Range variables for tracking the entire range, the first found range, and the current found range: 
    Dim rng As Excel.Range = ThisApplication.Range("Fruits")
    Dim rngFound As Excel.Range
    ' Keep track of the first range you find.
    Dim rngFoundFirst As Excel.Range


    ' You should specify all these parameters
    ' every time you call this method, since they
    ' can be overriden in the user interface.
'*Searches for the first match, specifying all the parameters except the cell to search after—by default, the search starts after the cell in the upper-left corner of the range—and searches for "apples" in the cell values, matching partial values, searching by rows in a forward direction, not case sensitive: 
    rngFound = rng.Find( _
      "apples", , _
      Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart, _
      Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, 
      False)

'*Continues searching as long as it continues to find matches: 
    While Not rngFound Is Nothing

'*Compares the first found range (rngFoundFirst) to Nothing, which it can only be if the code just found the first match. In that case, the code stores away the found range; otherwise, if the address of the found range matches the address of the first found range, the code exits the loop.
        If rngFoundFirst Is Nothing Then
            rngFoundFirst = rngFound
        ElseIf rngFound.Address = rngFoundFirst.Address Then
            Exit While
        End If

'*Sets the appearance of the found range, here you need to add the counter to int
        With rngFound.Font
            .Color = ColorTranslator.ToOle(Color.Red)
            .Bold = True
        End With

'*Performs another search: 
        rngFound = rng.FindNext(rngFound)
    End While
End Sub

'Reset Find, putting the range back as it started:
Private Sub ResetFind()
    Dim rng As Excel.Range = ThisApplication.Range("Fruits")
    With rng.Font
        .Color = ColorTranslator.ToOle(Color.Black)
        .Bold = False
    End With
End Sub

我在想类似的东西:

i was thinking of something like:

Dim i As Integer = 0

With rngFound.Font
    i = i + 1
    .Color = ColorTranslator.ToOle(Color.Red)
    .Bold = True
End With
MessageBox.Show("number you got: " & i)

但我认为我仍然迷失了一点.

but i think i am still lost a bit.

推荐答案

使用此方法:

Use this:

iCountOfApples = ExcApp.WorksheetFunction.CountIf(ObjectRange,StringToFind)

其中
iCountOfApples-id整数变量
ExcApp-是对象变量，类型:MS Excel应用程序的实例
ObjectRange-是对象变量，类型:范围
StringToFind-是字符串变量

where
iCountOfApples - id the integer variable
ExcApp - is the object variable, type of: the instance of MS Excel Application
ObjectRange - is the object variable, type of: Range
StringToFind - is the string variable

看起来就像您想显示有关Excel工作表中项目的一些统计信息.
如果您的应用程序也在编写此excel文件，为什么不在写本身时检查支票以计数苹果"，或者说在代码将颜色更改为红色的地方放一个计数器.

appears like you want to show some statistics about the items in excel sheet.
in case your app is writing this excel file also, why do you not put a check while writig itself to count your "Apples" or lets say put a counter where the code is changing the color to Red.

这篇关于vb.net excel查找并计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！