If value is not found while searching excel

Question

I have the below code:

        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet1 As Excel.Worksheet
        Dim rng As Excel.Range
        Dim codeabc As String
        Dim i As Integer
        If StoreNumber.Text = String.Empty Then
            MsgBox("Please fill out 'Store Number'")
            Exit Sub
        End If
        xlApp = CreateObject("Excel.Application")
        xlBook = xlApp.Workbooks.Open("C:\Users\jefhill\Desktop\MyUPS.csv")
        xlSheet1 = xlBook.Worksheets(1)
        rng = xlSheet1.Range("a1:a3000")
        codeabc = (StoreNumber.Text)
        For i = 1 To rng.Count
            If rng.Cells(i).Value = codeabc Then
                Address.Text = (rng.Cells(i).offset(0, 1).value()) & vbCrLf & (rng.Cells(i).offset(0, 2).value()) & " " & (rng.Cells(i).offset(0, 3).value()) & " " & (rng.Cells(i).offset(0, 4).value())
                Phone.Text = (rng.Cells(i).offset(0, 5).value())
            End If
        Next i
        xlBook.Close()

The above works fine. But I am trying to add a way where if the value you are searching is NOT found. I have tried adding a 'else' to the 'if' statement, but that just had it give the error every time.

What error are you getting?

user990423
– user990423

2015-06-16 21:45:09 +00:00
Commented Jun 16, 2015 at 21:45 — user990423
– user990423, Commented Jun 16, 2015 at 21:45

James Thorpe · Accepted Answer · 2015-06-16 21:45:18Z

2

You need another variable to track if it was found:

    Dim found as Boolean
    found = False
    For i = 1 To rng.Count
        If rng.Cells(i).Value = codeabc Then
            Address.Text = (rng.Cells(i).offset(0, 1).value()) & vbCrLf & (rng.Cells(i).offset(0, 2).value()) & " " & (rng.Cells(i).offset(0, 3).value()) & " " & (rng.Cells(i).offset(0, 4).value())
            Phone.Text = (rng.Cells(i).offset(0, 5).value())

            'set the flag
            found = True
        End If
    Next i

    If Not found Then
        'logic if not found
    End If

answered Jun 16, 2015 at 21:45

James Thorpe

32.3k6 gold badges76 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dwb Over a year ago

That worked perfect - and you were so quick I cannot even answer question answered yet! :D

Collectives™ on Stack Overflow

If value is not found while searching excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related