Search with multiple search parameters

Question

I have a problem with a function i wrote in vb. The Tool opens a Excel Sheet and has too Search two Values in that sheet.

The Excel sheet is structured as follows:

the Function i Wrote, looks if the Value in the Column "M" and in the Column "N" in the same Row are the same as Creterium 1 and 2. if that is so, it will return the value in the Column "O"

My Code looks like that:

    Function twoStrSearch(ByVal criteria1 As String, ByVal criteria2 As String, ByVal strPrimarySearchColumn As String, _
                      ByVal Offset_Krit2 As Integer, ByVal Offset_result As Integer, _
                      ByVal objWorksheet As Microsoft.Office.Interop.Excel.Worksheet) As VariantType
    '********************************************************************************************
    'Function for Searching an Excel Sheet.
    'If the Sheet Contains the two Criterias in the same row it will return the search Value
    '********************************************************************************************
    'Parameter:                 Explanation:         
    'criteria1                  The first comparison value
    'criteria2                  The second comparison value
    'strPrimarySearchColumn     The Name of the Row where the first comparsion value is
    'Offset_Krit2               The Offset Value where the second comparison value is
    'Offset_Ergebnis            The Offset Value where the Search result is what will be returned
    'objWorksheet               The object of the Excel Sheet that should be searched in
    '********************************************************************************************

    Dim strAddress As String
    Dim area As Microsoft.Office.Interop.Excel.Range
    Dim range As Microsoft.Office.Interop.Excel.Range
    'Get's the letter of the Column
    strAddress = objWorksheet.Cells.Find(What:=strPrimarySearchColumn).Address
    strAddress = Mid(strAddress, 2, 1)
    area = objWorksheet.Columns(strAddress & ":" & strAddress) 'Range over the Column
    For Each range In area
        'If both criteria in the same Row are True then get the result
        If range.Value2.ToString = criteria1 And range.Offset(0, Offset_Krit2).Value = criteria2 Then
            twoStrSearch = range.Offset(0, Offset_result).Value
            Exit Function
        End If
    Next
    twoStrSearch = "--" 'if nothing found result is "--"
End Function

The Eroor is happening in the For Each loop if he Compars the Cell Values with the Criteria1 and 2.

I stuck now for a while and i thought maybe some of you have an Idea!

Answer 1

Not for Points (Only for explanation purpose)

Summary.

1. The way you are extracting address will give you error if the search text is in say Col AA1
2. You don't need address to construct your range. You can use the Column number.
3. No point looping though all the cells (1048576 in case of xl2007+) Construct your relevant range by finding the last row in the search column

Let's say your data looks like this

Code: (Tried and Tested in VS 2010 Ultimate + Office 2010 Prof.)

Try this. I have commented the code so let me know if there is anything which doesn't make sense.

Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    '~~> Define your Excel Objects
    Dim xlApp As New Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim objWorksheet As Excel.Worksheet

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        '~~> Open relevant file
        xlWorkBook = xlApp.Workbooks.Open("C:\MyFile.xlsx")

        '~~> Display Excel
        xlApp.Visible = True

        '~~> Set your first worksheet
        objWorksheet = xlWorkBook.Sheets(1)

        Dim Ret = twoStrSearch("1", "text-x", "TextNumber", -1, 1)

        MsgBox (Ret)

        '~~> Close the File
        xlWorkBook.Close (False)

        '~~> Quit the Excel Application
        xlApp.Quit()

        '~~> Clean Up
        releaseObject (objWorksheet)
        releaseObject (xlWorkBook)
        releaseObject (xlApp)
    End Sub

    Function twoStrSearch(ByVal criteria1 As String, ByVal criteria2 As String, ByVal strPrimarySearchColumn As String,
    ByVal Offset_Krit2 As Integer, ByVal Offset_result As Integer) As String
        Dim area As Excel.Range = Nothing
        Dim range As Excel.Range = Nothing
        Dim aCell As Excel.Range = Nothing
        Dim ColNo As Integer, lRow As Integer

        '~~> Find which column as the search text
        aCell = objWorksheet.Cells.Find(What:=strPrimarySearchColumn)

        '~~> Set it to "--" in case nothing is found
        twoStrSearch = "--"

        '~~> if found
        If aCell IsNot Nothing Then
            '~~> Get the column number
            ColNo = aCell.Column

            '~~> Get last row of that column
            lRow = objWorksheet.Cells(objWorksheet.Rows.Count, ColNo).End(Excel.XlDirection.xlUp).Row

            '~~> Construct your range from row 2 onwards. Row1 has headers
            area = objWorksheet.range(objWorksheet.Cells(2, ColNo), objWorksheet.Cells(lRow, ColNo))

            For Each range In area
                'If both criteria in the same Row are True then get the result
                If range.Value2.ToString = criteria1 And range.Offset(, Offset_Krit2).Value = criteria2 Then
                    twoStrSearch = range.Offset(, Offset_result).Value
                    Exit For
                End If
            Next
        End If

        releaseObject (area)
        releaseObject (range)
        releaseObject (aCell)

        Return twoStrSearch
    End Function

    '~~> Release the objects
    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject (obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub
End Class

Output:

Answer 2

The For Each Loop has two major problems: it iterates through all the ranges in a column without specifying that you actually want cells (a column is a range, a bunch of cells is a range, etc.), by default, it assumes that you want to iterate through whole columns and thus .Value2 triggers an error (it cannot be applied to many cells). Also you are using .ToString without confirming that the value in the given cell is not null (not Nothing). Corrected code:

For Each range In area.Cells
    'If both criteria in the same Row are True then get the result
    If (range.Value2 IsNot Nothing) Then
        If range.Value2.ToString = criteria1 And range.Offset(0, Offset_Krit2).Value = criteria2 Then
            twoStrSearch = range.Offset(0, Offset_result).Value
            Exit Function
        End If
    End If
Next

Also there are some other problems with your code, for example:

strAddress = objWorksheet.Cells.Find(What:=strPrimarySearchColumn).Address

This line would trigger an error in case of not finding what is expected. A more proper way would be:

strAddress = ""
Dim tempRange As Microsoft.Office.Interop.Excel.Range = objWorksheet.Cells.Find(What:=strPrimarySearchColumn)
If (tempRange IsNot Nothing) Then
    strAddress = tempRange.Address
End If

You can get the row/column directly from the range (tempRange), instead using your current method. Please, refer to the Siddharth Rout's commment above.