Encontrar valores de las ultimnas celdas

[kab]

Hola.
Quisiera saber si alguien me puede ayudar con una funcion o codigo que busque los ultimos 5 valores de una columna, pero no todas las filas de esas columnas tienen valores entonces no se como encontrar esas celdas que si tienen valores y pegarlos en otra hoja. Ademas la hoja va a cambiar contiunuamente por lo q necesito que se actualice cada vez que los valores finales cambian.

Gracias

[3pies]

No existen fórmulas que te hagn eso...

Con macros es bastante sencillo conseguirlo. ¿Sabes algo sobre macros?.

[kab]

Un poco, lo basico. Lo que no se es como hacer para que actualice solo si el valor final cambia, y sin que exista un boton o algo asi.

[kab]

En realidad no necesito pegar esos valores en otra celda, sino sumarlos en una celda y en otra celda necesito tomar cada uno de estos ultimos 5 valores y restarle el valor de otra celda.

Gracias

[3pies]

Supongamos que tienes dos hojas: Hoja1 y Hoja2. Imagina que quieres evaluar cambios en la Hoja1, pero solo si afectan a la 1ª columna (columna A). Si se produce un cambio en esa columna, cogeremos los cinco últimos datos de la misma (si hay líneas vacías lo tendremos en cuenta).

Para ello, copia esto en la Hoja1, no en un módulo (desde VBA):

Código:

Sub Worksheet_Change(ByVal Target As Range)
'si hay errores, que continúe
On Error Resume Next
'Ocultamos el procedimiento
'Application.ScreenUpdating = False
'si cambiamos algo de la columna A...
If ActiveSheet.Name = "Hoja1" And Target.Column = 1 Then
    'copiamos los 5 valores del final a otra hoja
    Range("A1").Select
    'bajamos hasta el final de la columna
    ActiveCell.SpecialCells(xlLastCell).Select
    'recogemos el datos de las 5 últimas filas
    If ActiveCell <> "" Then
        'si la celda contiene datos, lo pasamos a una variable
        dato1 = ActiveCell
    Else
        'si no hay datos, buscamos la celda con el último dato
        Do While IsEmpty(ActiveCell)
            ActiveCell.Offset(-1, 0).Select
        Loop
        dato1 = ActiveCell
    End If
 
    If ActiveCell.Offset(-1, 0) <> "" Then
        dato2 = ActiveCell.Offset(-1, 0)
    Else
        Do While IsEmpty(ActiveCell.Offset(-1, 0))
            ActiveCell.Offset(-1, 0).Select
        Loop
        dato2 = ActiveCell.Offset(-1, 0)
    End If
 
    If ActiveCell.Offset(-2, 0) <> "" Then
        dato3 = ActiveCell.Offset(-2, 0)
    Else
        Do While IsEmpty(ActiveCell.Offset(-2, 0))
            ActiveCell.Offset(-1, 0).Select
        Loop
        dato3 = ActiveCell.Offset(-2, 0)
    End If
 
    If ActiveCell.Offset(-3, 0) <> "" Then
        dato4 = ActiveCell.Offset(-3, 0)
    Else
        Do While IsEmpty(ActiveCell.Offset(-3, 0))
            ActiveCell.Offset(-1, 0).Select
        Loop
        dato4 = ActiveCell.Offset(-3, 0)
    End If
 
    If ActiveCell.Offset(-4, 0) <> "" Then
        dato5 = ActiveCell.Offset(-4, 0)
    Else
        Do While IsEmpty(ActiveCell.Offset(-4, 0))
            ActiveCell.Offset(-1, 0).Select
        Loop
        dato5 = ActiveCell.Offset(-4, 0)
    End If
    'llamamos al macro pegardatos
    pegardatos
End If
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub

Ahora en un módulo, copia esto:

Código:

Public dato1 As Variant
Public dato2 As Variant
Public dato3 As Variant
Public dato4 As Variant
Public dato5 As Variant
 
Sub pegardatos()
'vamos a la hoja2
Hoja2.Select
'pegamos los datos
Range("A1") = dato5
Range("A2") = dato4
Range("A3") = dato3
Range("A4") = dato2
Range("A5") = dato1
'volvemos a la hoja1
Hoja1.Select
End Sub

