CODEWITHSUNDEEP
vbaexcel
lego tera
lego tera

Reputation: 21

VBA: After Find, FindNext works few times and then run-time error '380'

At first I had a problem with reloading a UserForm with next/previous row information, but I found a kind-of-a-solution, that you can see below. So that might be one problem.

I have variables given in the first UserForm and then Find:

Private Sub CommandButtonFIND_Click()
'on module public variables, e.g.
'Public NeedText As String
'Public KOrow As Variant
'Public wHat As String
'Public fInal As String
'Public wHere As Variant
wHat = "Ordered"
wHere = "AB"
fInal = x1Sheet.UsedRange.Rows.Count
Dim FndRng As Range
With x1Sheet.Range(wHere & "2:" & wHere & fInal)
    Set FndRng = .Find(What:=wHat, LookIn:=xlValues, 
MatchCase:=False)
If Not FndRng Is Nothing Then ' found it!
    KOrow = FndRng.Row
    NeedText = "Text " & x1Sheet.Range("A" & KOrow) & "/" &
x1Sheet.Range("B" & KOrow)
    UserFormMT.Show
    UserFormMT.MultiPage2.SetFocus
    Me.Hide
Else
    MsgBox "No more rows to handle"
End If
End With
End Sub

And on the UserFormMT the following:

Private Sub CommandButtonNEXT_Click()
If wHat = "" Then
    KOrow = KOrow + 1
Else
    With x1Sheet.Range(wHere & KOrow + 1 & ":" & wHere & fInal)
    Set FndRng = .Find(What:=wHat, LookIn:=xlValues,
      searchdirection:=xlNext, MatchCase:=False)
    If Not FndRng Is Nothing Then 'found next!
        KOrow = FndRng.Row
        NeedText = "Text " & x1Sheet.Range("A" & KOrow) & "/" & 
              x1Sheet.Range("B" & KOrow)
          'Tried e.g:
          'Me.Repaint
          'userform_initialize
          'userform_activate
        Unload Me
        UserFormMT.Show
    Else
        CommandButtonNEXT.Visible = False
    End If
    End With
End If
End Sub

And this works - for a while. After 2-5 Next-clicks on the second UserForm, then code stops on UserFormMT.Show and gives run-time error '380': Could not set the value property. Invalid value property.

Maybe I just don't see something obvious anymore, so I'm grateful for any help.

Upvotes: 0

Views: 99

Answers (1)

Miqi180
Miqi180

Reputation: 1691

Try using On Error Resume Next before the find statement like this:

On Error Resume Next
Set FndRng = .Find(What:=wHat, LookIn:=xlValues,
  searchdirection:=xlNext, MatchCase:=False)
If Err.Number <> 0 Then Debug.Print Err.Description ' remove in production
On Error Goto 0

The find statement will throw an exception if it doesn't find a matched value and 

If Not FndRng Is Nothing Then

in the next line of code will not catch it, because the error has already occurred.

Upvotes: 3

Related Questions