1

I am trying to use VB.Net to loop through an Excel data. What I want is for VB to loop through column B in Excel and find matching values in textbox1.text and a message box should say "hello".

I have been able to make VB.Net open Excel and my Workbook, but when I run the code, it gives me an error at

if .Cells(i, 1)=LowerCode Then

I have been stuck on this for hours. Please any suggestions or ideas would be appreciated. Below is my code:

Dim LowerCode As Integer
Dim FinalRow As Integer
Dim i As Integer

LowerCode = TextBox1.Text
FinalRow = xlWorkSheet.Range("B30").End(Excel.XlDirection.xlUp).Row

With xlWorkSheet
    For i = 2 To FinalRow
        If .Cells(i, 1) = LowerCode Then
            MsgBox("Hello")
        End If
    Next i

End With
T.S.
  • 18,195
  • 11
  • 58
  • 78
Sotie
  • 11
  • 1
  • 2
  • Do you have option strict on? Id advise it. Have you tried "If .Cells(i,1).Value = LowerCode" – majjam Jun 30 '15 at 17:08
  • http://stackoverflow.com/questions/2454552/whats-an-option-strict-and-explicit – majjam Jun 30 '15 at 17:08
  • What's the error? Have you tried breaking into the code and see what's the value if the cell you are looking at? – the_lotus Jun 30 '15 at 17:09
  • When you say `vb.net`, do you really mean `VBA`? Or, do you use `Intrerop`. If you work with visual studio and you have Excel file, you can connect to it like you connect to any table in any database and then you can massage and calculate your data anyway you want. – T.S. Jun 30 '15 at 19:22

1 Answers1

1

should you be looping through the rows and looking for that indexed cell value like this 

   for i= 2 to finalrow
       if xlWorkSheet.rows(i).cells(1).text = Lowercode then
          msgbox()
       end if
   next i
Grant Wilson
  • 23
  • 7