¿Como hacer para que la macro de las respuestas formuladas en vez de solo el valor?
Tengo una macro que saca promedios de a 3 filas de una base de datos.
El problema es que el resultado no esta formulado, requiero que las respuestas queden formuladas.
La macro va así:
Sub Promedios()
WS_Count = ActiveWorkbook.Worksheets.Count
For hoja = 2 To WS_Count
Sheets(hoja).Select
t = 1
ult = Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To ult - 2 Step 3
poner = Range("A" & Rows.Count).End(xlUp).Row
Cells(poner + 1, 1) = t
j = 2
While Cells(2, j) <> ""
Prom = Application.Average(Cells(i, j), Cells(i + 1, j), Cells(i + 2, j))
Cells(poner + 1, j) = Prom
j = j + 1
Wend
t = t + 1
Next
Next
End Sub
1 Respuesta
En lugar de tu línea: Cells(poner + 1, j) = Promt
Debes colocar alguna de estas 2 dependiendo de lo que necesites:
Cells(poner + 1, j).Formula = "=AVERAGE(" & Cells(i, j) & "," & Cells(i + 1, j) & "," & Cells(i + 2, j) & ")"
Cells(poner + 1, j).FormulaR1C1 = "=AVERAGE(R[-7]C:R[-5]C)"
La primera te dará como resultado una fórmula del tipo:
=PROMEDIO(20;50;85) es decir con los valores que tengan las celdas
La segunda te colocará las referencias:
=PROMEDIO(B8:B10)
En este caso debes hacer el cálculo de cuántas filas por encima se encuentra el dato, en mi ejemplo fueron 7 pero esto será la diferencia entre la fila donde colocas la fórmula y el rango de valores. Si no lo podés completar deja una imagen como para guiarme en la ubicación de tus datos.
La macro lo que hace es tomar las filas 5,6 y 7 para sacar el promedio de estas en la columna B, luego toma las filas 8,9 y 10 de las misma columna y saca el promedio. Esto se repite para todas las columnas, hasta llegar a las ultimas filas. No se repite fila dentro de una columna, por eso si ya se saco el promedio de las filas 8,9 y 10, el siguiente promedio seria de la fila 11 a la 13.
Al implementar lo que me dijiste efectivamente el segundo código muestra los restados formulados (con su referencia) y eso es lo que yo quiero, pero al remplazar el código que me indicaste por el que me diste, el promedio se corre una fila y no 3. Lo que quiero decir es que si saca el promedio de las filas 5,6 y 7 el segundo promedio lo inicia el la fila 6 a la 8, cuando debería de iniciar en la fila 8 ya que la 6 y 7 ya han sido promediadas.
Necesitabas saber cómo se deja formulado y eso es lo que te envié.
Pero además te dejé este comentario:
En este caso debes hacer el cálculo de cuántas filas por encima se encuentra el dato, en mi ejemplo fueron 7 pero esto será la diferencia entre la fila donde colocas la fórmula y el rango de valores. Si no lo podés completar deja una imagen como para guiarme en la ubicación de tus datos.
las filas que se toman son desde la fila 5 hasta la fila 238 (teniendo en cuenta que en un futuro pueden crearse nuevas filas al actualizarse los datos) y de la columna B a la AY
Te dejo 2 opciones:
1 - dejar fórmula como referencia absoluta, del tipo: =PROMEDIO(B$5:B$7)
Cells(poner + 1, j).FormulaR1C1 = "=AVERAGE(R" & i & "C:R" & i + 2 & "C)"
2- fórmula con referencias relativas, del tipo: =PROMEDIO(B5:B7)
f1 = poner + 1 - i : f2 = f1 - 2
Cells(poner + 1, j).FormulaR1C1 = "=AVERAGE(R[-" & f1 & "]C:R[-" & f2 & "]C)"
