Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Search form

0

Please, I have been trying to create a search form but not getting result.

Below is my sample code:

Private Sub cmdbtn_Enter_Click()
Dim ColNum As Long
Dim LastRow As Long

ColNum = 1

LastRow = Range("A5" & Rows.Count).End(xlUp).Value

Do Until Cells(LastRow, 9).Value = ""
If InStr(1, Cells(LastRow, 1).Value, txtSearch_Word.Value, vbTextCompare) > 0 Then
Worksheets("Search_Results").Value = Cells(ColNum, 1).Value
Worksheets("Search_Results").Value = Cells(ColNum, 2).Value
Worksheets("Search_Results").Value = Cells(ColNum, 3).Value
Worksheets("Search_Results").Value = Cells(ColNum, 4).Value
Worksheets("Search_Results").Value = Cells(ColNum, 5).Value
Worksheets("Search_Results").Value = Cells(ColNum, 6).Value
Worksheets("Search_Results").Value = Cells(ColNum, 7).Value
Worksheets("Search_Results").Value = Cells(ColNum, 8).Value
Worksheets("Search_Results").Value = Cells(ColNum, 9).Value
Worksheets("Search_Results").Value = Cells(ColNum, 10).Value
Worksheets("Search_Results").Value = Cells(ColNum, 11).Value
Worksheets("Search_Results").Value = Cells(ColNum, 12).Value
Worksheets("Search_Results").Value = Cells(ColNum, 13).Value
Worksheets("Search_Results").Value = Cells(ColNum, 14).Value

ColNum = ColNum + 1

End If


Loop


listDisplay.RowSource = "Search_Results!A1:N43"
If LastRow = 1 Then
MsgBox ("Data Not Available")
End If

End Sub

Answer
Discuss

Discussion

Hi Akees.
You have moved to the top of my list for the most obscure question.
I think that in order to help however this line has very little chance of working
Worksheets("Search_Results").Value = Cells(ColNum, 1).Value
You seem to be missing where you want to but the Cells(ColNum, 1).Value it should not be a work sheet. Also Cells(ColNum, 1) Think the first bit is a row number and second is the column.
I think if you provide a sample workbook and a bit more of a clue as to what you are searching for and where you expect to find we might be able to help or offer up some suggestions.
Also as an aside the word Form has a specific meaning in Excel VBA and I am not sure ifthis is what you want
k1w1sm (rep: 197) Sep 8, '19 at 4:53 pm
The best way for you to make progress is to try and run your sample code. Place the cursor anywhere in the procedure and press F5. The program will crash. Take note of the error message (and number) and press “Debug” below the error message. The VB Editor will highlight the line of code that caused the crash.
Helped by the error description, you can then try to correct the syntax. Should you not succeed in that endeavour you can ask a question here about that one line of code and the error it causes.
Your code contains at least a dozen syntax errors. Too much, by far, to handle in one thread here.
Variatus (rep: 4889) Sep 9, '19 at 12:08 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login