es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

BúsquedaV con múltiples criterios utilizando VBA y el método de fórmula de matriz.

13 Jul, 2023 Programación

Entonces, la idea es hacer uso del hermoso Método de Fórmula de Matriz y la idea detrás de él, cuando se necesita hacer un VLookUp con múltiples criterios en VBA.

La pregunta: ¿Podemos traducir esto a VBA?

{=INDEX(rango1, COINCIDIR(1, (A1=rango2)(B1=rango3)(C1=rango4), 0))}

¿Sin usar fórmulas en Excel en absoluto? Por ejemplo, sin hacer esto:

=AGREGAR(15, 6, ‘[TUR Master Report.xlsm]Archivo’!$B$2:$B$13/
((‘[TUR Master Report.xlsm]Archivo’!$B$2:$B$13>=DO2)*
(‘[TUR Master Report.xlsm]Archivo’!$B$2:$B$13<=DP2)*
(‘[TUR Master Report.xlsm]Archivo’!$A$2:$A$13=A2)), 1)

o cualquier cosa similar (.ArrayFormula, .Formula, etc.).

Estaba pensando en algo como esto foo = Coincidir(1, (A1=rangoA)(B1=rangoB)(C1=rangoC), 0), pero por supuesto no funciona, aunque está en la lógica de la fórmula de Excel. Hasta ahora he creado lo siguiente como solución alternativa:

Función GetLookupDataTriple(nombreTabla como String, buscarEnColumna como String, myArray como Variant) como Variant

Dim lo como ListObject
Set lo = Sheet1.ListObjects(nombreTabla)

Dim i como Long
para i = 2 hasta lo.ListColumns(myArray(0)).Range.Rows.Count
    si lo.ListColumns(myArray(0)).Range.Cells(RowIndex:=i) = myArray(1) entonces
        si lo.ListColumns(myArray(2)).Range.Cells(RowIndex:=i) = myArray(3) entonces
            si lo.ListColumns(myArray(4)).Range.Cells(RowIndex:=i) = myArray(5) entonces
                GetLookupDataTriple = lo.ListColumns(buscarEnColumna).Range.Cells(RowIndex:=i)
                salir de la función
            Fin si
        Fin si
    Fin si
siguiente i

GetLookupDataTriple = -1

Fin función

lo cual funciona bastante bien con 3 filtros, pero la idea es ser un poco más elegante, como en la fórmula original de Excel. Estos son datos de muestra, que hacen funcionar la función de arriba:

?GetLookupDataTriple(“Table1″,”To”,array(“From”,”Bulgaria”,”Cost”,200,”Currency”,”USD”))

Tags:  , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Sí, podemos traducir la fórmula de Excel {=INDEX(rango1,COINCIDIR(1,(A1=rango2)*(B1=rango3)*(C1=rango4),0))} a VBA. Una forma de hacerlo es usando una fórmula de matriz que aproveche el hecho de que VBA puede manejar matrices directamente. Podemos usar los métodos WorksheetFunction.Index y WorksheetFunction.Match en VBA para replicar la lógica de la fórmula de Excel, de esta forma:

    Dim rango1 As Range, rango2 As Range, rango3 As Range, rango4 As Range
Dim valorBuscadoA As Variant, valorBuscadoB As Variant, valorBuscadoC As Variant

Set rango1 = Hoja1.Range("A1:A10")
Set rango2 = Hoja1.Range("B1:B10")
Set rango3 = Hoja1.Range("C1:C10")
Set rango4 = Hoja1.Range("D1:D10")
valorBuscadoA = "manzana"
valorBuscadoB = "plátano"
valorBuscadoC = "naranja"

Dim resultado As Variant
Dim i As Long, j As Long
Dim matrizBusqueda As Variant, matrizCoincidencia As Variant

'crear una matriz con los valores buscados
matrizBusqueda = Array(valorBuscadoA, valorBuscadoB, valorBuscadoC)

'crear una matriz con las columnas coincidentes
matrizCoincidencia = Array(rango2.Value, rango3.Value, rango4.Value)

'redimensionar las matrices al número de filas del rango de búsqueda
ReDim Preserve matrizBusqueda(1 To rango1.Rows.Count)
ReDim Preserve matrizCoincidencia(0 To 2, 1 To rango1.Rows.Count)

'llenar la segunda dimensión de la matriz de coincidencia con el valor buscado
For i = 1 To rango1.Rows.Count
  For j = 0 To 2
    matrizCoincidencia(j, i) = matrizBusqueda(j)
  Next j
Next i

'comparar la matriz de coincidencia con los rangos de datos
Dim columnaCoincidencia As Variant, indiceCoincidencia As Long
columnaCoincidencia = Evaluate("INDEX((" & _
  "A1:A10=""" & valorBuscadoA & """)*" & _
  "(B1:B10=""" & valorBuscadoB & """)*" & _
  "(C1:C10=""" & valorBuscadoC & """)*" & _
  "FILA(B1:B10)),)")

'obtener el índice de la primera coincidencia
indiceCoincidencia = Application.WorksheetFunction.Match(1, columnaCoincidencia, 0)

'obtener el resultado usando el índice y el rango1
resultado = Application.WorksheetFunction.Index(rango1.Value, indiceCoincidencia, 1)

Debug.Print resultado

    En este código, primero definimos los valores de búsqueda (A1, B1 y C1) y los rangos de datos (rango1, rango2, rango3 y rango4). Luego creamos dos matrices, una para los valores de búsqueda y otra para los rangos de datos con los que queremos hacer coincidir. Redimensionamos estas matrices al número de filas del rango de búsqueda y llenamos la segunda dimensión de la matriz de coincidencia con los valores de búsqueda.

    Luego usamos el método Evaluate para comparar la matriz de coincidencia con los rangos de datos utilizando una lógica similar a la fórmula de Excel. La función INDEX devuelve una matriz de números de fila donde se cumplen todas las condiciones, y usamos la función MATCH para encontrar el índice de la primera coincidencia. Por último, usamos nuevamente la función INDEX para devolver el resultado usando el índice y el rango1.

    Este código debería producir el mismo resultado que la fórmula de Excel, y se puede modificar fácilmente para manejar diferentes rangos y valores de búsqueda.

Comments are closed.