I have a code which is mainly working except I have a validation error... My code has a search box where I put the surname in, the idea is that if the surname entered is in the excel sheet and a bunch of information is returned. This works as desired, as does my validation for no data entered in the search box.
What doesn't work is my validation for when I enter something in the search box which isn't a match (i.e. not in the excel sheet). Please see 'match validation' in the following code to see what I'm referring to do.
I just have no idea why it isn't working. I don't even get an error when I run the code and enter in wrong data, it just doesn't return an error message like it should and the form sort of freezes up (kinda like its in a non stop loop).
Any advice would be great, Thanks! Here some of the code:
'define objects
Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Open a existing workbook and sheet in excel
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Open(Filename:="c:\users\timothy\desktop\coding\output.xlsx")
oSheet = oBook.Worksheets(1)
Dim getSurname As String = ""
Dim getFirstname As String = ""
Dim getAge As String = ""
Dim getGender As String = ""
Dim getNum As Integer = 1
Dim getValidate As Integer = 0
While oBook.Worksheets(1).Range("A" & getNum).value <> searchInput.Text.ToUpper
getNum = getNum + 1
End While
'Length Validation (THIS WORKS)
If Len(searchInput.Text) = 0 Then
getValidate = getValidate + 1
End If
'Match validation (THIS DOES NOT WORK)
If oBook.Worksheets(1).Range("A" & getNum).value <> searchInput.Text.ToUpper Then
getValidate = getValidate + 1
End If
If getValidate = 0 Then
getSurname = oSheet.Range("A" & getNum).Value.ToString
getFirstname = oSheet.Range("B" & getNum).Value.ToString
getAge = oSheet.Range("C" & getNum).Value.ToString
getGender = oSheet.Range("D" & getNum).Value.ToString
outputData.Text = "SURNAME: " & getSurname & vbCrLf & "FIRSTNAME: " & getFirstname & vbCrLf & "AGE: " & getAge & vbCrLf & "GENDER: " & getGender & vbCrLf
Else
MsgBox("ERROR!! Please enter valid Quote Number.")
End If
This section of code
While oBook.Worksheets(1).Range("A" & getNum).value <> searchInput.Text.ToUpper
getNum = getNum + 1
End While
will continue to loop through the worksheet until it finds a match. Let's assume you get to the last row of your data with no match yet. The next "A" & getNum will be blank, which will not be a match, and so it will continue the While, until it hits your row limit and errors out. It never finds a match, so it will never hit your check.
However, I think if you put a check in there to look for those blank cells...
While oBook.Worksheets(1).Range("A" & getNum).value <> searchInput.Text.ToUpper
if oBook.Worksheets(1).Range("A" & getNum).value = "" then
exit while
else
getNum = getNum + 1
end if
End While
,then I think it will do what you are looking to do.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments