buscar máximos en una macro mediante "cells.find" excel 2007

andres265 · #1 (**permalink**) 28/10/2010, 09:24

Hola a todos (as),

Estoy elaborando una macro (aclaro que no soy muy conocedor del tema) que busca los cinco valores máximos (en formato moneda) de una lista, copia ese valor y las dos celdas a la izquierda del mismo y los pega en otra hoja del mismo libro.

La búsqueda de de los máximos la hago mediante la función "LARGE" así ya tengo un valor específico a buscar y poder copiar la información adicional que necesito. Como tengo que repetir este proceso 5 veces entonces desarrollé un pequeño procedimiento al que le paso, cada vez, el máximo correspondiente (valorMáximo1, valorMáximo2, etc.), la hoja donde debe buscarlo y la hoja donde debe pegarlo. Sin embargo tengo dos problemas:

1. Los valores vienen en formato moneda, el máximo correspondiente lo identifica bien pero cuando voy a buscar el valor con "cells.find" no lo encuentra. Para resolver esto decidí cambiar el formato de los valores a "General" al iniciar la macro, con lo cual funciona bien. ¿Hay alguna forma de hacer la búsqueda independiente del formato?. Intenté con "SearchFormat = False" pero si dejo el formato en "moneda" (o cualquier otro) no encuentra el dato.

2. La invocación del procedimiento "pegarValores" funciona bien para los 4 primeros máximos, pero al buscar el quinto no lo encuentra (aunque sí existe y lo identifica) y no tengo la más mínima idea de por qué.

Este es el código que estoy empleando:

Código:

'Si no se asigna el formato "General" a los datos no encuentra nada
    Selection.NumberFormat = "General"
    Set rangoValores = Selection
    
    valorMaximo1 = Application.WorksheetFunction.Large(rangoValores, 1)
    Call pegarValores(valorMaximo1, "5+ 5-", "B3")
    
    Sheets("Facturacion").Select
    Range("A1").Select
    valorMaximo2 = Application.WorksheetFunction.Large(rangoValores, 2)
    Call pegarValores(valorMaximo2, "5+ 5-", "B4")
    
    Sheets("Facturacion").Select
    Range("A1").Select
    valorMaximo3 = Application.WorksheetFunction.Large(rangoValores, 3)
    Call pegarValores(valorMaximo3, "5+ 5-", "B5")
    
    Sheets("Facturacion").Select
    Range("A1").Select
    valorMaximo4 = Application.WorksheetFunction.Large(rangoValores, 4)
    Call pegarValores(valorMaximo4, "5+ 5-", "B6")
    
'Hasta aquí funciona bien. En esta última parte no encuentra el valor
    Sheets("Facturacion").Select
    Range("A1").Select
    valorMaximo5 = Application.WorksheetFunction.Large(rangoValores, 5)
    Call pegarValores(valorMaximo5, "5+ 5-", "B7")
    
End Sub

