Wrong column value selected after selecting it from the Listbox

[ipbr21054]

Hi,
I am using the following code supplied below.

Some info for you.
Column J has code values in the following format.
M123
J089
A155
Z097
One letter followed by 3 numbers.

The code values are copied & sorted from A-Z in column AB
My userform opens & i now see all the code values listed A-Z in the Listbox.
So far all is ok.

Now when a user selects a code value from the Listbox the userform is closed at that value is supposed to then be selected in column J

What i am seeing more often than not is that the code value is actually being selected in column AB as opposed to column J


Do you see what the problem might be, Many Thanks

Option Explicit
Private Sub CloseForm_Click()
Unload HondaKeyCode
End Sub
Private Sub ListBox1_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
Private Sub UserForm_Initialize()
    Dim ws          As Worksheet
    Dim data        As Variant
    Dim itm         As String
    Dim cnt         As Long
    Dim i           As Long
   
    Set ws = ThisWorkbook.Worksheets("DATABASE")
   
    data = ws.Range("J6:K" & ws.Cells(ws.Rows.Count, "J").End(xlUp).Row).Value
   
    ReDim arr(0 To 1, 0 To UBound(data) - 1) As String
   
    cnt = 0
    For i = LBound(data) To UBound(data)
        itm = data(i, 1)
        If itm Like "[A-Za-z]###" Then
            arr(0, cnt) = itm
            arr(1, cnt) = data(i, 2)
            cnt = cnt + 1
        End If
    Next i
   
    If cnt > 1 Then
        ReDim Preserve arr(0 To 1, 0 To cnt - 1)
        Sort2DArray arr()
        Me.ListBox1.List = Application.Transpose(arr())
    ElseIf cnt > 0 Then
        Me.ListBox1.Column = arr()
    End If
   
    ws.Range("AB6").CurrentRegion.ClearContents
   
    With Me.ListBox1
        ws.Range("AB6").Resize(.ListCount, .ColumnCount).Value = .List
    End With
   
End Sub
Sub Sort2DArray(ByRef arr() As String)

    Dim tmp1 As String
    Dim tmp2 As String
    Dim i As Long
    Dim j As Long
  
    For i = LBound(arr, 2) To UBound(arr, 2) - 1
        For j = i + 1 To UBound(arr, 2)
            If UCase(arr(0, i)) > UCase(arr(0, j)) Then
                tmp1 = arr(0, i)
                tmp2 = arr(1, i)
                arr(0, i) = arr(0, j)
                arr(1, i) = arr(1, j)
                arr(0, j) = tmp1
                arr(1, j) = tmp2
            End If
        Next j
    Next i
  
End Sub

 

[6StringJazzer]

You have a couple of issues here.

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

First, you are using Cells unqualified. This mean it will look at ActiveSheet. I don't know what sheet is active at this point, but I suggest you qualify it to be explicit. (This isn't causing the problem you are reporting, but would be a better coding practice.)

Second, Cells.Find will search the entire worksheet. If you are looking specifically in column J then you should use Range("J:J") instead of Cells.

And you are using LookAt:=xlPart. I don't know if this is causing an issue because I don't know what your data looks like, but if you are looking for
M123
it will find a match on a cell that has
ABCM1234

 

[ipbr21054]

Thanks for the reply when i change the following i then get a RTE 13 TYpe mismatch
Did i add the J J part correctly ?

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

 

[6StringJazzer]

I think the problem is that you want to look only in column J but ActiveCell is not in column J. Use this:

   Range("J:J").Find(What:=ListBox1.Value, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate

 

[ipbr21054]

I thought Range(“J6”:”J”) would have covered.
Anyway I only change the xlPart to xlwhole & at present been working fine.
Thanks for the advice.

 

[6StringJazzer]

I thought Range(“J6”:”J”) would have covered.

That is an invalid range expression.