Macro para buscar un valor y devolver su "Información"(Indice y Coincidir en EXCEL

H   o la:

En VBA no es necesario declarar las variables, solamente en algunos casos.

Por otra parte, tampoco es necesario que pases el valor del control (textbox, combobox) a una variable y luego la variable a la celda, lo puedes hacer directamente del control a la celda.

Te anexo el código, en la primera parte establezco en los objetos h1 y h2 las hojas, de esa forma puedes hacer referencia a las hojas si importar en cuál hoja estés ejecutando el formulario.

Private Sub CommandButton1_Click()
'Act.Por.Dante Amor
    Set h1 = Sheets("Materiales")
    Set h2 = Sheets("Hoja2")
    '
    If MATERIAL0.Value = "" Then
        MsgBox "Captura un material válido"
        Exit Sub
    End If
    '
    Set b = h1.Range("B:B").Find(MATERIAL0.Value, lookat:=xlWhole)
    If b Is Nothing Then
        MsgBox "El material no existe"
    Else
        h2.Cells(3, "A") = CAPA0.Value
        h2.Cells(3, "B") = MATERIAL0.Value
        h2.Cells(3, "D") = ESPESOR0.Value
        h2.Cells(3, "C") = h1.Cells(b.Row, "C")
    End If
End Sub

Según tu ejemplo, cada vez que presiones el botón, siempre te va a poner los datos en la celdas de la fila 3, pero si lo que quieres es agregar un registro cada vez que presiones el botón, entonces sería así:

Private Sub CommandButton1_Click()
'Act.Por.Dante Amor
    Set h1 = Sheets("Materiales")
    Set h2 = Sheets("Hoja2")
    '
    If MATERIAL0.Value = "" Then
        MsgBox "Captura un material válido"
        Exit Sub
    End If
    '
    Set b = h1.Range("B:B").Find(MATERIAL0.Value, lookat:=xlWhole)
    If b Is Nothing Then
        MsgBox "El material no existe"
    Else
        u = h2.Range("D" & Rows.Count).End(xlUp).Row + 1
        h2.Cells(u, "A") = CAPA0.Value
        h2.Cells(u, "B") = MATERIAL0.Value
        h2.Cells(u, "D") = ESPESOR0.Value
        h2.Cells(u, "C") = h1.Cells(b.Row, "C")
        MsgBox "Registro agregado"
    End If
End Sub

'S aludos. Dante Amor. Recuerda valorar la respuesta. G racias

Dante te agradezco la respuesta. La utilicé y ha funcionado, ahora veré cómo indico como respuesta a mi problema. Sin embargo te hago una simple objeción. Digamos que yo quizá tengo el mismo problema en el futuro y necesito resolver por mi propia cuenta. Yo buscaba una solución simple como "cells(3,3)=Indice(Rango......) etc etc etc, en la misma línea, y no una rutina como la que me has dado ¿Hay alguna solución de ese tipo? No desvalorizo tu trabajo, todo lo contrario, me ha gustado. Lo de las variables he entendido que no hace falta, simplemente en la línea en que haga referencia al combobox pongo el nombre del combobox y listo. No conozco mucho sobre macros pero... "Set" qué función cumple? he buscado y sería como una variable, puede ser? Gracias por tu respuesta y solución

Buscar el valor se puede resolver de varias formas. La mejor de ellas es la que te puse, sin embargo, podemos poner la fórmula en la misma línea, quedaría así:

Private Sub CommandButton1_Click()
'Act.Por.Dante Amor
    Cells(3, "A") = CAPA0.Value
    Cells(3, "B") = MATERIAL0.Value
    Cells(3, "D") = ESPESOR0.Value
    Cells(3, "C") = "=VLOOKUP(RC[-1],Materiales!C[-1]:C,2,0)"
End Sub

Ejecuta la macro sobre la hoja2.

Te respondo tu duda, Set es para establecer un objeto en una "variable objeto". En mi ejemplo estoy estableciendo en la variable h1 el objeto sheets("materiales"), de esa forma puedo utilizar h1 como si estuviera haciendo referencia a sheets("materiales")

Al final de mi respuesta tienes 2 opciones para valorar la respuesta: "Votar" y "Excelente", si pusiste votar significa que tienes alguna duda, si no es así, apreciaría que cambiaras tu valoración.

Hola Dante, gracias por tu respuesta, lo del set lo había comprendido bien, y la "segunda solución" me satisface la duda, sin embargo ya había utilizado la macro que me habías pasado. Desde un principio he valorado tu respuesta como excelente. Ahora tengo otra duda, pero será parte de otro "posteo", hay alguna manera de notificarte de la nueva duda? Muchas gracias por todo

Lo puedes consegir con una busqueda

De esta manera

    Set h = Sheets("results")
    Set b = h.Columns("B").Find(combobox1)
    If Not b Is Nothing Then
    If b = comboBox1.Value Then
    Workbooks("hoja2").Activate
   Cells(3,3)  = h.Cells(b.Row, "c")

end if

end if

Si te silve no olvido valorar para cerrar la pregunta 

Solo cambia results por el nombre donde estas la base de datoe en tu casa mateiriales