Código para seleccionar columnas en VBA
He utilizado un código de Elsa Matilde y me ha venido genial para una hoja que estoy preparando para facilitarme un poco el trabajo. El código es este:
private Sub Worksheet_Change(ByVal Target As Range)
'x Elsamatilde
Dim celda As String
Dim horas As String
Dim minutos As String
Dim segundos As String
'controla el largo del dato
If Len(Target) < 4 Or Len(Target) > 6 Then Exit Sub
celda = Target.Value
If Len(Target) = 4 Then
horas = 0
minutos = Mid(celda, 1, 2)
segundos = Right(celda, 2)
ElseIf Len(Target) = 5 Then
horas = Left(celda, 1)
minutos = Mid(celda, 2, 2)
segundos = Right(celda, 2)
Else
horas = Left(celda, 2)
minutos = Mid(celda, 3, 2)
segundos = Right(celda, 2)
End If
celda = horas & ":" & minutos & ":" & segundos
'para que no se vuelva a ejecutar el change
Application.EnableEvents = False
Target = celda
Application.EnableEvents = True
El problema es que solo quiero seleccionar la columna DE, E, F, G y lo he intentado de todas maneras pero no consigo que funcione, así que me esta dando bastante problemas con el resto de celdas de la hoja ya que me sale todo con ese formato y no puedo cambiarlo por ejemplo si pongo MARZO en una celda lo transcribe así M:AR:ZO.
Tampoco me da el resultado correcto a la hora de sumar horas con el formato de 24 horas [h]: mm
Si me pudieras ayudar os lo agradecería mucho.
2 Respuestas
[Ho la y bienvenida a TodoExpertos!
Visita mi canal: Cursos de Excel y Macros, donde encontrarás varios cursos de Excel y Macros.
---------------------------
Prueba el siguiente código, pone el valor como hora para que lo puedas utilizar como hora.
También verifica si escribiste un número acorde a una hora.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D:G")) Is Nothing Then
With Target
If .Count > 1 Then Exit Sub
If .Value = "" Then Exit Sub
If Not IsNumeric(.Value) Then Exit Sub
'
Dim h As Long, m As Long, s As Long
Select Case Len(.Value)
Case 4
h = 0
m = Mid(.Value, 1, 2)
s = Mid(.Value, 3, 2)
Case 5
h = Mid(.Value, 1, 1)
m = Mid(.Value, 2, 2)
s = Mid(.Value, 4, 2)
Case 6
h = Mid(.Value, 1, 2)
m = Mid(.Value, 3, 2)
s = Mid(.Value, 5, 2)
Case Else
.NumberFormat = "General"
Exit Sub
End Select
If h >= 0 And h <= 23 Then
If m >= 0 And m <= 59 Then
If s >= 0 And s <= 59 Then
Application.EnableEvents = False
.Value = TimeValue(Format(.Value, "00:00:00"))
.NumberFormat = "hh:mm:ss"
Application.EnableEvents = True
End If
End If
End If
End With
End If
End Sub---------------
Cursos de Excel recomendados:
Curso de excel. Consejos y trucos de excel para mejorar la presentación de tus datos. - YouTube
---------------
Cursos de Macros recomendados:
Curso de macros. Consejos para empezar a programar. - YouTube
Curso de macros. Declarar variables en vba excel. - YouTube
--------------
Sal u dos Dante Amor
Muchísimas gracias .
Me ha perfecto, pero no se porque sigo teniendo el problema de la suma de horas
Tengo las horas en formato horas y la celda de la suma en formato [h]:mm
Y no consigo que salga el resultado correcto
Por ejemplo: 7:00+4:00+10:00+12:50+12:29 tendría que dar un resultado de 46:19 y el resultado que me da es 171:19. No se donde esta el problema.
No conozco mucho el mundo de VBA Y EXCEL pero me esta gustando bastante y por supuesto
Voy a ver todos tus videos
¿Y esos valores son horas y minutos o son minutos y segundos?
Puedes poner una imagen para ver qué tienes en la hoja.
También, realiza el ejemplo en otra hoja donde no está la macro. Revisa las sumas.
El resultado para mí es de 46:19
no puedo subir pdf de la captura porque no admite formato.
He realizado la suma en un Excel sin macro y si la realiza bien. tiene que se algún problema de la macro. hay alguna manera para que te pueda enviar el fichero?
Pero no pongas un pdf. Puedes poner aquí una imagen en jpeg
Para subir una foto o una imagen, tienes que guardar la imagen con formato jpg
Ya que tienes tu imagen en un archivo, presiona el icono de añadir imagen:
En la siguiente ventana que te aparece:
Presiona el botón Subir imagen. Selecciona el archivo con tu imagen y presiona Abrir.
La imagen que yo puse es en la hoja que tiene la macro.
Revisa cómo capturas los valores
Yo puse lo siguiente en las celdas de la columna D:
70000
40000
100000
125000
122900
Y la macro convirtió los números en formato de hora (como lo puse en la imagen)
-----------------
Intenta hacerlo tú y luego regresas aquí y me explicas con detalle qué estás poniendo, cómo lo estás poniendo y pones aquí las imágenes.
¿Puedes explicar?
Como veras en la imagen las celdas de la columna H llevan la fórmula SI para que si la columna DE esta vacía deje la celda de la columna H y si no esta vacía resta las horas de entrada y salida .
Pero eso es otra fórmula, no tiene que ver con la macro inicial.
La macro que te puse funciona.
Debes decirme exactamente qué estás capturando en cuáles celdas.
Qué te regresa tu fórmula y que debería regresar.
Es una hoja donde se recogen las horas de entrada y salida por turno en el mes correspondiente y los días en numero y en día de la semana. Tiene una validación de datos en la columna C para reflejar si es día libre, vacaciones, baja, etc y contiene un formato condicional a color dependiendo de lo que sea. Luego esta la columna H que es la del total de horas trabajadas que es una resta de entradas y salidas, pero esta tiene una fórmula para que si la recogida de datos de la columna DE no hay nada deje la celda de la columna H vacía y no aparezca un 0.
La columna J lleva la fórmula ESBLANCO para que si la columna c esta vacía la deje en blanco sino ponga 8:30.
La columna QUE se refiere a horas extras y lleva la fórmula SI Y MAX, es decir si DE esta vacío ponga o, sino el max de h menos j para que no me de horas negativas.
Y al final de las columnas hay un sumatorio para que me de el total de horas trabajadas y el total de horas extras.
Esa hoja va vinculada a otra donde me refleja las horas totales, horas extra, días de vacaciones... por trabajador y luego me queda hacer una macro para que cada mes me copie esas hojas en otra carpeta y las deje en blanco para el siguiente mes.
No se si me he explicado bien.
De nuevo te doy las gracias por tu ayuda.
la macro funciona muy bien,
pongo 70000 y me regresa 7:00:00. el problema ahora es la suma de las horas que no da el resultado correcto
Comparte tu archivo en google drive, comparte para cualquiera que tenga el enlace, copia el enlace y lo pegas aquí.
En la hoja deberán venir varios ejemplos. Ahí en la hoja me explicas en dónde tienes el problema. Y qué resultado necesitas.
Pero no pusiste en la hoja ninguna explicación.
Tampoco pusiste cuáles son los valores erróneos y cuáles son los valores correctos.
Debes ser muy específica en proporcionar información.
Te hago las siguientes recomendaciones para tu hoja:
1. Si entra a una hora, pero su salida es al otro día, entonces debes poner en la columna D fecha inicial y la hora inicial y en la columna E la fecha final y la hora final, de lo contrario no podrás calcular las horas trabajadas.
2. En tus fórmulas pones como resultado un espacio en blanco entre comillas:
=SI(D13=0," ",(E13-D13)+(G13-F13)+1)
No debes poner " ", eso provocará errores en las siguientes fórmulas, ya que el espacio en blanco provoca un error. Cámbialo por cero, podrás realizar otras fórmulas sin problemas.
Te invito a mirar los videos de Eventos de Hojas de mi canal.
En especial el video N° 46, a partir del 06:35, donde comento que lo primero que hay que agregar en los eventos Change (y otros) es el rango de aplicación.
Si, como comentas, mi macro (no sé de cuando será ;) te ha venido genial significa que funciona correctamente. Solo te queda agregar entonces el rango de acción. Lo agrego al inicio y descarto la ejecución si no se trata de las col D:G sin necesidad de seguir hasta el final para encontrar la salida ;)
Private Sub Worksheet_Change(ByVal Target As Range)
'x Elsamatilde
'declaración de variables
'..............
'solo debe ejecutarse en las col D:G
If Intersect(Target, Range("D:G")) Is Nothing Then Exit Sub
'el resto de los controles
If Len(Target) < 4 Or Len(Target) > 6 Then Exit Sub
'el resto del código
'..................
End Sub