Multi condition search function doesn't work

Question

I have a function that takes 2 search values and 2 columns and 1 result column (total 5 arguments) and returns the value from the last column if the first two conditions are met. But it doesn't work and returns VALUE error. I can't figure out what's wrong here.

 Function betterSearch(searchValue1 As Variant, searchValue2 As Variant, _
            searchColumn1 As Variant, searchColumn2 As Variant, _
            resultColumn As Variant)
    Dim i As Integer
    For i = 1 To searchColumn1.Rows.Count
        If searchColumn1.Cells(i, 1).Value = searchValue1 _
                    And searchColumn2.Cells(i, 2).Value = searchValue2 Then
            betterSearch = resultColumn.Cells(i, 1)
                MsgBox ("found")
                Exit For
        End If
        betterSearch = "Not found"
    Next
End Function

Unless `searchColumn2` has 2 columns, you should be using `And searchColumn2.Cells(i, 1).Value` rather than `And searchColumn2.Cells(i, 2).Value` — Rory, Sep 06 '17 at 14:05
Debug the code step by step, and tell us if the code itself produces an error. — Luuklag, Sep 06 '17 at 14:06
Still not working. I try to `MsgBox (searchColumn1.Cells(i, 1).Value)` it returns `VALUE` and doesn't msgbox anything. — Ans, Sep 06 '17 at 14:07
It can be the Variable Type, try to analyze what type you are using, sometimes `As Variant`can cause errors. Also use `Option Explicit` — danieltakeshi, Sep 06 '17 at 14:09
`searchColumn1.Rows.Count` it returns 1048576 the max rows... are you sure about it? — Abhinav Rawat, Sep 06 '17 at 14:15
I found two issue : - condition not working : parse searchColumnValue to a variable before testing - searchColumn.Rows.Count, if you select all column result in zero, and you "VALUE" Error — grominet, Sep 06 '17 at 14:35

Abhinav Rawat · Accepted Answer · 2017-09-06T15:59:45.703

this worked for me fine-- Updated

 Sub foo44()
    Dim val As Variant
    Dim c1 As Range
    Dim c2 As Range
    Dim c3 As Range
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set c1 = ws.Range("C:C")
    Set c2 = ws.Range("D:D")
    Set c3 = ws.Range("E:E")

    Debug.Print betterSearch(23, 23, c1, c2, c3)
    End Sub


     Function betterSearch(searchValue1 As Variant, searchValue2 As Variant, searchColumn1 As Range, searchColumn2 As Range, resultColumn As Range)
        Dim i As Long
        Dim c1 As Variant
        Dim c2 As Variant
        For i = 1 To searchColumn1.End(xlDown).Row
             c1 = searchColumn1.Cells(i, 1).Value
             c2 = searchColumn2.Cells(i, 1).Value
            ' Debug.Print c1
             'Debug.Print c2
            If c1 = searchValue1 And c2 = searchValue2 Then
                betterSearch = resultColumn.Cells(i, 1).Value
                    Exit For
            End If
            betterSearch = "Not found"
        Next
    End Function

Can you explain what is the point of having `searchColumn2` as an argument if you are not going to use it in the function at all? I believe OP has the searchColumn 1 and 2 as adjacent columns in his workbook so that the code must have worked for him. I believe you should change the line as `c2 = searchColumn2.Cells(i, 1).Value`? — Tehscript, Sep 06 '17 at 15:45
Also if you have an empty cell in the `searchColumn1`, the code will stop looking for the rest of the values. You should find the last non-empty row from bottom to top, not from top to bottom. You can refer to [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) — Tehscript, Sep 06 '17 at 16:40

Tehscript · Answer 2 · 2017-09-06T15:37:36.043

0

Try getting column numbers with .column property. Also try to get the last used row number as per below:

Function betterSearch(searchValue1 As Variant, searchValue2 As Variant, _
            searchColumn1 As Range, searchColumn2 As Range, _
            resultColumn As Range)
    Dim i As Long
    For i = 1 To Cells(Rows.Count, searchColumn1.Column).End(xlUp).Row
        If Cells(i, searchColumn1.Column).Value = searchValue1 _
            And Cells(i, searchColumn2.Column).Value = searchValue2 Then
            betterSearch = Cells(i, resultColumn.Column)
            MsgBox ("found")
            Exit For
        End If
        betterSearch = "Not found"
    Next
End Function

edited Sep 06 '17 at 15:37

answered Sep 06 '17 at 14:21

Tehscript

2,556
2
13
23

@ScottCraner Yes I was about to fix that thanks. Actually I assumed it depending on the variable name, but surely it may be a range. – Tehscript Sep 06 '17 at 14:26
1

Another method would be `Intersect(searchColumn1,searchColumn1.Parent.UsedRange).rows` – Scott Craner Sep 06 '17 at 14:27
@ScottCraner `Intersect(searchColumn1,searchColumn1.Parent.UsedRange).rows` returns a value error without any useful info. The edited method works both for column and range. – Tehscript Sep 06 '17 at 14:33
sorry forgot the `.Count` at the end. – Scott Craner Sep 06 '17 at 14:35
And it is just a personal quirk, yours will work fine. – Scott Craner Sep 06 '17 at 14:42
1

@ScottCraner Definitely it works, thanks for this nice alternative! – Tehscript Sep 06 '17 at 14:42

Multi condition search function doesn't work

2 Answers2

Linked