VBA - guardar columna para usar en una formula

[Seskin]

Buenas,

Tengo el siguiente problema, quiero guardar parte de una formula en diferentes variables, por si en un futuro el dato cambiase de columna, no tener que cambiar dicho dato en la sucesivas fórmulas, el problema es que no consigo que funcione, tengo lo siguiente:

Código vb:

Sub formulaVba()
    '
    Dim datoMes As Variant
    Dim columnaRamo As Variant
    Dim intervencionP1 As Variant
    '
    datoMes = Worksheets("DATOS_TABLA").Range("AH:AH")
    columnaRamo = Worksheets("DATOS_TABLA").Range("AT:AT")
    intervencionP1 = Worksheets("DATOS_TABLA").Range("AX:AX")
    'intervencionP1 = "DATOS_TABLA!AH:AH"
    '
    Dim filtroMes As String
    Dim filtroRamoAtencion As String
    '
    filtroRamoAtencion = "=SI(ESBLANCO(filtroRamo);PROMEDIO.SI.CONJUNTO(intervencionP1;DATOS_TABLA!AH:AH;filtroMes);PROMEDIO.SI.CONJUNTO(intervencionP1;DATOS_TABLA!AH:AH;filtroMes;DATOS_TABLA!AT:AT;filtroRamo))"
    filtroMes = Worksheets("CONSULTAS_TABLA").Range("filtroMes")
    '
    If filtroMes = "" Then
    MsgBox "Es necesario indicar en el campo Mes el criterio de selección", vbInformation, Application.Name
    Else
    Worksheets("CONSULTAS_TABLA").Range("F20").FormulaLocal = filtroRamoAtencion
    End If
    
    '
End Sub
 

Cuando voy a la celda para comprobar el dato aparece el error #¿NOMBRE? y cuando comprueba la fórmula que se presenta en la celda veo que no está pasando la columna sino el nombre de la variable:

=SI(ESBLANCO(filtroRamo);PROMEDIO.SI.CONJUNTO(intervencionP1;DATOS_TABLA!AH:AH;filtroMes);PROMEDIO.SI.CONJUNTO (intervencionP1;DATOS_TABLA!AH:AH;filtroMes;DATOS_TABLA!AT:AT;fil troRamo))

Si cambio intervencionP1 por la dirección de la columna, es decir, sin usar variable la fórmula funciona.

¿Alguna sugerencia?

[Triby]

Necesitas concatenar:

Código vb:

filtroRamoAtencion = "=SI(ESBLANCO(filtroRamo);PROMEDIO.SI.CONJUNTO(" & intervencionP1 & ";Aquí más texto...."
 

[Seskin]

Hola Triby, gracias por contestar,

Se me olvidó comentar que si concateno me da error 1004 de ejecución, por lo que tampoco puedo, ¿alguna sugerencia?

Gracias!

[Triby]

Qué parámetro espera la función CONJUNTO?

Algo me dice que debería ser una cadena que le indice un rango de celdas y no el rango como tal. Prueba primero a hacer la fórmula sin variables y, cuando funcione, entonces haces los reemplazos necesarios.

[Seskin]

La fórmula funciona perfectamente sin variables, es cuando los incluyo cuando deja de funcionar.

[jchuk]

Hola,

Pienso que va por donde dice Triby,

En la variable intervencionP1, ya que es variant, estás almacenando toda la tabla, es decir todas las filas y columnas con sus valores, ya que en la sentencia de asignación no indicas propiedad y por tanto VBA asume Value.

En la fórmula donde concatenas la variable lo que se espera es el rango, no los valores.

Pienso que si añades .Address a la asignación lo arreglarías:

intervencionP1 = Worksheets("DATOS_TABLA").Range("AX:AX").Address

Saludos