odd object = nothing

Epoc

New Member
Joined
Apr 1, 2010
Messages
7
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 @
Code:
>        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
\\
Code:
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

New Member
Joined
Apr 1, 2010
Messages
7
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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:
Code:
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

New Member
Joined
Apr 1, 2010
Messages
7
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,235
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,166
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,505
Messages
5,602,062
Members
414,498
Latest member
jordanmiller7890

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top