odd object = nothing

[Epoc]

I guys i'm new here. Got some experience with vb6/vb.net and this is more or less the first time i've tried doing something with Excel macros.

The thing is that i get an odd error when conducting a search in a excel sheet. an object(that by all means shouldn't be NULL/nothing) is NULL/nothing, and i cannot seem to find the error. however i belive it got something to do with macro memory somehow, since the problem rises after i've been using the macro for a while.
The debug points @

>        Set rngFind = .FIND(TARGET)
//rngFind = Nothing and this should not happen.
//Unless the search cannot find a match to the keyword
>        If Not rngFind Is Nothing Then  //rngFind = Nothing

whole sub further down.

Dumping the code. just tell me if you're intrested of some of the data aswell.

Microsoft 2k3 pro edition
Using Excel 2k3 (11.8320.8221) SP3
//
Blad1=sheet1
Blad2=sheet2
Blad3=sheet3
\\

Public Sub findus(ByVal TARGET As String)
    ListBox1.Clear
    alist.Clear
    Dim wbkthis As Workbook
    Dim shtthis As Worksheet
    Dim rngThis As Range
    Dim rngFind As Range
    Dim firstAddress As String
    Dim addSelection As String
    On Error GoTo errh
Set wbkthis = ThisWorkbook
    Set shtthis = wbkthis.Worksheets(3)
   Set rngThis = shtthis.Range("B2", "B" & Blad3.Cells(1, 1))
    With rngThis
   
        Set rngFind = .FIND(TARGET)
        If Not rngFind Is Nothing Then
            firstAddress = rngFind.Address
            Do
                Set rngFind = .FindNext(rngFind)
                alist.AddItem (rngFind.Address)
                ListBox1.AddItem (rngFind.Value)
                
            Loop While Not rngFind Is Nothing And rngFind.Address <> firstAddress
        End If
    End With
    
    Set rngThis = Nothing
    Set shtthis = Nothing
    Set wbkthis = Nothing
    Set rngFind = Nothing
    
    Exit Sub
    
errh:
    Debug.Print Err.Description
    Call MsgBox(Err.Description)
    Exit Sub
End Sub

I don't think you'll need more than that since you should be able to understand it anyhow.

many thanks in advance.

 

[Norie]

The first thing that springs to mind is that the find isn't finding anything.

The second thing is, and I don't mean to be rude, that you aren't actually giving us the full information.

I kind of understand what you seem to want to do but I don't understand fully.

An explanation in words is worth more than a thousand lines of code.

Sometimes, not always, but you know what I mean.

 

[Colin Legg]

Hi,

The thing is that i get an odd error when conducting a search in a excel sheet. an object(that by all means shouldn't be NULL/nothing) is NULL/nothing, and i cannot seem to find the error.

Do you get an error message - if so, what is the error number and its description? Or are you finding that code execution is just mysteriously pausing?

 

[Epoc]

The first thing that springs to mind is that the find isn't finding anything.

The second thing is, and I don't mean to be rude, that you aren't actually giving us the full information.

1:
The Find is finding the values i search for when i open the macro. so the search works. And as I said "The problem rises after a while"

2:
Just tell me what you want
I'll fixx it. but to put it simple. The Range/object does not get filled buy the .FIND(TARGET) as it should.

When i remove the If-statement where it checks the object/range then i get the following error message.
"91:Object variable or With block variable not set."

 

[Norie]

Are you 100% sure the find is finding what you are looking for?

If so how are you checking that?

The error you describe really does point to the find not finding something.

PS You should probably remove the On Error stuff - that could just be hiding the problem.

 

[RoryA]

I suspect your problem is that you are not specifying all the arguments for the Find method. These arguments are stored between calls (and are affected if you do a find in the UI) so you should specify them each time, especially whether you want a full or partial match, to look in values of formulas and case-sensitivity.

 

[Colin Legg]

Hi,

When i remove the If-statement where it checks the object/range then i get the following error message.
"91:Object variable or With block variable not set."

The line:

If Not rngFind Is Nothing Then

is important. It is defensive coding to cater for when a range containing the search value isn't found.

I'll fixx it. but to put it simple. The Range/object does not get filled buy the .FIND(TARGET) as it should.

I think this is the issue. Whatever value it is searching for (TARGET argument) isn't being found. To know the answer to this you need to know two things:
1. The value held by TARGET
2. The values in the worksheet range (shtthis.Range("B2", "B" & Blad3.Cells(1, 1)) you are searching.

You're in the best position to investigate that - we can't because we don't know either of them!

 

[Epoc]

To check the search i got a list with products, which i search for the letter 'A', and more than 50% of the products contain 'A'.
And since i don't give the .FIND any other data to work with it utilises the default values(= not case-sensitive and such) AND it DOES work until some memory restriction or something has been reached.

 

[Norie]

Epoc

Have you looked at Rory's post?

Using Find in code can be pretty tricky, especially when you don't specifically supply all the arguments.

The only argument in the code posted appears to be what you are looking for.

 

[RoryA]

And since i don't give the .FIND any other data to work with it utilises the default values

No, it does not. It uses the last used values for the most part.