Doubt Search VBA Record

Question

I have a problem with my application, follow:

In my excel I have a sheet called parameters, on it I have two columns, where column A is the parameter name and column B is the parameter value.

I created a method called BuscarParametro where I receive the parameter name (column A) and return the parameter value (column B).

The method looks for in column A the value it received per parameter, finding it takes the number of the row and concatenates with column B to know the value of the parameter.

My doubts would be:

1. There’s a better and more efficient way to do this?

2. In the method code, returns nothing in the method Find, even if there is value in the spreadsheet

Below is the method:

Public Function BuscarParametro(Parametro As String) As String

    Dim Resultado As Range
    Dim Posicao As String

    Set Resultado = Sheets("parametros").Range("A1:A9999").Find(Parametro, _
                    LookIn:=xlValues, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows)

    If Resultado Is Nothing Then
        Exit Function
    End If

    Posicao = "B" & Resultado.Row

    BuscarParametro = Sheets("parametros").Range(Posicao)

End Function

Answer 1

You can use the formula VLOOKUP (or PROCV en) to find the required data in the same row in column B.

Syntax:

VLookup(parametro_que_quero_encontrar,
        range_que_quero_procurar, 
        retornar_valor_de_qual_coluna_do_range, 
        procurar_valor_aprox_ou_exato)

See applied in code:

Public Function BuscarParametro(param As String) As String

    result = Application.WorksheetFunction.VLookup _
            (param, Range("A:B"), 2, 0)

    BuscarParametro = result

End Function

Sub busca_param()

    MsgBox (BuscarParametro("item5"))

End Sub

And the result: