CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
Pedro Gaspar
Pedro Gaspar

Reputation: 9

EXCEL VBA , Search Line Error 1004

I am trying to run an excel vba form to search through the lines, but for some unknown reason I get the error:

Method Range of object Global failed 

Private Sub CommandButton3_Click()
    Dim last, i As Integer
    Dim ref, lote As String
    'Sheets("analisegeral").Visible = True
    Sheets("analisegeral").Select
    last = Range("analisegeral").End(xlUp).Row + 1  

       For i = 2 To last                         ref = Cells(i, 8)
          lote = Cells(i, 13)

          If TextBox1.Text = ref Then
             TextBox2.Text = lote
             GoTo fim
          End If

       Next i

       If TextBox1.Text <> ref Then
          TextBox2.Text = ""
          MsgBox "Referência não encontrada!", vbInformation
          TextBox1.Text = ""
          TextBox2.Text = ""
          GoTo fim
       End If
    fim:
End Sub

Upvotes: 0

Views: 176

Answers (1)

AntiDrondert
AntiDrondert

Reputation: 1149

There are few issues with your code.

Invalid declaration

Dim last, i As Integer
Dim ref, lote As String

Note that last and ref are declared as Variant type here, unless it was your intent, change it to following:

Dim last As Integer, i As Integer
Dim ref As String, lote As String

Failing to activate worksheet where range is located

'Sheets("analisegeral").Visible = True
Sheets("analisegeral").Select

The fact that your sheet is hidden (or very hidden) disallows it's selection.
Probably this is the case of your error.

Wrong method of calculating last row number

last = Range("analisegeral").End(xlUp).Row + 1

Given you will actualy select analisegeral sheet, this still doesn't make sense:
Range("NamedRange") is a construction that allows to refer to previously named range (either with VBA or manualy). Unless you have one, this will raise another error. Perhaps you meant something like this?

last = Range("A" & Rows.Count).End(xlUp).Row

This will give you a number of column A last row.

Final advice: avoid using Select

Upvotes: 2

Related Questions