Saludos.

Edito, para colgar el ejemplo: http://www.2shared.com/document/RecDDwxN/Ejemplo.html

[kab]

No me esta sirviendo, lo que hace es irse a la ultima columna donde tengo datos escritos y despues se queda pegado.
Espero me puedas ayudar con esto.

[kab]

No se si debe a que en otra fila pero muy abajo (fila 3000), tengo otras celdas con datos. Probé eliminando esas celdas pero aun asi no me esta funcionando. Si lo hago en un documento nuevo con Hoja1 y Hoja 2 si me sirve, de lo contrario no, a pesar de que cambié los nombres de las Hojas en el codigo.
Saludos

[3pies]

Prueba a cambiar todo aquello donde pone:

Hoja1.select y Hoja2.select, por esto:

Sheets("Hoja1").Select, o Sheets("Hoja2").Select, o Sheets("Previsión de ventas").Select si es que tu hoja se llama así.

[kab]

Si, eso lo cambie y aun asi no servia. No se como pero ahora si me esta sirviendo a la perfeccion, fui muy acertada tu respuesta. Ahora lo que no se es como decirle que tome cada uno de estos valores y le reste el numero que tengo en una celda y en otra celda que sume todos. Lo que no se es como escribir las formulas en macros.

[kab]

Hola 3 pies.

Esto es lo que tengo para calcular esos 2 valores que necesito.

Sub pegardatos()
'vamos a la hoja2
Sheets("Lista de productos").Select

'Aplicamos la formula de la demanda diaria promedio
Range("J3").Formula = (dato6 + dato5 + dato4 + dato3 + dato2 + dato1) / (6 * 15)

'Aplicamos la formula de la desviacion
Range("K3").Formula = SQRT((dato6 - Cells(3, 10).Value) ^ 2 + (dato5 - Cells(3, 10).Value) ^ 2 + (dato4 - Cells(3, 10).Value) ^ 2 + (dato3 - Cells(3, 10).Value) ^ 2 + (dato2 - Cells(3, 10).Value) ^ 2 + (dato1 - Cells(3, 10).Value) ^ 2)



La formula de la demanda diaria promedio si me sirve, pero la de la desviacion no, ya probe cambiando varias cosas pero aun asi no me sirve. Quisiera saber que estoy haciendo mal.
Mil gracias

[3pies]

Prueba con esta fórmula:

Código:

Range("K3") = "=SQRT((" & dato6 & "-J3) ^ 2 + (" & dato5 & "-J3) ^ 2 + (" & dato4 & "-J3) ^ 2 + (" & dato3 & "-J3) ^ 2 + (" & dato2 & "-J3) ^ 2 + (" & dato1 & "-J3) ^ 2)"

Saludos

[kab]

