Find method in if selection

[mo2783]

Hiya All

I have a worksheet with functions created by the user called =PutValues(), what i am trying to do is write some code to find these function and count how many are on the worksheek. I have something like 30 worksheets full of them. i have wrote the following code:

Public Function PutValueOccurrence(strPutValueStartingPoint As String, intRowCount) As Integer

    Dim strFindItem As String
    Dim lCount As Integer
        
    strFindItem = "PutValues"
    
    Range("A1").Select
        
    For lCount = 1 To intRowCount
        Range("A" & lCount).Select
        If Rows.Find(What:=strFindItem, After:=ActiveCell, _
                        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False) = "PutValues" Then
            
            MsgBox "Found"
            intFound = 1 + intFound
                       
        Else
           
           MsgBox ActiveCell.Address
        End If
    Next
   
    PutValueOccurrence = intFound

End Function

Any help will be much appreciated

 

[combo]

See Find and FindNext methods in excel vba help. Add counter. You may need to convert the string to Unicode (strFindItem = StrConv("PutValues", vbUnicode)), that was necessary in my sase in excel xp.

combo

 

[mo2783]

Can anyone help why i am getting a error saying "Object Variable or With Block variable not set" The errors occurs at the line

 If Rows(lCount).Find

Public Function PutValueOccurrence(strPutValueStartingPoint As String, intRowCount As Integer) As Integer

    Dim strFindItem As String
    Dim lCount, intFound As Integer
           
    strFindItem = "PutValues"
    
    lCount = 1
    Range("A" & lCount).Select
    
    For lCount = 1 To intRowCount
    
        Range("A" & lCount).Select
        
        If [b]Rows(lCount).Find[/b](What:=strFindItem, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate Then
        

            MsgBox ActiveCell.Address & " " & "Found"
            intFound = 1 + intFound
            Range("A" & lCount).Select
        Else
            MsgBox ActiveCell.Address & " " & "Not Found"
        End If
    Next
   
    PutValueOccurrence = intFound

End Function

 

[combo]

Find method returns a range or Nothing if no match is found. You catch the second case and thus try to proceed with: Nothing.Activate

combo

 

[mo2783]

What do you mean? have a elseif statement? or is it on the end of the find method?

 

[combo]

Your full line with error is:

Rows(lCount).Find(....).Activate Then

Here:
1.
Rows(lCount) points to lCount row in active worksheet,
2.
Rows(lCount).Find(....) searches this row according to rules set in brackets,
3.
If Rows(lCount).Find(....).Activate activates the first found cell.

If there is nothing found in step (2), it returns Nothing, that you try to activate it in step (3). The error arises here. The test for Nothing is necessary after step (2).

You could really see the help file. Nb. there is no need to activate any cell to proceed with your task.

combo

 

[mo2783]

I have tried what you said combo, but still getting the same error meassage! Any ideas why it dont like the following line of coding.

If Rows(lCount).Find(What:=strFindItem, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False).Activate Then

Which is being controlled by a for...Next loop.

any ideas please

 

[Rofeu]

Hi,

as combo says, if it returns NOTHING then that is what you're trying to activate.

Your IF statement doesn't compare anything, so that's another point where it will error.

right now it says IF NOTHING.activate then....

The activate is not necessary at all actually.

What you need to do is capture the result of the find first then test that.

i.e.

Dim FoundRange as Range

Set FoundRange = Rows(lCount).Find(What:=strFindItem, After:=ActiveCell, LookIn:=xlFormulas, _
                    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                    MatchCase:=False, SearchFormat:=False)

If FoundRange Is Nothing Then.....

and set your object variable to nothing at the end of your code.


Cheers,

Roel

 

[mo2783]

Thanks for you help finally got it to work and here is the solution might be of help to someone else!

Public Function PutValueOccurrence(strPutValueStartingpoint As Variant, intRowCOunt As Integer) As Integer
    
    Dim strFindItem As String
    Dim lCount As Integer
    Dim intFound As Integer
    Dim FoundRange As Range
     
    strFindItem = "PutValues"
    
    Range("A1").Select
        
    For lCount = 1 To intRowCOunt
        
        Range("A" & lCount).Select
        
        Set FoundRange = Rows(lCount).Find(What:=strFindItem, after:=ActiveCell, _
                        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, MatchCase:=False)
                            
        If FoundRange Is Nothing Then
           
        Else
            intFound = 1 + intFound
        End If
    Next
    
    PutValueOccurrence = intFound
        
End Function

 

[combo]

Sorry for still going back to things already pointed, but:
- you still search for "PutValues", guess you like to use the function argument,
- Find method finds only one occurence within given range (row in your case), what happens when you have two values here?
- I do not know what excel version you use, in my case (xp) when searching for built-in function name Find method expects unicode string, otherwise finds nothing.

combo

 

[mo2783]

I have had no problems withthe above code it works perfectly. I am using excel 2003, maybe thats why!