Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to add a head to the search results in Excel 2016 using VBA

Status
Not open for further replies.

duytoi

Vendor
Jul 24, 2024
1
0
0
VN
Here is my entire VBA code. I have very limited knowledge of Excel and VBA, so I would greatly appreciate your help.
PHP:
Private Sub input_search_Change()
    Dim arr(), result, i As Long, a As Long, dk As String
    dk = input_search.Text
    
    arr = Sheets("Sheet1").Range("A4:D20006").Value
    ReDim result(1 To UBound(arr, 1), 1 To 6)
    
    For i = 1 To UBound(arr, 1)
        If arr(i, 2) Like "*" & dk & "*" Or _
         arr(i, 4) Like "*" & dk & "*" Then
        a = a + 1
        result(a, 1) = arr(i, 1)
        result(a, 2) = arr(i, 2)
        result(a, 3) = arr(i, 3)
        result(a, 4) = arr(i, 4)
        End If
    Next i
    
    lstwebsite = ""
    lstwebsite.Clear
    lstwebsite.List = result

End Sub
Private Sub UserForm_Initialize()
lstwebsite.List = Sheets("Sheet1").Range("A4:D20006").Value
End Sub

I have built a data table and a data search function for Excel using a VBA ListBox. Everything works perfectly, but now I want to add a title to the search results. Does anyone know how to do this?

Currently, the title is not included in the search results when the program runs.
list3_g7lfgo.png


This is my source data table, I would greatly appreciate any assistance

list4_ahg0kv.png
 
With a lot of guessing, I assume that you ask how to fill listbox headers headers if ColumnHeads property is set to true.

If so, it works only when RowSource property of ListBox is a valid address of a range, where the data is stored. The headers are from the row above. An alternative is to have a list without headers and add labels above.
However, if you need a list with headers:
- add worksheet ("SearchData") to keep search results, with headers in first row (it may be finally hidden), put required headers in A1:D1 (A1:F1? - depending on how many columns you need, 4 or 6; adjust it respectively below),
- store somewhere value of a, calculated in input_search_Change, skip the rest if a=0(),
- store search data in destination range:
[pre] ThisWorkbook.Worksheets("SearchData").Range("A2").Resize(a,4)=result[/pre]
- add data to list:
[pre] lstwebsite.Rowsource=ThisWorkbook.Worksheets("SearchData").Range("A2").Resize(a,4).Address(External:=True)[/pre]

Note that there is a VBA dedicated forum707


combo
 
By the statement;
duytoi said:
I want to add a title to the search results
Do you mean - have words SEARCH WEBSITE to rows 1-2 in column B?


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top