0

So I have been stomped for days on end with trying to find a way around using the .Find function in my application which just needs to find the value typed in the textbox, and either return the value and row on an exact match, or display that the value is simply not in the spreadsheet. While a I have a working implementation without looping, anything I put into the textbox is sought as a Substring. This is not what I want.

I'm not versed in VBA, and all I see out there are VBA ways of accomplishing this with For Loops, which I am not having luck with finding the value in the cell. I'm currently doing this:

Dim xlApp = New Excel.Application  ' this starts new Excel Application
Dim xlWB = xlApp.Workbooks.Open("C:\Users\Me\Desktop\ExcelValues.xls")
Dim xlWS = xlWB.Worksheets("Sheet1")

Dim Value2Find_1 As String = txtValue1.Text

Dim Range2Use_1 = xlWS.Range("A1:A4000")        'Range to span the A Column.

Dim xlCell_A = Range2Use_1.Find(txtValue1.Text) 'Looks up the searched serial value in A Column.

Dim LastRow = xlWS.Range("A4000").End(Excel.XlDirection.xlUp).Row + 1                      

If Value2Find_1 <> Nothing Then
        Range2Use_1.Find(What:=Value2Find_1, MatchCase:=True)
        MessageBox.Show("Value of " & Value2Find_1 & " found in cell " & xlCell_A.Row)


    Else
        MsgBox("no match...")
End If

I can get a hit with this and even find where the value is in the row in the sheet, but even if I use a string for Value2Find_1 like MICHAEL, if I type MIC into the textbox, it's going to return the substring within MICHAEL at the given row. Because users will be searching for strings of variable length, I can't set a limit on the string value. Is there a solid example of accomplishing this WITHOUT a VBA example using a looping structure. I see Excel's Find function isn't really reliable as I cannot set an ExactMatch parameter. I've looked at other examples, and they seem extremely abstract, and do not explain well what is happening with getting values using the loop structure, and converting them from Integer to the String from the cell where the found item lives.

Note: Yes, I am using the Excel Interop... Imports Excel = Microsoft.Office.Interop.Excel

DesignerMind
  • 410
  • 4
  • 8
  • 29

1 Answers1

0

Use the parameter LookAt in Find function. Something like below

Range2Use_1.Find(What:=Value2Find_1, MatchCase:=True, LookAt:=xlWhole)
Mukul Varshney
  • 3,131
  • 1
  • 12
  • 19
  • That appears not to work. Still returns a `substring`. Strange is for some reason I'm struggling with this. I've written the following: `For i = 1 To LastRow If (Value2Find_1 = xlCell_A.Cells().Value) Then MessageBox.Show("Found the value...") ElseIf (Value2Find_1 <> xlCell_A.Cells().Value) Then MessageBox.Show("No val.") Exit Sub` And for some reason, it returns an error specifically on `If (Value2Find_1 = xlCell_A.Cells().Value) Then` as `Object variable or With block variable not set.`. – DesignerMind Jan 16 '17 at 00:20
  • If I have `CLOUD777` in the spreadsheet, and search for it, it finds it. But if I type `CLOUDD777` instead of `CLOUD777`, it is like it refuses to run the code for handling the mismatch, and instead errors out. Why is this happening? What do I need to do to properly handle an "Almost Match" as still a "True Mismatch." I cannot account for the value not being found, unless I type something completely different. – DesignerMind Jan 16 '17 at 00:25
  • within for loop, add a check if Not xlCell_A Is Nothing. If its not nothing then you are safe to use xlCell_A.Cells().Value – Mukul Varshney Jan 16 '17 at 04:04