Private Sub pegarValores(valorBuscado As Variant, hojaPegar As String, celdaInicial As String)

    Cells.Find(What:=valorBuscado, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select
    Range(Selection, Selection.Offset(0, -2)).Copy
    Sheets(hojaPegar).Select
    Range(celdaInicial).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
        Transpose:=False
    Application.CutCopyMode = False

End Sub

De antemano mil gracias por su ayuda,

Andrés

mrocf · #2 (**permalink**) 29/10/2010, 20:25

Hola! Andrés.
Tu código no parece prever que existan números repetidos entre los 5 mayores, ¿Es así ó faltó considerar esa cuestión?

Otra cosa: ¿Por qué no los ordenas de mayor a menor?

Y finalmente, la mejor opción: ¿Por qué no los filtras y te quedas con los 5 mayores?...
Saludos, Cacho.

mrocf · #3 (**permalink**) 29/10/2010, 20:48

Supondré que tus datos se encuentran en las columnas [A:C], siendo la fila "1" la de encabezados.
Además voy a suponer que debes pegar tus datos en la hoja "Hoja2" a partir de la celda A1.

En tal caso podrías intentar lo siguiente:

Código vb:

Ver originalSub Macro816()
'-----------------------------
' By Cacho Rodríguez
'-----------------------------
ActiveSheet.AutoFilterMode = False
With Range([a2], [c65536].End(xlUp))
  Union([a1:c1], .Cells).AutoFilter Field:=3, Criteria1:="5", Operator:=xlTop10Items
  .SpecialCells(xlCellTypeVisible).Copy Worksheets("Hoja2").[A1]
End With
ActiveSheet.AutoFilterMode = False
End Sub

Como verás: una sola selección y un solo "pegado".

Comenta tus impresiones, por favor.
Saludos, Cacho.

andres265 · #4 (**permalink**) 04/11/2010, 08:55

¡Hola Cacho!

Pensé que cuando uno recibía alguna respuesta del foro te enviaban una notificación al correo, pero no me llegó nada... hoy entré por pura casualidad (soy el mismo que te envió un correo preguntándote por la eliminación de filas vacías).

1. No hace falta validar si los valores están repetidos o no ya que cada valor hace referencia a un servicio diferente.
2. En tu propuesta, supongo que también existe un "xlBottom10Items"? Ya que necesito identificar los 5 valores mayores y menores.
3. Necesito pegar estos valores (5+ y 5-) en otro libro, la modificación sería algo así como "Workbooks("Libro2").Worksheets("Hoja2").[A1]?
4. Había intentado hacer el filtro pero entonces para los 5 menores sacaba los que tuvieran valor "0" y esos no me interesan.

Hice la prueba con el código que me enviaste y me sale el error: "Run time error 1004: Application defined or object defined error"

Nuevamente mil gracias por tu ayuda,

Andrés

PS: ¿conoces algún libro que puedas recomendarme para aprender BIEN de macros? las que he hecho hasta ahora, como puedes ver, son bastante "rudimentarias" y veo que tus soluciones son muy elegantes y limpias.

mrocf · #5 (**permalink**) 04/11/2010, 10:37

Por favor, Andrés: muestra el código que has utilizado e indica -claramente- cual es la línea que da ese error que mencionas.

Saludos, Cacho.

andres265 · #6 (**permalink**) 04/11/2010, 10:43

Hola Cacho,

Usé exactamente el mismo código que enviaste, sin modificar. El error sale en la línea:

Código:

Union([a1:c1], .Cells).AutoFilter Field:=3, Criteria1:="5", Operator:=xlTop10Items

Gracias,

Andrés

mrocf · #7 (**permalink**) 04/11/2010, 12:03

Mmmmmm...
A mí me funciona perfectamente por lo que se hace difícil "adivinar" cual es tu problema sin ver el libro.

¿Podrías subirlo a algún servidor público (estilo MediaFire ú otro) para ver tu implementación?

Saludos, Cacho.

andres265 · #8 (**permalink**) 04/11/2010, 14:06

Hola Cacho,

Por esos caprichos de Windows tuve que reiniciar mi computador y cuando fui a probar nuevamente ya funcionó sin problemas (con los ajustes necesarios para el "formato" de la tabla de datos que voy a manejar).

Ahora tengo otra duda. Inicialmente la idea era pegar esos valores en otra hoja del mismo libro, pero ahora hubo un cambio y necesito hacerlo en otro libro. Intenté modificar el código así:

Código:

Sub BuscarTop5()
    
    Windows("Archivo Datos.xlsx").Activate
    Sheets("Sheet1").Select
    ActiveSheet.AutoFilterMode = False
    With Range([b6], [d65536].End(xlUp))
        Union([b5:d5], .Cells).AutoFilter Field:=3, Criteria1:="5", Operator:=xlTop10Items
        .SpecialCells(xlCellTypeVisible).Copy Windows("Informacion Presentacion.xlsm").Worksheets("Sheet1").[A1]
    End With
    ActiveSheet.AutoFilterMode = False

End Sub

Pero me dice que

Cita:

Runtime error 438: Object doesn't support this property o method.

¿Cuál sería la forma adecuada de pegar los valores en el otro libro? Bueno, me refiero a una forma corta de hacerlo en lugar de

Código:

Windows(archivoReporte).Activate
    Sheets("Top5").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
        Transpose:=False

Una vez más, muchas gracias por tu valiosa ayuda.

Saludos,

Andrés

JoaoM · #9 (**permalink**) 05/11/2010, 10:33

Cita:

Iniciado por andres265

¡Hola Cacho!

Pensé que cuando uno recibía alguna respuesta del foro te enviaban una notificación al correo, pero no me llegó nada...

Al escribires un mensage, abajo existe una casilla "Tipo de seguimiento" para que seleciones como quieres recibir las respuestas, selecionas segun tu criterio y ya

mrocf · #10 (**permalink**) 05/11/2010, 11:30

Hola! Andrés
¿Cuál es el código "verdadero" que estás utilizando?...
Te lo pregunto pues:

.SpecialCells(xlCellTypeVisible).Copy Windows("Informacion Presentacion.xlsm").Worksheets("Sheet1").[A1]

refiere a la celda A1 de la hoja "Sheet1" del libro "Informacion Presentacion.xlsm", mientras que el código

Sheets("Top5").Select
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

refiere a la celda B3 de la hoja "Top5" de un libro no indicado.

Saludos, Cacho.

andres265 · #11 (**permalink**) 05/11/2010, 14:42

Hola Cacho, disculpa la confusión.

Mira, este es el código tal y como lo estoy implementando:

Código:

   Windows(archivoDatos).Activate
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    ActiveSheet.AutoFilterMode = False
    With Range([b6], [d65536].End(xlUp))
        Union([b5:d5], .Cells).AutoFilter Field:=3, Criteria1:="5", Operator:=xlTop10Items
        .SpecialCells(xlCellTypeVisible).Copy
    End With

    Windows(archivoReporte).Activate
    Sheets("Top 5").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, _
        Transpose:=False

Donde: “archivoDatos” es el archivo desde el cual estoy leyendo la información y “archivoReporte” es el archivo donde necesito pegar la información sacada del anterior.

¡Mil gracias!

mrocf · #12 (**permalink**) 07/11/2010, 21:51

Amigo Andrés.
Me parece que ya estamos aplicando más tiempo del necesario al tema que nos ocupa. Es decir:

El último código que has publicado no parece tener problema alguno.
De modo que si el mensaje

Runtime error 438: Object doesn't support this property o method.

tiene que ver con este código, se te ha olvidado indicar cual línea lo produce.
Te sugiero que subas a un servicio gratuito tu libro para bajarlo y verlo pues ya hemos entrado en la etapa de "adivinar" cual es tu problema, por lo que viéndolo será todo más expedito.

Te mando un gran saludo, Cacho.

aheld55 · #13 (**permalink**) 01/04/2011, 02:56

Hola, mi problema es algo parecido.
Lo que tengo que lograr es lo siguiente en macro de Excel 2007:
tengo una variable en una columna de un hoja que se llama var1
luego tengo que seleccionar otra hoja y seleccionar una columna completa y luego busco en esa columna. Hasta aqui todo bien, pero mi problema empieza si var1 no simplemnte no existe en la Columna que estoy buscando. El codigo que ocupo es el siguiente:

Var1= palabra buscada
Sheets("Daten").Select

Columns("C:C").Select
While var1 <>var2
Selection.Find(What:=var1, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
var2=ActiveCell.FormulaR1C1
Wend

si la variable esta en la columna, funciona 100%, pero si no esta se cae en esta orden.
intente hacer un
If Selection.Find.found = False Then
exit while
end if

pero tampoco me dio resultado, espero se haya entendido mi problema.

Tratare de representarlo

Tabla 1
A B C
1 Casa Pedro
2 Casa Manuel
3 Departamento Juan
4 Departamento Francisco
5 Departamento Paula
6 Casa Marcela
7 Departamento Cesar

Tabla 2
1 A B C
2 Casa Pedro azul
3 Casa Manuel Verde
4 Departamento Juan Amarillo
5 Departamento Manuel Rojo
6 Departamento Pedro Lila
7 Casa Marcela Rosado
8 Casa Juan Verde 2
9 Casa Alberto Azul oscuro
10 Departamento Claudio rojo claro
11 Departamento Daniel mate
12 Departamento Francisca turquesa
13 Casa Matias gris
14 Casa Guillermo cafe
15 Casa Francisca blano
16 Departamento Daniel morado

Sorry trate de incertar la tabla o una imagen de esta, pero no lo logre, espero se entinda

Lo que necesito es seleccionar el nombre y si vive en casa o en Deapartamente de la tabla 1 eso lo guardo en dos variables, de las cuales el nombre es var1
Luego me voy a la tabla 2 y selcciono la columna B y busco, en cuanto encuentro el nombre verifico si tambien vive en Departamento o en Casa, si eso coincide entonces copio el colo de la columna C de la tabla 2 a la columna C de la Tabala 1.
Hasta aqui, todo va excelente y funciona perfecto, pero cuand el nombre que esta en la Tabla 1 simplemente no existe en la tabla 2 se cae.
Para esto tengo dos posibilidades que me gustaria lograr cualquier de las dos.
Posibilidad 1:
Si busco a Cesar y no esta entonces que pare de hacer todo y me diga, El nombre cesar no se encuentra en la base de datos. desea seguir sin ingresar este nombre? SI que siga, NO que pare
Posibilidad 2:
Si busco a Cesar y no esta entonces que pare de hacer todo y me diga, El nombre cesar no se encuentra en la base de datos. Desea ingresear el nombre y los demas datos a la base de datos ahora? y que se habra un formulario para ello, y que simplemente me la agregue al final. (Esta opcion es la que mas me gustaria)

Espero se haya entendido,
De antemano muchas gracias

Monimo · #14 (**permalink**) 01/04/2011, 11:41

Quizá solo debas poner un:

On error Resume Next.

Otra cosa te recomiendo que abras un tema nuevo pues es mas fácil que las personas te contesten ya que al estar tu pregunta dentro de otro tema, pueden pensar que ya está resuelto.

mrocf · #15 (**permalink**) 01/04/2011, 11:43

Hola! aheld55. Para una estructura de datos como la mostrada, fíjate en lo siguiente:

Código vb:

Ver originalSub Macro993()
Dim Rng As Range
Set Rng = Sheets("Daten").[a1].CurrentRegion.Columns(1)
With Range("c1:c" & [a1].End(xlDown).Row).Offset(, 4)
 
.Formula = "= SUMPRODUCT( (A1 = " & _
  Rng.Address(External:=True) & ")*(B1 = " & _
  Rng.Offset(, 1).Address(External:=True) & ") * ROW(" & _
  Rng.Offset(, 2).Address(External:=True) & ") )"
.Value = .Value
 
.Offset(, -4).Formula = "= IF(" & _
  .Cells(1).Address(False, False) & "=0, """", INDEX(" & _
  Rng.Offset(, 2).Address(External:=True) & ", " & _
  .Cells(1).Address(False, False) & ") )"
.Offset(, -4).Value = .Offset(, -4).Value
.EntireColumn.Delete
 
End With
 
Set Rng = Nothing
End Sub

¿Te puede ser de utilidad?
Saludos, Cacho.

JoaoM · #16 (**permalink**) 02/04/2011, 17:38

Cita:

Iniciado por andres265

Pensé que cuando uno recibía alguna respuesta del foro te enviaban una notificación al correo, pero no me llegó nada...

Si eres el creador del tema, al crearlo, antes de Enviar el tema debes seleccionar abajo; Notificacion instantanea por correo electronico donde dice Tipo de seguimiento.
Si entras a algun tema que no sea tu creacion, para recibir notificaciones sobre dicho tema, debes en Herramientas (en la barra arriba a la derecha del tema) seleccionar Seguir el tema

aheld55 · #17 (**permalink**) 03/04/2011, 11:20

Muchas gracias Cacho,

pero lastima que aun soy muy novato, y no entendi el codigo, serias tan amable de explicarmelo?

lo he hecho correr en excel pero no cacho que onda, no entendi que es lo que hace.

Saludos

mrocf · #18 (**permalink**) 03/04/2011, 12:40

Se te olvidó mencionar si el código te ha funcionado correctamente pues, de no ser así, entonces la estructura de tus datos no es la que has mostrado en este debate.
De todos modos te comento:

.) El código se ejecuta teniendo activa la hoja en la que se encuentra la que llamas "tabla1"

.) Mediante el

Código:

.Formula = "= SUMPRODUCT( (A1 = " & _
  Rng.Address(External:=True) & ")*(B1 = " & _
  Rng.Offset(, 1).Address(External:=True) & ") * ROW(" & _
  Rng.Offset(, 2).Address(External:=True) & ") )"

ponemos en la columna G (es la columna auxiliar que elegí utilizar) la fórmula:

= SUMAPRODUCTO( (A1 = Daten!$A$1:$A$15)*(B1 = Daten!$B$1:$B$15) * FILA(Daten!$C$1:$C$15) )

Esta fórmula nos dará: "0" si los datos de las columnas A y B no están en la que llamas "tabla2", mientras que te brindará el número de fila cuando los datos de las columnas A y B (de la tabla "tabla1") se encuentren en la "tabla2".

.) Por su parte, el

Código:

.Offset(, -4).Formula = "= IF(" & _
  .Cells(1).Address(False, False) & "=0, """", INDEX(" & _
  Rng.Offset(, 2).Address(External:=True) & ", " & _
  .Cells(1).Address(False, False) & ") )"

pone en la columna C la fórmula:

= SI(G1=0; ""; INDICE(Daten!$C$1:$C$15; G1) )

Esta fórmula nos dará: "(vacío)" si la fila correspondiente de la columna G contiene un "0", mientras que te brindará el dato buscado en caso contrario.

El resto de las líneas de código te deberían ser de más sencilla interpretación. Sin embargo, es importante que comprendas que la potencia y rapidez de este código lo brinda el Excel y no el VBA.

Saludos, Cacho.

aheld55 · #19 (**permalink**) 05/04/2011, 00:45

hahah no entiendo mucho esto de los foros, recien ahora me estoy empzando a meter.
Como es eso de que hay que cerrar? y como se agradece?? de hecho que estoy agradecido, pero existe alguna forma de dar puntaje o algo asi?.
NO creo que esten a disposisicon de todo el mundo, el mensaje extra gigante que te envie fue solo para que entiendas lo que estoy haciendo, nada mas. Solo necesito saber como decirle a VB que si en una columna no encuentra un dato, que me diga o que pare, pero no que se caiga. perdon si me entendiste mal.

lo que pasa es que si este codigo:

Selection.Find(What:=var1, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

no encuntra resultado, se cae, yo quiero queme diga "no existe el dato" o poder ingresar el nuevo dato o algo asi.
Eso es todo, muchas gracias por el momento, creo que todo esto se ha transformado en una gran confusion, sorry.

Saludos

JoaoM · #20 (**permalink**) 06/04/2011, 10:37

Cita:

Iniciado por aheld55

hahah no entiendo mucho esto de los foros, recien ahora me estoy empzando a meter.
Como es eso de que hay que cerrar? y como se agradece?.Saludos

Es logico que al estar el tema resuelto, se cierre el tema para no originar que surja nueva pregunta dentro del mismo tema que nada tenga que ver con la pregunta originaria o, alguna respuesta derspues del tema resuelto.
Se deveria cerrar tema despues de algunos mese de inatividad y la recomendación que se hace es que si la pregunta es tuya (creaste el tema) no es logico que venga yo a preguntar sobre otro tipo de codigo o otro codigo con otra finalidad, por eso se deve crear un tema para cada pregunta, interrugante o duda que se tenga dentro del objectivo de la pregunta.
El buscador pienso que está echo para que busque dentro del foro en general, y si dos o mas preguntas estan en un solo tema, es dificil encontrar determinado tema que queremos.

aheld55 · #21 (**permalink**) 07/04/2011, 08:01

a ok, no hay problema, gracias