Macro para copiar columna

Descargar el ejemplo

Usualmente en Excel no se trabaja en términos de columnas. La mayoría de las plantilllas se elaboran pensando en ampliar el rango de datos en términos de filas. Comparto decididamente esta premisa, especialmente porque una hoja tiene más filas que columnas, exactamante 1048576 filas * 16384 columnas (Office 2013). Pero cuando es inevitable ampliar nuestro número de columnas, nada mejor que hacerlo automáticamente mediante un código de VBA.

En el presente ejemplo se pretende copiar la última columna con datos y pegarla en una columna subsiguiente para ir ampliando el rango paulatinamente. En el modelo de datos que se presenta, se copió la columna J y se pegó en la columna K.

Quiero agradecer al experto "GREGORI00001" de todoexpertos, quien desinteresadamente me dio una mano para terminar el código que implemente en otra plantilla y que da vida a la presente nota.

Aquí el modelo de datos:

El código es el siguiente:

Sub copiar()

Dim Col As Integer

Col = ActiveSheet.Range("XFD1").End(xlToLeft).Column

ActiveCell.Offset(0, -1).Select

Selection.EntireColumn.Insert

ActiveCell.Offset(0, -1).Select

ActiveCell.EntireColumn.Select

Selection.Copy

ActiveCell.Offset(0, 1).Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    ActiveCell.Offset(2, 0).Select

End Sub

Explicando paso a paso:

1. Col = ActiveSheet.Range("XFD1").End(xlToLeft).Column, recorre la fila uno desde la última columna hasta la primera columna donde encuentre dato.

2. ActiveCell.Offset(0, -1).Select, se posiciona una columna hacia la izquierda respecto de la primera columna con dato que encuentra.

3. Selection.EntireColumn.Insert, Inserta una columna en columna activa.

4. ActiveCell.Offset(0, -1).Select, se posiciona una columna hacia la izquierda respecto de la columna insertada, que es la columna con datos que requiero copiar.

5. ActiveCell.EntireColumn.Select

    Selection.Copy

Selecciona y copia la columna activa.

6. ActiveCell.Offset(0, 1).Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

Se posiciona una columna hacia la derecha respecto de la posición de la columna copiada y,  deshabilita el modo copiar.

7.     ActiveCell.Offset(2, 0).Select

Finalmente, se posiciona dos filas debajo de la fila activa. 

Buscar en dos direcciones combinando las funciones Coincidir, Dirección e Indirecto

Descargar el ejemplo xls.

Ya había escrito una nota referente a este tema, usando las funciones Coincidir e Indice. Accidentalmente vuelvo a retomarlo por causa de una necesidad que vi en una plantilla. Se trataba de una referencia de celda que cambiaba al insertar una columna; por lo que la referencia quedaba desactualizada cada vez que se ejecutaba la mencionada acción, solo respecto de la columna, puesto que la referencia de fila era constante.

En la presente nota, se tiene una base datos con las ventas por días en siete sedes, lo que se pretende es consultar las ventas de cualquier día, en cualquiera de las siete sedes. 

Para tal fin, se implementaron dos listas validadas (opción validación de datos-pestaña Datos) con las sedes y los días respectivamente. 

Lista de sedes:

Lista de días: esto desde la hoja "ventas". También se puede hacer creando un nombre para el rango de datos. Véase la nota: asignar nombres a celdas y rangos. 

Pasando a la consulta que quiero ilustrar, debo mencionar que el truco consiste en usar la función dirección para establecer la referencia de celda correspondiente; para finalmente consultar el valor con la función Indirecto. Una de las ventajas que encuentro de esta forma de trabajar, es que no se hace referencia a una matriz de datos; lo que le confiere mayor flexibilidad a la búsqueda, ya que no se requiere actualizar la formulación si se ingresan nuevos datos.

Lo primero que debo hacer es usar la función coincidir para traducir cada valor de las listas sede y día en una referencia de posición. En otras palabras, lo que busco es construir una dirección en términos de fila y columna en donde se encuentra el dato consultado.

He usado las celdas B2 y D2 concatenadas como argumento de búsqueda, dado que en la hoja "ventas" los nombres de las sedes estan así: sede1, sede2, sede3, etcétera.

Ahora hago lo mismo con los días:

Ahora que ya tenemos nuestra referencia de fila y de columna, vamos a usar la función Dirección para que la presente en forma dirección de celda:

El resultado es:

La celda F27 de la hoja "ventas".

Para comprender un poco más el funcionamiento de "Dirección", veamos los argumentos:

=DIRECCION(fila; columna; [abs]; [a1]; [hoja])

fila: número de fila.
Columna: número de columna.
abs: es opcional y puede ser un número entre 1 y 4 que indica el tipo de referencia absoluta que se aplicará (1 = ref. absoluta en fila y col., 2 = ref. absoluta solo en fila, 3 = ref. absoluta solo en col. y 4 ref. relativa en fila y col.). Si no se especifica nada, dejará la referencia absoluta en fila y columna, como en nuestro caso.
a1: especifica el estilo de referencia a presentar, A1 o F1C1.
hoja: es el nombre de la hoja de donde se obtiene la referencia, si se omite, se extraerá de la hoja activa. En nuestro caso especificamos el nombre de la hoja "ventas".

