Select value in listbox to be taken to worksheet value

[ipbr21054]

Morning,
I have a userform with values in a Listbox.
The code below is on a command button.

I would select a value in the Listbox & run the code on the command button to then be taken to that selection on the worksheet of which the range for it is J6 then down the page.
The code is nearly correct BUT doesnt select the value in column J for some reason.

Do you see why this might be.

Thanks

Private Sub SelectAndGo_Click()
  With ThisWorkbook.Worksheets("DATABASE")
        Dim data As Variant
        data = .Range("J6:J" & .Cells(.Rows.Count, "J").End(xlUp).Row).Value
  End With
  
   Cells.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
  Unload HondaKeyCode
End Sub

 

[portews]

A couple of things.
Set "data" as a range instead of a variant. Also you don't want to look for the .value at the end (.value will give you what's in that cell vs the location of it)
You weren't telling the "find" where to look, it needs to be in the form of <range>.find. You had Cells.find, but Cells didn't refer to anything.
I also moved the dim statements first, above the With.

Private Sub SelectAndGo_Click()
Dim data As Range

With ThisWorkbook.Worksheets("Sheet2")
    Set data = .Range("J6:J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
End With
  
   data.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
 Unload HondaKeyCode
End Sub

 

[ipbr21054]

Hi,
Thats gives me a mismatch error & the below is shwon in yellow.

   data.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
 Unload HondaKeyCode

 

[portews]

My bad. Change "Sheet2" to "DATABASE"

 

[ipbr21054]

I did but the same message

 

[ipbr21054]

Changing data.Find to Cells.Find theres no error message BUT it doesnt select in column J

 

[ipbr21054]

This works.

Private Sub SelectAndGo_Click()
With ThisWorkbook.Worksheets("DATABASE")
Dim data As Variant

    data = .Range("J6:J" & .Cells(.Rows.Count, "J").End(xlUp).Row)
End With
  
   Cells.Find(What:=ListBox1.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
 Unload HondaKeyCode
End Sub