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.

 

[Epoc]

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.

Just to clarify.
Everything works untill a certain point. i don't know if there is a memory limit for macros or something like that.

The Search does work as intended. however breaks after afew uses. i'll post the whole macro here:

Dim tempo, tempo2 As String
Dim currow, curpris, curnamn, curart, maxrows
Private Sub CommandButton1_Click()
On Error GoTo errhand

Blad2.Cells(1, 1).Value = TextBox1.Text
Blad2.Cells(1, 5).Value = " Pris/enhet"
Blad2.Cells(2, 5).Value = TextBox2.Text * 1
Blad2.Cells(1, 3).Value = " Jämförpris"
Blad2.Cells(4, 1).Value = TextBox3.Text
Blad2.Cells(3, 1).Value = TextBox6.Text
Call calcjmf(TextBox2.Text, TextBox3.Text)
Blad2.Cells(2, 3).Value = tempo
Blad2.Cells(2, 4).Value = "/" & tempo2
Blad2.Cells(4, 2).Value = tempo2
Blad2.Cells(5, 2).Value = "Art.nr:" & TextBox4.Text
Call printit
If save.Value = True Then
Call saveit
End If
Call rs
Exit Sub
errhand:
    Call MsgBox("HALLÅ!" & Chr(13) & Chr(13) & "Kontrollera vad du fyllt i!", vbExclamation + vbOKOnly, "Fel inmatning")
    
Exit Sub

End Sub
Public Sub saveit()
    If currow <> "" Then
    Blad3.Cells(currow, 2) = TextBox1.Text
    Blad3.Cells(currow, 3) = TextBox4.Text
    Blad3.Cells(currow, 5) = TextBox6.Text
    Blad3.Cells(currow, 4) = TextBox2.Text
    Else
    maxrows = xlLastRow
    Dim nextrow
     nextrow = maxrows + 1
    Blad3.Cells(nextrow, 2) = TextBox1.Text
    Blad3.Cells(nextrow, 3) = TextBox4.Text
    Blad3.Cells(nextrow, 4) = TextBox2.Text
    Blad3.Cells(nextrow, 5) = TextBox6.Text
    currow = nextrow
    Dim twb As ThisWorkbook
    Dim ws As Worksheet
    ws = twb.Worksheets(3)
    
    End If
    ws = Nothing
    twb = Nothing
    
    Exit Sub
End Sub
Function xlLastRow() As Long
     
     '    find the last populated row in a worksheet
     
    If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
    With Blad3
        On Error Resume Next
        xlLastRow = .Cells.FIND("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row
        If Err <> 0 Then xlLastRow = 0
    End With
     
End Function
Public Sub calcjmf(ByVal pris As String, ByVal amount As String)
   
    Dim a, b, c, result, tmp
    a = pris
    b = amount
    If OptionButton1.Value = True Then
    c = "kg"
    ElseIf OptionButton2.Value = True Then
    c = "liter"
    ElseIf OptionButton3.Value = True Then
    c = "st"
    End If
    tmp = a / b
    tempo = tmp
    tempo2 = c
      
    a , b, c, result, tmp = Nothing
    End Sub

Private Sub CommandButton2_Click()
End
'Call Me.Hide
End Sub

Private Sub CommandButton3_Click()
If (Me.Height <= 170) Then
Me.Height = 260
CommandButton3.Picture = Image1.Picture
ElseIf (Me.Height <= 260) Then
Me.Height = 170
CommandButton3.Picture = Image2.Picture
End If
End Sub

Private Sub CommandButton4_Click()
findus (TextBox5.Text)

End Sub
Public Sub rs()
OptionButton3.Value = True
TextBox6.Text = ""
TextBox3.Text = "1,0"
TextBox1.Text = ""
TextBox2.Text = ""
TextBox4.Text = ""
End Sub

Private Sub ListBox1_Click()
Call rs
curnamn = ListBox1.Value
TextBox1.Text = ListBox1.Value
Dim a, s, d, e, f
a = InStr(1, alist.List(ListBox1.ListIndex, 0), "$B")
s = Mid(alist.List(ListBox1.ListIndex, 0), a + 3)
d = Blad3.Cells(s, 3)
TextBox4.Text = d
e = Blad3.Cells(s, 4)
f = Blad3.Cells(s, 5)
TextBox2.Text = e
TextBox5.Text = f
currow = s
'Call MsgBox(d)
a , s, d, e, f = Nothing
End Sub

Private Sub OptionButton1_Click()
If OptionButton3.Value = True Then
TextBox3.Text = "1,0"
TextBox3.Enabled = False
Else
TextBox3.Enabled = True
End If
End Sub

Private Sub OptionButton2_Click()
If OptionButton3.Value = True Then
TextBox3.Text = "1,0"
TextBox3.Enabled = False
Else
TextBox3.Enabled = True
End If
End Sub

Private Sub OptionButton3_Click()
If OptionButton3.Value = True Then
TextBox3.Text = "1,0"
TextBox3.Enabled = False
Else
TextBox3.Enabled = True
End If
End Sub

Public Sub printit()
Call Blad2.PrintOut(1, 1)
End Sub
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.Number & ":" & Err.Description
    Call MsgBox(Err.Description)
    Exit Sub
End Sub

Private Sub TextBox1_Change()
If Not TextBox1.Text = curnamn Then
currow = ""
End If
End Sub


Private Sub UserForm_Initialize()
currow = ""
End Sub

don't bother trying to read some of the strings if you ain't from scandinavia

and yes there i afew unused variables At the moment.

 

[Epoc]

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

i don't specify the search in any level so it should use the default values @ all times since i haven't specify'd the search directly in excel either.

 

[RoryA]

Yes you do:

xlLastRow = .Cells.FIND("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row

 

[Norie]

In this piece of code do you see anything missing?

Set rngFind = .FIND(TARGET)

Compare it to this earlier code, which appears to have a few more arguments.

xlLastRow = .Cells.FIND("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row

One other thing, I'm wondering why FIND is all uppercase - it shouldn't be, do you have a sub/variable/otter named FIND somewhere?

PS You really should lose the On Error stuff.

 

[Epoc]

Yes you do:

xlLastRow = .Cells.FIND("*", .Cells(1), xlFormulas, _
        xlWhole, xlByRows, xlPrevious).Row

right... i always asks stupid questions and i always end up hating myself
Thanks guys.

 

[Epoc]

now to try fixxing it ...