Me sirvio a la prefeccion, mil gracias
Ahora tengo otro problema. Resulta que necesito hacer todo el sub anterior pero para otro producto (otra columna) porque son muchos.
Te escribo el codigo, es el mismo que me enviaste pero le agregue un ElseIf (lo marque de color rojo) para seleccionar otra columna pero lo q pasa es que se me queda pegado:(



Sub Worksheet_Change(ByVal Target As Range)
'si hay errores, que continúe
On Error Resume Next
'Ocultamos el procedimiento
'Application.ScreenUpdating = False
'si cambiamos algo de la columna E...

'BOLSA PARA AUTOCLAVE 12X15"
If ActiveSheet.Name = "Inventario" And Target.Column = 5 Then
'copiamos los 5 valores del final a otra hoja
Range("E1").Select
'bajamos hasta el final de la columna
UltimaFila = Columns("E:E").Range("A2990").End(xlUp).Row
Cells(UltimaFila, 5).Select
ActiveCell = UltimaFila.Select
'recogemos los datos de las 5 últimas filas
If ActiveCell <> "" Then
'si la celda contiene datos, lo pasamos a una variable
datoA1 = ActiveCell
Else
'si no hay datos, buscamos la celda con el último dato
Do While IsEmpty(ActiveCell)
ActiveCell.Offset(-1, 0).Select
Loop
datoA1 = ActiveCell
End If

If ActiveCell.Offset(-1, 0) <> "" Then
datoA2 = ActiveCell.Offset(-1, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-1, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoA2 = ActiveCell.Offset(-1, 0)
End If

If ActiveCell.Offset(-2, 0) <> "" Then
datoA3 = ActiveCell.Offset(-2, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-2, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoA3 = ActiveCell.Offset(-2, 0)
End If

If ActiveCell.Offset(-3, 0) <> "" Then
datoA4 = ActiveCell.Offset(-3, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-3, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoA4 = ActiveCell.Offset(-3, 0)
End If

If ActiveCell.Offset(-4, 0) <> "" Then
datoA5 = ActiveCell.Offset(-4, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-4, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoA5 = ActiveCell.Offset(-4, 0)
End If

If ActiveCell.Offset(-5, 0) <> "" Then
datoA6 = ActiveCell.Offset(-5, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-5, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoA6 = ActiveCell.Offset(-5, 0)
End If

'llamamos al macro pegardatos
pegardatosA

'BOLSA PARA AUTOCLAVE 3.5*8
ElseIf ActiveSheet.Name = "Inventario" And Target.Column = 16 Then 'copiamos los 5 valores del final a otra hoja
Range("P1").Select
'bajamos hasta el final de la columna
UltimaFila = Columns("P:P").Range("P2990").End(xlUp).Row
Cells(UltimaFila, 16).Select
ActiveCell = UltimaFila.Select
'recogemos los datos de las 5 últimas filas
If ActiveCell <> "" Then
'si la celda contiene datos, lo pasamos a una variable
datoB1 = ActiveCell
Else
'si no hay datos, buscamos la celda con el último dato
Do While IsEmpty(ActiveCell)
ActiveCell.Offset(-1, 0).Select
Loop
datoB1 = ActiveCell
End If

If ActiveCell.Offset(-1, 0) <> "" Then
datoB2 = ActiveCell.Offset(-1, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-1, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoB2 = ActiveCell.Offset(-1, 0)
End If

If ActiveCell.Offset(-2, 0) <> "" Then
datoB3 = ActiveCell.Offset(-2, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-2, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoB3 = ActiveCell.Offset(-2, 0)
End If

If ActiveCell.Offset(-3, 0) <> "" Then
datoB4 = ActiveCell.Offset(-3, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-3, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoB4 = ActiveCell.Offset(-3, 0)
End If

If ActiveCell.Offset(-4, 0) <> "" Then
datoB5 = ActiveCell.Offset(-4, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-4, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoB5 = ActiveCell.Offset(-4, 0)
End If

If ActiveCell.Offset(-5, 0) <> "" Then
datoB6 = ActiveCell.Offset(-5, 0)
Else
Do While IsEmpty(ActiveCell.Offset(-5, 0))
ActiveCell.Offset(-1, 0).Select
Loop
datoB6 = ActiveCell.Offset(-5, 0)
End If

'llamamos al macro pegardatos
pegardatosB
End If
'Mostramos el procedimiento
Application.ScreenUpdating = True
End Sub



SALUDOS

[3pies]

¿Y por qué no creas un macro para cada columna?. Así será más legible el código.

Luego desde un tercer macro, puedes llamar a los dos anteriores, de esta forma:

Código:

Sub Macro_final()
'lanzamos los macros
Macro1
Macro2
End Sub

[kab]

Disculpa mi ignorancia pero no se como hacer eso.
En cada modulo pongo el codigo de cada producto y hago un modulo final para llamar a cada modulo de producto? Es decir quito el codigo que tengo en la Hoja2 (inventario)

[3pies]

Nada, olvídalo, cría que te referías a hojas distintas y no a columnas distintas.

Con el elseif, ya lo estabas haciendo bien. Para ver donde tienes errores, y demás, fíjate en el código que controla lo que ocurre en la primera columna (el código que te puse yo). Compáralo con el tuyo, algo tiene que haber que no esté correcto. Tienes que ser tú quien le eche un vistazo, que yo ya te he dado la idea de como arrancar

[kab]

Ok, muchisimas gracias por tu gran ayuda, me ha servido mucho.
Saludos.