0
Function test()

Dim result As String
Dim x As Integer
Dim search_value As String
Dim column As Integer
search_value = "esg001"
column = 1

For x = 2 To 3
Sheets(x).Select
Range("B:B").Select

On Error Resume Next
    Cells.Find(search_value).Select
    ActiveCell.Offset(0, column).Select

result = ActiveCell.Value



If search_value <> "" Then
GoTo ola
Else
End If
Next
ola:
    test = result


End Function
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
paulo
  • 1
  • Needs to specify more parameters of `Find`, specifically `LookIn` and `LookAt`. Also, *really* needs to [avoid Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). UDFs can't select. – BigBen Apr 21 '21 at 18:41
  • 2
    Also it appears like you are trying to recreate VLOOKUP. Why? – Scott Craner Apr 21 '21 at 18:42
  • 3
    `=IFERROR(VLOOKUP("esg001",Sheet2!B:C,2,FALSE),VLOOKUP("esg001",Sheet3!B:C,2,FALSE))` – Scott Craner Apr 21 '21 at 18:45
  • It's just a test function because I have 30 sheet to search. I had tried with vlookup at the first time but I had the same problem. Debugging it looks fine and gives the result I need. – paulo Apr 21 '21 at 21:11
  • At the last row of code when I debug it gaves test="ok" and that is what I want but when the function closes the cell that contains the formula returns #Value!. – paulo Apr 21 '21 at 21:21
  • Thanks to all. Problem solved after get rid off select. Kisses and hugs to all. – paulo Apr 22 '21 at 11:15

1 Answers1

1

as stated in the comments the following formula will do what you want:

=IFERROR(VLOOKUP("esg001",Sheet2!B:C,2,FALSE),VLOOKUP("esg001",Sheet3!B:C,2,FALSE))

Where Sheet2 and Sheet3 are the names of the sheets.

Now a couple of notes on your attempted code.

  • Do not use .Select. More info on that HERE
  • when using UDF avoid hardcoding ranges, pass them as parameters. The reason is that the formula would not update when the data updates if it is not a parameter.

This accepts two parameters: Search Value and which column to return. It also accepts as many ranges as desired to search for the value:

Function test(schVal As String, clm As Long, ParamArray schRng() As Variant) As Variant

    Dim i As Long
    For i = LBound(schRng) To UBound(schRng)
        If TypeOf schRng(i) Is Range Then
            If schRng(i).Columns.Count > clm Then Exit Function
            Dim rngArr() As Variant
            rngArr = Intersect(schRng(i), schRng(i).Parent.UsedRange).Value
            
            Dim j As Long
            For j = 1 To UBound(rngArr, 1)
                If rngArr(j, 1) = schVal Then
                    test = rngArr(j, clm)
                    Exit Function
                End If
            Next j
        Else
            test = "Parameters 3 and higher should be ranges"
            Exit Function
        End If
    Next i
    
    test = "Not Found"
End Function

Now you would call it (using the formula above as a template):

=TEST("esg001",2,Sheet2!B:C,Sheet3!B:C)

It will first

Scott Craner
  • 148,073
  • 10
  • 49
  • 81