EXCEL VBA , Search Line Error 1004

Question

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

Try qualifying the range with a workbook and worksheet name before the `Range("analisegeral").end...` line -- assuming there is a named range by that name (are you sure there is? Double check). Also, it seems like you were trying to `activate` the worksheet, but I could be wrong. — chillin, Jul 17 '18 at 09:23
`ref = Cells(i, 8)` is on the same line as `For i = 2 To last `. Is it intentional? If so, separate these two statements with `:` or move `ref = Cells(i, 8)` to another line. Also, you can't select range if you you're not on the sheet where the range is located. — AntiDrondert, Jul 17 '18 at 09:27
I gather that the analisegeral worksheet is **not** the one with the button or the one whose private worksheet code sheet that private sub is in. — , Jul 17 '18 at 09:29
Please confirm that you have a named range called *analisegeral* on the *analisegeral* worksheet. — , Jul 17 '18 at 09:31
@AntiDrondert It's not on the same line , only on stackoverflow... And i have a `Sheet("...").Select` — Pedro Gaspar, Jul 17 '18 at 09:48
No. I was perfectly specific and my inquiries require lucid responses, not vague requests for validation. — , Jul 17 '18 at 09:51

score 2 · Accepted Answer · answered Jul 17 '18 at 09:49

2

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

answered Jul 17 '18 at 09:49

AntiDrondert

1,128
8
21

Hi , i got it working but i was wondering if it was possible to return more than one value and show it as a list on textbox? – Pedro Gaspar Jul 17 '18 at 12:24
As a string with delimiter? Otherwise it won't be a TextBox but a ListBox. – AntiDrondert Jul 17 '18 at 12:28
I'ts going to be a combobox , but i want to list all the matching values if possible – Pedro Gaspar Jul 17 '18 at 12:56
I advise you to look into [Validation object](https://msdn.microsoft.com/en-us/vba/excel-vba/articles/validation-object-excel), it might be a bit easier to manipulate with than TextBoxes, ComboBoxes or ListBoxes. You can dynamically add matching values to either [collection](https://msdn.microsoft.com/en-gb/vba/language-reference-vba/articles/collection-object) or [dictionary](https://msdn.microsoft.com/en-gb/vba/language-reference-vba/articles/dictionary-object) in your `else` clause. – AntiDrondert Jul 17 '18 at 13:17
I know i just want to know the code to show not only the first matching line but also the other lines – Pedro Gaspar Jul 17 '18 at 13:39
@PedroGaspar You can always start a separate question, that is how SO works anyway. It is considered a bad tone to a) answer the question in comments b) make follow-up questions through editing original question or comments. You can be lucky enough to find out that there might be already similiar asked question with an accepted answer. – AntiDrondert Jul 17 '18 at 13:59