Ordenar Excel VBA por fecha (sin textbox)
Odeatho
-
lermite222 Mensajes publicados 9042 Estado Colaborador -
lermite222 Mensajes publicados 9042 Estado Colaborador -
Hola a todos,
He recorrido bastantes foros que dan respuestas que me convienen casi, pero que no logro adaptar.
Tengo una hoja de cálculo de Excel con una columna llena de fechas y me gustaría simplemente hacer un orden según la fecha.
Leí que la solución probablemente pasaría por la conversión a modo estándar de la columna.
He encontrado un código que funciona casi:
Dim ColDates As Integer
Dim f1 As Worksheet
Set f1 = Worksheets("sheet")
ColDates = 40
'1.Columns(ColDates).NumberFormat = "0"
f1.Range("A1").CurrentRegion.Sort Key1:=Range("I250"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' ', _DataOption1:=xlSortNormal
'1.Columns(ColDates).NumberFormat = "dd/mm/yyyy"
Pero hay dos problemas que no logro resolver.
El primero es que para indicar el contenido de mis columnas, la primera celda es una cadena, así que cuando se ordena, la pone automáticamente al final, que es lo que no quiero. He intentado cambiarlo a número durante el periodo del ordenamiento, pero no tuve éxito.
Segundo problema, me parece entender que Excel tiene problemas entre fechas jj/mm/aaaa y mm/jj/aaaa, además cuando pongo la columna en formato estándar hay algunas celdas que no cambian (que no logro formatear como Fecha)
¿Tendrías ideas?
He recorrido bastantes foros que dan respuestas que me convienen casi, pero que no logro adaptar.
Tengo una hoja de cálculo de Excel con una columna llena de fechas y me gustaría simplemente hacer un orden según la fecha.
Leí que la solución probablemente pasaría por la conversión a modo estándar de la columna.
He encontrado un código que funciona casi:
Dim ColDates As Integer
Dim f1 As Worksheet
Set f1 = Worksheets("sheet")
ColDates = 40
'1.Columns(ColDates).NumberFormat = "0"
f1.Range("A1").CurrentRegion.Sort Key1:=Range("I250"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
' ', _DataOption1:=xlSortNormal
'1.Columns(ColDates).NumberFormat = "dd/mm/yyyy"
Pero hay dos problemas que no logro resolver.
El primero es que para indicar el contenido de mis columnas, la primera celda es una cadena, así que cuando se ordena, la pone automáticamente al final, que es lo que no quiero. He intentado cambiarlo a número durante el periodo del ordenamiento, pero no tuve éxito.
Segundo problema, me parece entender que Excel tiene problemas entre fechas jj/mm/aaaa y mm/jj/aaaa, además cuando pongo la columna en formato estándar hay algunas celdas que no cambian (que no logro formatear como Fecha)
¿Tendrías ideas?
11 respuestas
-
Hola,
Efectivamente no es posible cambiar una fecha ingresada como texto por un formato fecha.
La causa: Excel trata las fechas como números '1 = 1/1/1900
el 13/11/2008 = 39765
Lo que implica que en la celda no hay una fecha sino un número que Excel MUESTRA con el formato designado.
Pero en una celda de texto, no es un número lo que hay, sino una cadena (string).
y no hay problema con los formatos.
Para solucionarlo puedes hacer un bucle sobre tu columna para volver a convertir todo a fecha con..Worksheets("Feuil2").Range("A7") = CVDate(Worksheets("Feuil2").Range("A7"))
Ajusta las direcciones, por supuesto.
Cuando esté hecho, tu filtro funcionará normaal.
A+
--
La experiencia instruye con más seguridad que el consejo. (André Gide)
Si te golpeas un pote y suena hueco, no es necesariamente el pote el que está vacío. ;-) (Confucio)-
Gracias =) Pero no tengo un nivel muy bueno en VBA y me cuesta manejar los Range Ves en el trozo de código que cité en mi primer post, no entiendo por qué es Range(A1) luego Key(I250). Y intenté usar tu fórmula pero es igual, no consigo decirle que tome desde la celda 1 hasta la 250. Otra pregunta, ¿cómo se hace para poner la columna AN dentro de un rango? No he tenido éxito tampoco. Aquí está el código que puse pero el rango no es correcto. Dim A As Long For A = 2 To 250 Cells(40, A).Value = Cells(12, A).Value f1.Range(12, A) = CVDate(f1.Range(12, A)) Next A Seguramente son preguntas fáciles que hago, lo siento :)
-
-
Hola,
esperando a nuestro amigo Louis (¡hola, Lhermite, es la hora!) ;-)
tu variable A siendo un número designando una columna no puedes emplear con facilidad el objeto "range"
entonces debes usar cells(12,A)
si tu variable X designara una fila en la columna B podrías escribir range("B:" & X)
--
Cordialmente,
Michel-
Gracias Michel =)
Intenté hacer lo que me dijiste pero no funcionó, tengo la impresión de que soy yo quien no entiende lol
en realidad me gustaría recorrer mi columna L o AN a elegir de 1 a 250
probé esto (en un bucle de 1 a 250 con A como variable incrementada) :
f1.Range("AN:" & A) = CVDate(f1.Range("AN:" & A))
me dice "method range of object worsheet failed"
y esto :
f1.Cells("AN:" & A) = CVDate(f1.Cells("AN:" & A))
me dice " Type mismatch"
no entiendo qué es lo que falla
-
-
CVdate?
¿No sería mejor Cdate?
--
Atentamente,
Michel -
Hola, hola Michel, ya estoy despierto... :-)
No, es bien CVDate...
Y comencemos por el principio, cambia tu columna Textre a fecha...
1°) con Range primero pones la columna Y DESPUÉS la fila.. Tipo B5
Pero con Cells es al revés, Cells(Fila,Columna)
así que cuando pones...
Cells(40, A).Value = Cells(12, A).Value
Las direcciones no son correctas.
Ejecuta primero esta pequeña funciónSub ChangerT_D() Dim Lig As Long, Col As Integer Col = 2 ' Para la columna B .. a adaptar. Nota: para la columna AN es 40 For Lig = 2 To 250 'adaptar las filas Cells(Lig, Col) = CVDate(Cells(Lig, Col)) Next Lig End Sub
Tú dices..
Y veremos para la continuación
EDIT:
Evita usar variables abstractas como A, B, C etc, si trabajas con filas pon una variable que te haga pensar en filas, yo empleo Lig y Col para columnas.
Intenta también poner una o varias mayúsculas en el nombre de la variable pero cuando la retranscribas NO PONGAS mayúsculas, así podrás detectar rápidamente un error de sintaxis si la palabra que acabas de escribir no se transforma sola con una o más mayúsculas. (pequeños trucos)
--
La experiencia instruye más seguramente que el consejo. (André Gide)
Si te golpeas a un pote y suena hueco, no necesariamente es el pote el que está vacío. ;-)(Confucio) -
Voy a responder de todas maneras a tu segunda pregunta antes de tu respuesta,
Key1:=Range("I250"), me sorprendería que eso esté bien,
Key es la “palabra” a buscar, así que si por ejemplo lo que buscas está en una lista de nombres escribe en A1 ...Marcel
y entonces debería ser Key=range("A1"), buscará todas las líneas donde esté escrito el nombre Marcel.
--
La experiencia instruye más seguramente que el consejo. (André Gide)
Si te pegas a un pote y suena hueco, no es necesariamente que el pote esté vacío. ;-)(Confucio)-
Hola a todos,
Gracias, voy a probar todo esto ( desde ahora hasta el miércoles por la noche de la próxima semana y sí, ¡viva la alternancia! :p)
les mantendré informados.
Sí, entonces de hecho la función que escribí en mi primer post no ordena por fecha en absoluto, ¿verdad?
O tal vez está previsto que no haya nada en la casilla en cuestión, en cuyo caso el orden se haría de menor a mayor?
En fin, son solo suposiciones. Es raro, de todos modos, tomé esa función de un foro que daba funciones para ordenar por fecha.
¡Muchísimas gracias!
-
-
Hola,
Probablemente porque hay una mezcla de formato,
intenta poner toda la columna en Texto.. si no funciona prueba en Estándar..
Tú dices.
Hasta luego
EDIT:
Negativo, no funciona cambiando el formato.
--
La experiencia instruye más seguramente que el consejo. (André Gide)
Si te empeñas en un pote y suena hueco, no es necesariamente porque el pote esté vacío. ;-)(Confucio) -
intenta más bien con...
Sub ChangerT_D() Dim Lig As Long, Col As Integer Col = 2 ' Para la columna B .. a adaptar. Nota: para la columna AN es 40 On Error resume Next For Lig = 2 To 250 'adaptar las filas Cells(Lig, Col) = CVDate(Cells(Lig, Col)) Next Lig End Sub
dices.
--
La experiencia instruye más seguramente que el consejo. (André Gide)
Si te golpeas a una olla y suena hueca, no es necesariamente la olla la que está vacía. ;-)(Confucius)-
Gracias Lermitte22,
siento que avanza mucho, te lo agradezco (no me olvido de los demás, eh :) )
entonces digamos que no me lo ordena en orden ascendente ( pero eso no viene del formato, creo)
Voy a darte el código exacto de la macro
y también en las celdas donde no hay nada (ponía 250 para estar seguro de no pasarlo por alto, pero creo que solo hay unas 150 celdas llenas) hay 00/01/1900 pero creo que es porque considera que la celda vacía representa 0.
Solo quiero entender ahora por qué no se ordena en el orden correcto.
Sub TrierSelonLaDate()
Dim f1 As Worksheet
Set f1 = Worksheets("Actions GO")
Dim Lig As Long, Col As Integer, ColDep As Integer
Col = 40 ' Para la columna B .. a adaptar. Nota: para la columna AN es 40
ColDep = 12
On Error Resume Next
For Lig = 2 To 250 'adaptar las filas
Cells(Lig, Col).Value = Cells(Lig, ColDep).Value
Cells(Lig, Col) = CVDate(Cells(Lig, Col))
Next Lig
Cells(1, Col) = 1
Dim A As Long
For A = 2 To 250
Next A
Cells(40, 1).Value = "1000"
Dim ColDates As Integer
ColDates = 40
f1.Columns(ColDates).NumberFormat = "0"
f1.Range("A1").CurrentRegion.Sort Key1:=Range("I250"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
', _DataOption1:=xlSortNormal
f1.Columns(ColDates).NumberFormat = "dd/mm/yyyy"
End Sub
Ella probablemente esté mal escrita o no esté escrita como un código verdadero, lo siento de antemano.
Te agradezco a todos
-
-
Hola,
S, creo que viene del formato,
Primero lo pones en formato estándar o numérico (no recuerdo cuál) lo que, visto que Excel trata las fechas como números, traduce tus fechas en números y el ordenamiento se hace sobre números.
Luego vuelves a poner el formato de fecha
No lo ves porque va demasiado rápido.
Pero es una buena idea a recordar para ordenar por fecha.
A+
EDIT:
¿Qué quieres decir con “no ordena en el orden correcto”? si quieres cambiar el sentido basta con cambiar xlAscending por xlDescending
--
La experiencia instruye más firmemente que el consejo. (André Gide)
Si te golpeas con una olla y suena hueco, no siempre es que la olla esté vacía. ;-)(Confucius)-
Sí Lermitte22, era el principio de base: copiar la columna de fecha, transformarla al formato de fecha, luego en estándar hacer un orden de menor a mayor y después reformatearla como fecha.
cuando te digo que está en el orden incorrecto es algo así:
15/11/2008
15/10/2008
15/05/2009
31/10/2008
10/08/2008
31/10/2008
31/10/2008
26/10/2007
15/10/2008
15/10/2008
por eso no entiendo muy bien qué está fallando, ¿no hay algo más simple que lo que escribí arriba solo para ordenar los números? no necesito reformatearlo como fecha al final ya que lo voy a copiar en una columna sin usar.
-
-
Sin embargo, con la idea de cambiar el formato funciona impecable...
Sub TrieDate() Dim Plage As Range Dim Col As Byte, LigDeb As Long, DerLig As Long Sheets("TriDate").Select Col = 6 'para la columna F LigDeb = 5 '1ª fila de tri DerLig = 14 Set Plage = Range(Cells(LigDeb, Col), Cells(DerLig, Col)) Plage.Select Selection.NumberFormat = "0" Selection.Sort Key1:=Range("F5"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.NumberFormat = "dd/mm/yyyy" Range("F17").Select End Sub
Resultado...26/10/2007 10/08/2008 15/10/2008 15/10/2008 15/10/2008 31/10/2008 31/10/2008 31/10/2008 15/11/2008 15/05/2009
He probado con el ejemplo que das..
Un saludo
--
La experiencia enseña con más seguridad que el consejo. (André Gide)
Si te golpeas un pote y suena hueco, no necesariamente es el pote el que está vacío. ;-) (Confucio) -
Un poco más automático...
Sub TrieDate() Dim Plage As Range Dim Col As Byte, LigDeb As Long, DerLig As Long Sheets("TriDate").Select Col = 6 'para la columna F LigDeb = 5 '1ª fila de clasificación DerLig = Cells(65536, Col).End(xlUp).Row Set Plage = Range(Cells(LigDeb, Col), Cells(DerLig, Col)) Plage.NumberFormat = "0" Plage.Sort Key1:=Cells(LigDeb, Col), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Plage.NumberFormat = "dd/mm/yyyy" End Sub
--
La experiencia instruye con más seguridad que el consejo. (André Gide)
Si te empeñas en un cubo y suena hueco, no necesariamente es el cubo el que está vacío. ;-) (Confucio)-
Gracias gente, va avanzando bien, casi no falta nada.
Tengo un problema en realidad, tomé tu macro lermitte22 y cuando la ejecuto mis fechas se ordenan primero por día y luego por mes, así obtengo:
01/01/2008
01/03/2009
01/07/2008
01/08/2008
01/08/2008
01/09/2008
01/11/2008
02/10/2008
02/10/2008
03/09/2008
03/10/2008
03/10/2008
03/11/2008
(la lista es larga pero doy un ejemplo simple)
Entonces pensé en quitar la última línea para ver cómo se presenta el formato y de hecho solo la celda 3 pasa a modo estándar (mi primera fila es la fila 2)
Creo que todo el problema viene de que mi celda 1 en la columna de fechas es un texto, eso debe causar problemas para formatear toda la columna.
Por eso cuando al principio intentaba copiar mi columna en la columna AN (vacía) y poner 1 como valor en estándar en la celda 1 para que la fila 1 no se moviera.
Es complicado :s
-
-
Hola,
Incluso en texto eso debería cambiar, ¡excepto si hay espacios en las celdas!
Para probar, pon en una columna vacía =NBCAR(..) y verifica si muestra el número de caracteres que ves.
Si es más, me dices y te pasaré una macro que lo corrija
--
La experiencia instruye más seguramente que el consejo. (André Gide)
Si te golpeas con una rodaja y suena hueca, no necesariamente es la rodaja la que está vacía. ;-)(Confucio)