Visto lo anterior, solo resta hacer nuestra consulta mediante la función Indirecto; ya había escrito una nota acerca de esta función: hacer consultas con la función indirecto.

Comprobamos que para la sede 4, el día 26; las ventas fueron de 5521 unidades.

 

Estimación.Lineal

Hola Amigos de MBExcel.

Después de una larga temporada sin escribir, me aventuré, más que a escribir, a explicar mediante un video ¿Cómo obtener los estadígrafos básicos de una regresión lineal usando la función Estimación.Lineal?

Debo mencionar que esta intención nace de varias consultas que he recibido acerca del tema:

Anónimo17 de octubre de 2016, 09:17

Buenos días, me pueden explicar como obtener la tabla con los resultados de la estimación lineal, ya que cuando hago este cálculo solo me aparece como resultado el valor de la pendiente en una celda, pero no la tabla completa con todos los resultados como se ve en el ejemplo.
Gracias

Responder

Buenas tardes:
Sigo con el mismo problema al tratar de obtener la estimación lineal. Yo tengo Excel 2010 y cuando entro la matrix de 2 columnas y 21 filas que se corresponde con tres repeticiones de la curva de calibración de siete puntos, en lugar de obtener la tabla completa con todos los estadígrafos obtengo solamente la pendiente del gráfico en una sola celda. Me podrían ayudar por favor,

Gracias

Multiplicar horas y minutos por enteros

Una mañana cualquiera y cansado del mismo requerimiento, decido formular mediante MS Excel una simple pero útil solución.

Requería saber el tiempo en horas y minutos que estuvo por fuera de funcionamiento un conjunto de equipos; con lo que surgía la necesidad de multiplicar las horas y/o minutos de inactividad por el número de equipos. Para resolver tal necesidad no bastaba con ejecutar una sencilla operación matemática, por lo que hubo que valerse del formato personalizado "[h]:mm"; ya que las horas y minutos en Excel corresponden a un número en el que la parte decimal corresponde a la parte proporcional de un día; así, por ejemplo 0.5 corresponde a las 12:00 PM, 0.8 corresponde a las 19:12 PM y 1 corresponde a las 24:00 horas. De acuerdo con lo mencionado y, acudiendo a mi afición por resolverlo todo mediante Excel, surge lo siguiente:


Podemos insertar una tabla (uno de mis favoritos) y sumar mediante la fila de totales.

La columna D lleva el formato mencionado: "[h]:mm".

Por último, podemos sumar la cantidad de horas y minutos en la tabla, usando la opción totales. Puede verse que existen múltiples opciones; tales como: promedio, cuenta, máximo, mínimo, suma, desviación estándar, varianza, entre otras.

Buscar valor mediante macro

 Descargar el ejemplo
 

El título de esta nota parece un tanto inusual teniendo en cuenta que existe una función para buscar de manera vertical, en una o dos dimensiones, sin tener que acudir al "tortuoso" pero fascinante escenario de las macros.

Un amigo, ávido de implementar mejoras ofimáticas en el supermercado donde labora, me pregunta lo siguiente: 

Tengo un sensor de código de barras que transmite el código de cada producto a una hoja de cálculo ¿cómo puedo hacer que Excel busque ese valor en una columna determinada, y que cuando encuentre ese valor, se situe en una celda contigua al valor encontrado?

La respuesta fue, un código de Visual Basic.

Cómo no soy experto en el tema, tome en cuenta varias referencias; entre ellas el material notable de la experta Elsa Matilde. 

El requerimiento supone que el código detectado siempre quedará incluido en la celda G2; este sera el valor buscado en la columna D. Cuando se encuentre el valor , la celda activa será la celda contigua al valor encontrado; es decir, la celda correspondiente a la columna E.

Aquí el código que será incluido en un módulo:

Sub buscador() Dim mihoja As String, Donde As String Dim Quebusco As String Dim resulta As Object Dim ubicado As String 'la variable mihoja guarda la hoja donde se hará la búsqueda mihoja = "Hoja1" 'la variable Donde guarda el rango donde debe efectuarse la búsqueda, columna D Donde = "D:D" 'la variable Quebusco guarda el dato a buscar que se encuentra en la celda G2 Quebusco = Range("G2").Value 'se crea un objeto con el resultado de la función Find Set resulta = Sheets(mihoja).Range(Donde).Find(Quebusco, LookIn:=xlValues, LookAt:=xlWhole) If resulta Is Nothing Then 'si no se encuentra el dato puede mostrar un mensaje de error como el siguiente MsgBox "No se encontró el dato", vbCritical, "NO ENCONTRADO" Else 'si encontró el dato, se situa una columna hacia la derecha, celda contigua al dato encontrado. resulta.Offset(0, 1).Select End If 'se limpia la variable Set resulta = Nothing End Sub

Para terminar, se puede asignar la macro a un botón de formulario para facilitar la ejecución de la macro.