Click here to Skip to main content
15,890,438 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.


VB
' 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:
VB
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.
Posted
Comments
chandanadhikari 10-Jan-12 4:02am    
hi,
what are you lost about? can you specify clearly?

Use this:
VB
iCountOfApples = ExcApp.WorksheetFunction.CountIf(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
 
Share this answer
 
Comments
ProEnggSoft 18-Mar-12 12:11pm    
5!
Maciej Los 20-Mar-12 13:48pm    
Thank you!
hi,
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.
 
Share this answer
 
Comments
Member 8327439 10-Jan-12 17:06pm    
Thanks that is a great idear, i allredy have an XML settings file where i could just add some things into that.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900