Write a function in vba macro to find value of cell

Question

I wrote a function in macro that it doesn't respond well. Description of macro:

I want to find value of a cell by 3 Characters

Item
Quality of my item
Date

I want to write a function that can accept different variable range in any sheet and search those 3 characters to find the value.

My code is here

Function findvalue(x1 As Variant, x2 As Range, x3 As Variant, x4 As Range, x5 As Variant, x6 As Range)
    Dim cell As Range
    Dim cell2 As Range
    Dim cell3 As Range

    For Each cell In x2 
        If x1 = cell.Value Then
            For Each cell2 In x4
                If x3 = cell2.Value Then
                    For Each cell3 In x6
                        If x5 = cell3.Value Then
                            findvalue = Cells(cell2, cell3)
                        End If
                    Next cell3
                    Exit For
                End If
            Next cell2
            Exit For
        End If
    Next cell
End Function

Item and quality of item should be in column and date should be in row

I attached my file

Sheet1 has my data and in Sheet2 I want to find my value

Download my sheet

I for one tend to not like downloading .xlsm files from sources that I don't know. Why not instead include a clearer description of what your function is supposed to do? — John Coleman
And what is your problem? (I, too, will not download from that site. There are too many Download buttons which don't seem to refer to your file, trying to get me to download something for which I have no use and may interfere with how my computer functions). — Ron Rosenfeld

Scott Craner Scott Craner · Accepted Answer · 2016-01-03T21:44:00

If I understand your question, this formula already exists as an array formula and does not need to be reinvented. I did not download your file, as like the others I do not trust sites like the one used.

I threw some junk data together like this:

Then set up a quick lookup fields like this:

In B21 in the following formula:

=INDEX(C2:O13,MATCH(B19,IF(A2:A13=A19,B2:B13),0),MATCH(C19,C1:O1,0))

It is an array formula and needs to be confirmed with Ctrl-Shift-Enter.

If you want a UDF then use this:

Function findvalue(x1 As Variant, x2 As Range, x3 As Variant, x4 As Range, x5 As Variant, x6 As Range)
Dim i&, j&
Dim rw&, clm&
For i = 1 To x2.Rows.Count
    If x2.Cells(i, 1) = x1 And x4.Cells(i, 1) = x3 Then
        rw = x2.Cells(i, i).Row

         For j = 1 To x6.Columns.Count
            If x6.Cells(1, j) = x5 Then
                clm = x6.Cells(1, j).Column
                findvalue = ActiveSheet.Cells(rw, clm)
                Exit Function
            End If
        Next j
    End If
Next i

findvalue = "N/A"
End Function

Write a function in vba macro to find value of cell

1 Answers