1

I would get help if possible.
I have two worksheets in the same workbook. Through a form, I search the "Dados" worksheet and copy the result to the "Pesquisa" worksheet. The results are shown in a listview. When I double click on a result, it occurs: "Error 1004 Application definition error or object definition". I do not understand the reason for the error, since I've already reviewed my code and found no problems.

Thank you very much for your help. Below, follow my code. 

    Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)  
      Sheets("Dados").Range("A" & Sheets("Pesquisa").Range("W" & ListBox1.ListIndex + 2)).Select
    End Sub
J.schmidt
  • 721
  • 5
  • 27
Evaldo
  • 57
  • 1
  • 6
  • If you add a control expression for the (ListBox1.ListIndex + 2), what number do you get? – Ass3mbler Jan 25 '19 at 14:52
  • You are calling the range incorrectly I believe. If I assume `ListBox1.ListIndex + 2` is a number, currently it resolves to `Range("A" & "W2")` which isn't correct. You need something like `...Range("A1:"& ...)` – BruceWayne Jan 25 '19 at 14:52
  • As a guess, I'd say the reference to `Sheets("Pesquisa").Range("W" & ListBox1.ListIndex + 2)` is not returning a valid number. I'd recommend breaking out those nested references into intermediate variables to make clear what you're trying to access, so you can check values along the way. – PeterT Jan 25 '19 at 14:52
  • Wait - You're calling the range on `Dados` sheet, but also trying to `Select` a range from another. I believe that's a reason it's failing as well. – BruceWayne Jan 25 '19 at 14:58
  • @Ass3mbler, in this case, I get number 2. – Evaldo Jan 25 '19 at 16:43

3 Answers3

4
Sheets("Dados").Range("A" & Sheets("Pesquisa").Range("W" & ListBox1.ListIndex + 2)).Select

That's a lot of things going on in a single statement, with several reasons for things to go awry.

Break it down.

First get your Worksheet objects:

Dim dadosSheet As Worksheet
Set dadosSheet = ActiveWorkbook.Worksheets("Dados") 'possible error 9 here

Dim pesquisaSheet As Worksheet
Set pesquisaSheet = ActiveWorkbook.Worksheets("Pesquisa") 'possible error 9 here

Next we want to build a Range address string in column W, from ListBox1.ListIndex, and get a rowIndex from that cell in the pesquisaSheet:

Dim rowSourceAddress As String
rowSourceAddress = "W" & ListBox1.ListIndex + 2 ' definitely a positive integer

Dim rowIndex As Long
rowIndex = pesquisaSheet.Range(rowSourceAddress).Value ' possible error 13 here
Debug.Assert rowIndex > 0 ' suspect this assertion will fail

Then we want to build another Range address string in column A:

Dim targetAddress As String
targetAddress = "A" & rowIndex ' if rowIndex isn't a positive integer...

Dim targetRange As Range
Set targetRange = dadosSheet.Range(targetAddress) '...error 1004 is thrown here

And lastly we want to Select that range:

targetRange.Select

Sure, it's more code - and arguably a bit more local variables than needed. But with everything explicit and every member call in its own instruction, any given instruction has only 1 reason to fail, which makes debugging much easier.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • 2
    alright alright alright *matthew mcconaughey voice* – Kubie Jan 25 '19 at 15:18
  • 1
    `Dim targetRange As Range Set targetRange = dadosSheet.Range(targetAddress)` Yes, the error actually thrown here, with the following message: Run-time error 1004: The Range method of the _Worksheet object failed. – Evaldo Jan 25 '19 at 16:34
  • 2
    @user2734504 so now you only need to look at the value of `sourceRowAddress` in the actual worksheet to find where the worksheet contains bad data =) – Mathieu Guindon Jan 25 '19 at 16:39
  • Ok, @MathieuGuindon! `rowSourceAddress` retuns the W2. But, there is no data in this cell. – Evaldo Jan 25 '19 at 16:56
  • 1
    @user2734504 and that empty cell makes `targetAddress` be `A0`, which is why error 1004 is thrown :) – Mathieu Guindon Jan 25 '19 at 16:57
  • @MathieuGuindon, Right! If I set a value in W2, the error does not occur. However, when I double click on the results of the listview, the value of A & (W2 value) is highlighted. The correct one would be to highlight the value of A corresponding to the line on which the result is. For example: If W2 is equal to 1, double click always highlights cell A1 even if my registration is referring to line 2 or line 3 or line 5 and so on ... – Evaldo Jan 25 '19 at 17:08
  • 1
    @user2734504 looks like you're on track! – Mathieu Guindon Jan 25 '19 at 17:20
  • @MathieuGuindon, the solution found, based on your instructions, was to put the value of W2 equal to the index's value that interested me in a While Loop. The expression that was placed inside the loop was: `Sheets("Pesquisa").Range("H" & n).Offset(1, 15).Value = ActiveCell.Row` So, it works fine! Thank you! =) – Evaldo Jan 25 '19 at 17:33
2

Not sure if this is the problem, but you may need to include .Value in your expression.

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)  
    Sheets("Dados").Range("A" & Sheets("Pesquisa").Range("W" & ListBox1.ListIndex + 2).Value).Select
End Sub

Additionally, are you confident that the value at Sheets("Pesquisa").Range("W" & ListBox1.ListIndex + 2).Value is a positive integer? If not, this will throw that error you are receiving.

Kubie
  • 1,551
  • 3
  • 12
  • 23
  • 1
    Indeed, that's the one reason error 1004 could be thrown - have an upvote! =) – Mathieu Guindon Jan 25 '19 at 15:23
  • Yes, it's an integer value (use as a sequential code to identify the people registered in the Dados Worksheet). But the error still occurs even after inserting .Value. =( – Evaldo Jan 25 '19 at 15:36
  • 1
    @user2734504 `.Value` was implicit, making it explicit makes no real difference (other than enhancing the readability). Try breaking down the expression as suggested in another answer; there's an assertion that will definitely fail. – Mathieu Guindon Jan 25 '19 at 15:45
  • 1
    Couple of thoughts, first try `MsgBox Sheets("Pesquisa").Range("W" & ListBox1.ListIndex + 2).Value` and see what value you get. Second, you might need a `Worksheet` reference before the `Listbox1`, so for example: `Sheets("Pesquisa").Range("W" & Sheets("Pesquisa").ListBox1.ListIndex + 2).Value`. Third, I know sometimes directly referring to Comboboxes doesn't work for me so I have to do something along the lines of: `Sheets("Pesquisa").OLEObjects("ListBox1").Object.ListIndex`, might not be needed in your case but wanted to give an fyi – Kubie Jan 25 '19 at 15:45
  • 1
    But yea definitely look at @MathieuGuindon 's answer and you can really narrow down why the error is happening – Kubie Jan 25 '19 at 15:46
  • 1
    The `ListBox1` doesn't strike me as a problem though; the code seems to be in a handler for that very listbox control, so its location makes no difference: if it's a UserForm, then it's implicitly `Me.ListBox1`; if it's a Worksheet, then it's still implicitly `Me.ListBox1` - qualifying it with anything other than `Me` makes it a roundabout reference IMO ;-) – Mathieu Guindon Jan 25 '19 at 15:57
1
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    MsgBox "You selected:" & vbCrLf & ListBox1.ListIndex & ": " & ListBox1.List(ListBox1.ListIndex)
    'Sheets("Dados").Range("A" & Sheets("Pesquisa").Range("W" & ListBox1.ListIndex + 2)).Select
End Sub
simple-solution
  • 1,109
  • 1
  • 6
  • 13