Searching Excel sheet from VB
Ive written some basic code from reading the values in certain cells in vb and putting them into a combo box. Now ive got a text box for membership numbers, (this is just an exercise im not from a company or anything ) How can i get VB to search the excel sheet for that number?
If the number is found then open formx else "Sorry you have enter the number incorrectly"
Cheers
VB Code:
Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")
appExcel.DisplayAlerts = False
appExcel.Visible = False
appExcel.ScreenUpdating = True
appExcel.Workbooks.Open FileName:="C:\names.xls"
appExcel.Worksheets("sheet1").Select
Private Sub Command1_Click()
If Form1.NamesCombo.ListIndex < 0 Then
MsgBox "You must select a student!", vbExclamation, "Error"
Exit Sub
End If
On Error GoTo errhandle:
Dim appExcel As Object
Set appExcel = CreateObject("Excel.Application")
Private Sub Command1_Click()
appExcel.DisplayAlerts = False
appExcel.Visible = False
appExcel.ScreenUpdating = True
appExcel.Workbooks.Open FileName:="C:\names.xls"
appExcel.Worksheets("sheet1").Select
appExcel.Save
appExcel.Quit
MsgBox "The records have been updated"
Exit Sub
errhandle:
Set appExcel = Nothing
MsgBox "An error has occured: " & Err.Description
appExcel.Quit
End Sub
This is the code i am using for opening and closing VB once data has been entered. Now what i want to do, once a value is typed into a text box i want to be able to search the excel sheet for that exact value, if it is not found then "error message" else load formX
Regards
Re: Searching Excel sheet from VB
You have two Command1_Click events and only one End Sub. also, code outside any procedures.
You need to use the .Find method off of the Range object.
VB Code:
With Worksheets(1).Range("A1:A500")
Set c = .Find(UserForm1.txtSearch.Text, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox c.Value
End If
End With