code not workig my way

[kelly mort]

I have this code to populate my listbox. Instead on showing me the data as i have it on my sheet, it starts from the next column. I want it to show me the data starting from column B but it started with C.

I have got get it show the data the way it is on my sheet so that i can run the other codes because most of the events rely on the first column in the list box which is now not available.

Sub Lookup()
        Dim rngFind As Range
        Dim strFirstFind As String
        
        On Error GoTo errHandler:
        lstWin.Clear
        With Sheet1.Range("C3:C303")
        Set rngFind = .Find(txtLookup.Text, LookIn:=xlValues, lookat:=xlPart)
        If Not rngFind Is Nothing Then
        strFirstFind = rngFind.Address
        
        Do
        If rngFind.Row > 1 Then
        lstWin.AddItem rngFind.Value
        lstWin.List(lstWin.ListCount - 1, 1) = rngFind.Offset(0, 1)
        lstWin.List(lstWin.ListCount - 1, 2) = rngFind.Offset(0, 2)
        lstWin.List(lstWin.ListCount - 1, 3) = rngFind.Offset(0, 3)
        lstWin.List(lstWin.ListCount - 1, 4) = rngFind.Offset(0, 4)
        lstWin.List(lstWin.ListCount - 1, 5) = rngFind.Offset(0, 5)
        lstWin.List(lstWin.ListCount - 1, 6) = rngFind.Offset(0, 6)
        lstWin.List(lstWin.ListCount - 1, 7) = rngFind.Offset(0, 7)
        lstWin.List(lstWin.ListCount - 1, 8) = rngFind.Offset(0, 8)
        lstWin.List(lstWin.ListCount - 1, 9) = rngFind.Offset(0, 9)
        End If
        Set rngFind = .FindNext(rngFind)
        Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
        End With
        Me.Reg1.Enabled = False
        On Error GoTo 0
        Exit Sub
        
errHandler::
            MsgBox "Check your entry for errors"
    End Sub

 

[footoo]

With Sheet1.Range("C3:C303") ???

 

[kelly mort]

Is that the cause of my problem??

But when i can it to b3:b303 then i cant lookup the name in column c again. What should i do

 

[footoo]

I'm not sure what you want to do.

This bit:
= rngFind.Offset(0, 1)
returns the contents of column D in the same row as rngFind.
Is that what you want?

 

[kelly mort]

I'm not sure what you want to do.

This bit:
= rngFind.Offset(0, 1)
returns the contents of column D in the same row as rngFind.
Is that what you want?

I want the content of column B show in my first column of the listbox. I have 11 columns data on my sheet. And i need them all here in the listbox. I wae using rowsource first then i decided to make things a bit advanced then i had issues.

So i want to get them listed just on my sheet. For what i know my code is to look inside column C and return a search from what it took from the txtbox. But i am not getting that result

Hope i am clear enough. Thanks

 

[kelly mort]

By the way is there a way i could make this code shorter? The length is confusing me.

 

[footoo]

I want the content of column B show in my first column of the listbox. I have 11 columns data on my sheet. And i need them all here in the listbox.

Your code starts at column C :

lstWin.AddItem rngFind.Value [COLOR=#ff0000]'Column C[/COLOR]
lstWin.List(lstWin.ListCount - 1, 1) = rngFind.Offset(0,[COLOR=#ff0000] 1[/COLOR]) [COLOR=#ff0000]'Column D[/COLOR]
lstWin.List(lstWin.ListCount - 1, 2) = rngFind.Offset(0, [COLOR=#ff0000]2[/COLOR]) [COLOR=#ff0000]'Column E[/COLOR]
'etc.

Perhaps change to :

lstWin.AddItem rngFind.Offset(0, [COLOR=#ff0000]-1[/COLOR]) [COLOR=#ff0000]'Column B[/COLOR]
lstWin.List(lstWin.ListCount - 1, 1) = rngFind [COLOR=#ff0000]'Column C[/COLOR]
lstWin.List(lstWin.ListCount - 1, 2) = rngFind.Offset(0, [COLOR=#ff0000]1[/COLOR]) [COLOR=#ff0000]'Column D[/COLOR]
lstWin.List(lstWin.ListCount - 1, 3) = rngFind.Offset(0, [COLOR=#ff0000]2[/COLOR]) [COLOR=#ff0000]'Column E[/COLOR]
'etc

 

[kelly mort]

Your code starts at column C :

lstWin.AddItem rngFind.Value [COLOR=#ff0000]'Column C[/COLOR]
lstWin.List(lstWin.ListCount - 1, 1) = rngFind.Offset(0,[COLOR=#ff0000] 1[/COLOR]) [COLOR=#ff0000]'Column D[/COLOR]
lstWin.List(lstWin.ListCount - 1, 2) = rngFind.Offset(0, [COLOR=#ff0000]2[/COLOR]) [COLOR=#ff0000]'Column E[/COLOR]
'etc.

Perhaps change to :

lstWin.AddItem rngFind.Offset(0, [COLOR=#ff0000]-1[/COLOR]) [COLOR=#ff0000]'Column B[/COLOR]
lstWin.List(lstWin.ListCount - 1, 1) = rngFind [COLOR=#ff0000]'Column C[/COLOR]
lstWin.List(lstWin.ListCount - 1, 2) = rngFind.Offset(0, [COLOR=#ff0000]1[/COLOR]) [COLOR=#ff0000]'Column D[/COLOR]
lstWin.List(lstWin.ListCount - 1, 3) = rngFind.Offset(0, [COLOR=#ff0000]2[/COLOR]) [COLOR=#ff0000]'Column E[/COLOR]
'etc

It now shows the Column B first then jumps to D.

 

[footoo]

It now shows the Column B first then jumps to D.

Can't see how that's possible.
Did you change the code correctly?
Step through via F8 to see what's happening.

 

[kelly mort]

Can't see how that's possible.
Did you change the code correctly?
Step through via F8 to see what's happening.

Okay it has worked i missed some stuffs. But i can add only 10 columns not the 11.

After this line ,

lstWin.List(lstWin.ListCount - 1, 9) = rngFind.Offset(0, 8)

I get error when i click the lookup button.

How will i get the 11th column because the line ;

lstWin.List(lstWin.ListCount - 1, 10) = rngFind.Offset(0, 9)

Gives an error