Macro para transformar datos de horizontal a vertical

En este video explico como utilizar la macro que sirve para transformar información que se encuentra de manera horizontal a vertical, esto es util cuando nos pasan los datos en columnas hacia la derecha y nos cuesta mucho darle un formato de base de datos.

Les comparto el link para que puedan descargarlo

Macro para crear indice de hojas en excel

Cuando tenemos muchas hojas en nuestro libro de excel a veces necesitamos poner un vinculo a cada hoja desde una hoja principal para poder dirigirnos a cada una de ellas, esto se llama crear indice de hojas de excel, pero hacerlo de forma manual tomaría mucho tiempo si se tratara de varias hojas. Existe una manera mucho más rápida de hacerlo con tan solo un clic, y esto se logra usando programación vba o también llamado macro. 

Pasos previos

Debemos tener un libro con 7 hojas la primera sera la hoja "Indice" en la cual se generarán los indices de cada hoja y las 6 restantes tendrán el nombre de cada mes  desde enero hasta junio.

En la celda A1 de la hoja Indice pondremos el texto "Lista de hojas" que sera nuestra cabecera, y de allí para abajo se almacenaran los indices de cada hoja.

Crear macro para insertar el código

Nos dirigimos  al entorno de programación vba (Visual basic para Aplicaciones) presionando las teclas Alt+F11, luego en el menú le damos clic en Módulo.

Dentro del modulo pegaremos el siguiente código:

Sub Crear_Indice_Hojas()
Fila = 1
For Each Hoja In Worksheets
    ' Agregar vinculo a cada hoja del mes
    If Hoja.Name <> "Indice" Then
        With Worksheets("Indice")
            .Hyperlinks.Add Anchor:=.Cells(Fila, 1), Address:="", _
            SubAddress:=Hoja.Name & "!A1", TextToDisplay:=Hoja.Name
            'Agregar vinculo de retorno a la hoja indice
        End With
        
        With Worksheets(Hoja.Name)
            .Hyperlinks.Add Anchor:=.Cells(1, 2), Address:="", _
            SubAddress:="Indice!A1", TextToDisplay:="Indice"
        End With
    End If
    Fila = Fila + 1
Next
End Sub

Para usar el código que hemos programado simplemente debemos seleccionar la hoja "Indice" y presionamos la combinación de teclas Alt + f8 y ejecutar la macro "Crear_Indice_Hojas".

Una vez ejecutada la macro nos agrega dentro de la hoja "Indice" los nombres de cada hoja del mes en una lista a partir de la celda A2.

También se agrega un vinculo de retorno hacia la hoja "indice" en cada Hoja de mes en la celda B1, si deseamos modificar la posición del vinculo, dentro del código deberíamos modificar esta parte: cells(1,2) donde 1 es la posición de la fila y 2 la posición de la columna por la celda que queramos.

With Worksheets(Hoja.Name)
            .Hyperlinks.Add Anchor:=.Cells(1, 2), Address:="", _
            SubAddress:="Indice!A1", TextToDisplay:="Indice"
        End With

Finalmente guardamos nuestro libro con el tipo "Libro de excel habilitado para macros"

De una manera sencilla nos ahorramos mucho tiempo en crear los indices para nuestras hojas de excel.

Desde aquí puedes descargar el archivo de ejemplo 

Funcion para sumar digitos de un numero en excel vba

Vamos a crear una función para sumar dígitos de un numero en excel vba la cual la realizaremos usando vba (Visual basic para aplicaciones)

Ingresaremos a nuestro editor de vba y agregamos un modulo en el cual pegaremos el siguiente código que esta explicado en los comentarios.

Function SumarDigitos(Numero As Double)
'Recorrer lac cantidad de digitos que tiene el numero
For i = 1 To Len(Numero)
    'Almacenar la suma en la funcion SumarDigitos
    SumarDigitos = SumarDigitos + Val(Mid(Numero, i, 1))
Next
End Function

Para usar la función debemos dirigirnos a cualquier celda de nuestra hoja y escribir la funcion
=SumarDigitos(Numero) donde Numero es un valor numerico o referencia a la celda de la cual se desea sumar sus digitos.

Veamos el ejemplo

Función para sumar los dígitos de un número

Se debe tomar en cuenta lo siguiente:
Muestra cero cuando se quiere sumar valores que no sean numéricos, como caracteres especiales o letras.
Si se ingresa un numero negativo, este no se toma en cuenta.

Registro de productos desde un formulario vba hacia una hoja de excel

En este tutorial les voy a enseñar como realizar el registro de productos desde un formulario hacia una hoja de excel.

El formulario cuenta con el boton nuevo que nos permite generar un codigo autonumerico para la columna codigo de producto, un boton grabar el cual primero valida que los datos a ingresar sean correctos y luego inserta los valores del formulario hacia la hoja de productos y un boton salir el cual cierra el formulario.

Se ha tratado que los controles utilizados para el ingreso de informacion sean los basicos como el label, textbox y combobox.

Este tutorial consta de dos videos secuenciales.

Video # 1 - Diseñando el formulario de ingreso de productos




Video # 2 - Programando el formulario de ingreso de productos.

El formulario terminado lo pueden descargar desde este link.

Macro en excel para convertir texto a mayúscula, minúscula y nombre propio

Macro en excel para convertir texto a MAYÚSCULA, minúscula y Nombre Propio
Actualmente en excel existen estas funciones, pero la limitación es que si tenemos varias celdas (unas 1000 celdas por ejemplo) deberíamos convertirlas en otras celdas y luego reemplazarlas para que surjan efecto.

Lo que haremos con estas macros sera convertirlas en el mismo lugar donde se encuentran con un solo paso.

Y bien pondremos los códigos vba de cada una.

Convertir a mayúsculas

Sub Mayusculas()
Application.ScreenUpdating = False
For Each celda In Selection
    celda.Value = UCase(celda)
Next
Application.ScreenUpdating = True
End Sub

Convertir a minúsculas

Sub Minusculas()
Application.ScreenUpdating = False
For Each celda In Selection
    celda.Value = LCase(celda)
Next
Application.ScreenUpdating = True
End Sub

Convertir a Nombre propio

Sub NombrePropio()
Application.ScreenUpdating = False
For Each celda In Selection
    celda.Value = WorksheetFunction.Proper(celda)
Next
Application.ScreenUpdating = True
End Sub

Para utilizarlo, deben seleccionar el rango de celdas que desean convertir y luego ejecutar el código respectivo y verán que da el resultado esperado.

Ingreso de datos desde formulario vba en una hoja de excel

Cuando deseamos ingresar información a una hoja de excel mediante un formulario vba. podemos hacerlo de la siguiente manera.

Abrimos un nuevo libro de excel y creamos una hoja y la nombraremos "Datos", dentro de ella tendremos las siguientes columnas: Código,Nombres y ciudad como se muestran en la siguiente imagen.

El objetivo es llenar información en la hoja "DATOS" según lo que vayamos ingresando en el formulario, este tendrá las cajas de texto y botones que permitirán el ingreso de información mas ordenada y validada.

Para lo cual nos dirigiremos al entorno de programacion VBA (Presionamos las teclas ALT + F11) e insertamos un userform al cual le agregaremos los siguientes controlesy le asignaremos los valores indicados de sus propiedades.

Orden  Control                Caption                                          Name
1           Label                    Ingresar informacion de clientes    Por defecto
2           Label                    Código                                            Por defecto
3           Label                    Nombres                                         Por defecto
4           Label                    Ciudad                                            Por defecto
1           CommandButton  Registrar                                        btnRegistrar
2           CommandButton  Cerrar                                             btnCerrar
1           Textbox                (Vacio)                                           txtCodigo
2           Textbox                (Vacio)                                           txtNombres
3           Textbox                (Vacio)                                           txtCiudad
El formulario quedara como muestra la siguiente imagen.

Una vez diseñado el formulario pasaremos a programarlo para que nos permita insertar informacion dentro de nuestra hoja "DATA"

Colocamos el siguiente código en el evento clic del botón Registrar.

Private Sub btnRegistrar_Click()
Dim uFila As Integer '
'Validar que los campos a registrar no se encuentren vacios
If Trim(txtCodigo.Text) = "" Then
    MsgBox "Ingrese un valor en el campo Código", vbExclamation, "Validación"
    txtCodigo.SetFocus
    Exit Sub
End If
If Trim(txtNombres.Text) = "" Then
    MsgBox "Ingrese un valor en el campo Código", vbExclamation, "Validación"
    txtNombres.SetFocus
    Exit Sub
End If
If Trim(txtCiudad.Text) = "" Then
    MsgBox "Ingrese un valor en el campo Código", vbExclamation, "Validación"
    txtCiudad.SetFocus
    Exit Sub
End If
With Sheets("DATOS")
 'Obtiene la ultima posicion de datos de la hoja Datos + 1
    uFila = .Range("A" & Rows.Count).End(xlUp).Row + 1
    'Registrar datos del formulario en la hoja DATOS
    .Cells(uFila, 1).Value = txtCodigo.Text
    .Cells(uFila, 2).Value = txtNombres.Text
    .Cells(uFila, 3).Value = txtCiudad.Text
End With
Call Limpiar 'Llama a la subrutina para limpiar los textbox
End Sub

El codigo anterior utiliza una subrutina para limpiar los campos del formulario que es la siguiente y la tenemos que pegar fuera del boton registrar:

'Subrutina creada para limpiar campos del formulario
Sub Limpiar()
txtCodigo.Text = ""
txtNombres.Text = ""
txtCiudad.Text = ""
txtCodigo.SetFocus
End Sub

Luego dentro del evento clic del botón cerrar colocamos el código para cerrar el formulario.

Private Sub btnCerrar_Click()
'Cerrar el formulario
End
End Sub

Ahora para poder abrir el formulario e ingresar información de manera mas facil, podemos ingresar una auto forma dentro en nuestra hoja  Datos. Nos dirigimos a la pestaña insertar y en el grupo ilustraciones damos clic en formas e insertamos in rectángulo, Le agregamos el texto "Formulario"

Posteriormente nos dirigimos al entorno de programación e insertamos un modulo y pegamos el siguiente código:

Sub AbrirForm()
UserForm1.Show
End Sub

Entonces, le damos clic derecho al botón creado en la hoja excel y le damos clic en "asignar macro" y elegimos la macro llamada "AbrirForm". Esto permitirá que al darle clic al botón formulario se nos muestre el formulario sin necesidad de ingresar al código.

Finalmente guardamos nuestro archivo de excel y en el tipo de archivo elegimos Libro de excel habilitado para macros

De esta manera hemos realizado el ingreso de información desde un formulario en vba a una hoja de excel. Espero les haya sido de utilidad.

Puedes descargar el código desde aquí.

Filtrar datos que contienen tildes en excel vba

En excel podemos realizar filtro de datos para mostrar los datos que queremos de un cierto rango de datos.
En esta oportunidad tenemos información en  nuestra columna "A" entre los cuales hay algunas palabras que contienen datos con tildes

Nuestro objetivo es mediante una macro en vba filtrar aquellos datos que tengan tilde. y que quede como esto.

Para eso crearemos un modulo en nuestro entorno de programacion vba y pegamos el siguiente código

Sub FiltrarTildes()
Range("A1:A11").Interior.Color = xlNone
For Each celda In Range("A1:A11")
    pos = InStr(1, celda, "á", vbTextCompare) + _
    InStr(1, celda, "é", vbTextCompare) + _
    InStr(1, celda, "í", vbTextCompare) + _
    InStr(1, celda, "ó", vbTextCompare) + _
    InStr(1, celda, "ú", vbTextCompare)
    If pos > 0 Then
         celda.Interior.Color = vbWhite
    End If
Next
ActiveSheet.Range("A1:A11").AutoFilter field:=1, Criteria1:=RGB(255, 255, 255), Operator:=xlFilterCellColor
End Sub

Este codigo busca las tildes dentro de nuestro rango de datos ( A1:A11)  y pinta de color blanco la celda donde encuentra una tilde.

Posteriormente se filtran los datos que tengan el color de fondo de blanco rojo.

Y de esa manera se muestran los datos que contienen tildes.

En este enlace les comparto el archivo de ejemplo 

Trabajar con tablas dinamicas en excel 2013 #1

Siguiendo con la serie de tutoriales aquí les dejo un video de como trabajar con tablas dinámicas en excel 2013, no varia casi nada en cuanto a otras versiones anteriores de excel.

Posteriormente estaré subiendo la segunda parte de este tutorial ya que el tiempo ha quedado corto.
Espero lo disfruten .

Mover la barra de desplazamiento SmallScroll usando excel vba

En excel existe un código para mover la barra de desplazamiento y de esta manera podemos dirigirnos hacia cualquiera de los lados izquierda, derecha, abajo y arriba

Por ejemplo si queremos mover la barra de desplazamiento hacia 15 filas abajo debemos escribir el siguiente código:

Sub MoverAbajo()
ActiveWindow.SmallScroll Down:=15
End Sub

De igual manera podemos hacerlo para los demás lados:

Up: Hacia Arriba

Down: Hacia Abajo

ToRight: Hacia la derecha

ToLeft: Hacia la izquierda.

Una vez explicado la manera de usar este código vamos a realizar un ejemplo completo que consiste en lo siguiente:

Ejemplo:

En una hoja de nuestro libro (ver figura 1) se tiene movimientos de venta de varios meses.

Vemos que en la figura se muestra el mes enero en la columna "A". De esa manera mas abajo (No se ve en la imagen) se tiene información del mes febrero, marzo, etc.

El objetivo es seleccionar el mes en la celda B1 y que automáticamente se muestre en pantalla el movimiento de ese mes. Eso lo lograremos haciendo uso de la propiedad SmallScroll en vba.

Figura 1

Trabajando...

Aquí podrás bajar el archivo con el cual trabajaremos, cuenta con dos hojas, la "Consulta" contiene información de las ventas desde el mes de enero a abril. En la celda B1 se encuentra una lista desplegable con validación de datos, el cual contiene los meses de enero a diciembre que servirá para mostrar las ventas de un determinado mes que elija el usuario.

Se tiene también la hoja "Meses" el cual contiene los meses de enero a diciembre que se usa en la hoja consulta.

Bien entonces vamos a presionar las teclas Alt+F11 para ingresar a nuestro editor de vba.

Añadiremos un modulo y pegaremos el siguiente código

Sub MiScroll()
'Buscar mes
On Error GoTo Err
Range("B2").Select
pos = Range("A:A").Find(Range("B1").Value).Row - 2
ActiveWindow.SmallScroll Down:=pos
Exit Sub
Err:
If Err.Number = 91 Then
    MsgBox "No se encuentra el mes seleccionado", vbInformation, "Error"
Else
    MsgBox Err.Description, vbCritical, "Error"
End If
End Sub

Explicacion del codigo:

pos = Range("A:A").Find(Range("B1").Value).Row - 2

Con este codigo buscaremos el mes que elegimos en la celda B1 de nuestra hoja consulta en toda la columna "A" y si lo encuentra guardamos la posición del mes en la variable pos y le restamos menos 2.

ActiveWindow.SmallScroll Down:=pos

Como la variable pos almacena el numero de la fila donde encontramos el mes, utilizaremos ese numero para movernos hacia abajo y colocamos la variable pos en vez de un numero fijo en nuestro codigo.

If Err.Number = 91 Then
    MsgBox "No se encuentra el mes seleccionado", vbInformation, "Error"
Else
    MsgBox Err.Description, vbCritical, "Error"
End If

 En caso no se ubique el mes nos mostrara un mensaje personalizado.

Luego estando dentro de nuestro entorno de desarrollo le damos doble  clic al objeto Hoja1(Consulta) y pegamos el siguiente código:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row = 1 Then
    Call MiScroll
End If
End Sub

Este código sirve para llamar a la función MiScroll que tenemos en nuestro modulo 1 cada vez que seleccionamos el mes desde la Celda B1.

Una vez finalizado el nuestro ejemplo lo guardamos en nuestra pc como tipo de libro "Libro de excel habilitado para macros".

En este enlace les dejo el archivo finalizado

Funcion para generar sentencia SQL Insert into desde excel

Vamos a crear una función en excel la cual nos permita generar una cadena de texto de la sentencia INSERT INTO de SQL Server a partir de los datos de una o varias columnas así como vemos en la imagen inferior.
Tenemos el registro de países con los campos idpais, descripción y a la derecha se genera la sentencia insert into de todos los registros.

Esto lo lograremos utilizando una función personalizada en vba.

DESARROLLO

Presionamos Alt + F11 para dirigirnos a nuestro entorno de visual basic

Menu insertar - > Modulo

Pegamos el siguiente código:

Function GenerarSQL(Rango As Range, Tabla As String)
For Each celda In Rango
    If IsNumeric(celda) Then
        Concat = Concat & celda & ","
    Else
        Concat = Concat & Chr(39) & celda & Chr(39) & ","
    End If
Next
    GenerarSQL = Left(Concat, Len(Concat) - 1)
    GenerarSQL = "INSERT INTO " & Tabla & " VALUES (" & GenerarSQL & ")"
End Function

Esta funcion llamada GenerarSQL va a constar de dos parámetros de entrada:
Rango: Rango de celdas las cuales contienen información y que representaran a un campo de una tabla.
Tabla: Cadena de texto que sera el nombre de la tabla.

Si los valores de nuestro parámetro Rango son de tipo texto se le  agrega una camilla simple antes y después del valor. Ya que para el SQL la cadena de texto lo necesita.

EJECUCIÓN
Nos ubicamos en la celda E4 de nuestra hoja de calculo y escribimos la función que hemos creado anteriormente: =GenerarSQL(B4:C4,"TB_PAISES")

Desde B4:C4 se encuentran los datos y "TB_PAISES" es el nombre de la tabla a la cual le vamos a insertar los datos.

SQL SERVER

En nuestra base de datos creamos nuestra tabla llamada TB_PAISES

CREATE TABLE [dbo].[TB_PAISES](
[IDPAIS] [int] NULL,
[DESCRIPCION] [varchar](250) NULL
)

Luego copiamos la sentencia de la columna "E" de nuestra hoja de excel y pegamos en el sql server, Ejecutamos las sentencias y se registraran todos los países que teníamos en nuestra hoja de excel.

Esto puede servirnos para insertar registros de nuestra hoja de excel hacia cualquier tabla ya que es personalizable el rango de celdas y el nombre de la tabla.

Desde aqui podran bajar el archivo de ejemplo.

Función en vba para concatenar múltiples celdas en excel

Existen muchas maneras de concatenar las celdas de una hoja en excel, lo común es hacerlo con la función concatenar o con el símbolo "&". Pero cuando deseamos concatenar varias celdas nos resulta muy trabajoso realizarlo de manera individual así que a veces nuestra paciencia se acaba.

La otra de forma de  realizarlo es crear una función UDF en excel vba que nos permita concatenar las celdas que nosotros necesitemos en un solo paso.  Entonces pasaremos a crear la sintaxis de nuestra función a crear.


Una vez que tengamos abierto nuestro libro de excel realizaremos lo siguiente:

- Presionamos ALT + F11 para ingresar a VBA
- Clic en el menú insertar / Agregar modulo

Luego en el modulo pegamos el siguiente código

Function UnirCeldas(Rango As Range, Sep As String)
For Each celda In Rango
    concat = concat & celda & Sep
Next
UnirCeldas = Left(concat, Len(concat) - 1)
End Function

Esta función nos sirve para poder concatenar todas las celdas tanto verticales como horizontales que nosotros necesitemos.

Entonces vamos a explicar la sintaxis de nuestra función para luego aplicarla en una hoja de excel

=UnirCeldas(Rango,Sep)

Rango: Rango de celdas a concatenar
Sep: Separador de cada celda al momento de concatenar

Ponemos un ejemplo para su mayor comprensión

Luego tienen que guardarlo como libro de excel habilitado para macros 

Función para obtener el nombre de una columna a partir de un número

Para realizar nuestro ejemplo Función para obtener el nombre de una columna a partir de un número primero debemos tener en cuenta lo siguiente.

Como todos sabemos las hojas de excel están compuestas por filas y columnas y en este caso nos centraremos en las columnas.Las columnas se nombran con letras del alfabeto ingles tales como A,B,C,etc. Estas columnas también tienen una posición numérica.

Por ejemplo la Columna A es nuestra primera columna por lo tanto tiene la posición 1, La B tiene posición 2 y así sucesivamente.

Entonces una vez que tenemos esto claro, vamos a explicar en que consiste nuestro ejercicio:
Crearemos una función udf usando programación vba la cual nos va a permitir ingresar un numero entero y como resultado nos dará el nombre de una columna, por ejemplo si ingreso el numero 1 nos mostrara la letra "A", el numero 2 la letra "B", el 5 la letra "E"

Bueno ahora empecemos a programar...

Creamos nuestra función llamada NumLet la cual tiene como parámetro la variable Num de tipo integer, esta variable nos indicara la posicion de la columna que queremos mostrar

Function NumLet(Num As Integer) As String
End Function

La cantidad de columnas que existe en una hoja de excel tiene un limite que en el caso de mi versión 2007 es 16384 que es la columna "XFD".En caso que ustedes utilicen otra versión pueden cambiarla.
Esta validación nos sirve para salir de la función en caso que el usuario ingrese un numero fuera del rango

If Num > 16384 Then Exit Function

Una vez tengamos el valor de nuestra variable "num" solo nos hará falta convertirlo en una referencia de rango. Entonces a nuestra propiedad cells le asignaremos la fila 1 y la columna Num (valor entero) e indicamos que queremos la dirección de la celda con la propiedad .Address y lo almacenamos en la variable dato. Si el valor de nuestra variable Num es 1, nuestro dato almacenara "SA$1"

dato = Cells(1, Num).Address

Ahora solo nos falta obtener La letra que se encuentra en la referencia de celda.'La letra siempre va a estar entre los símbolos de "$" por lo tanto habría que extraer la letra usando la función mid (extraer)
Pongamos de ejemplo que nuestra variable dato sea "$A$1":

Como parámetro "string" le indicaremos la variable dato, obtendremos información a partir del segundo dígito y por ultimo le indicaremos que nos tome la cantidad de dígitos restando "len(dato)" que es la cantidad de dígitos de la variable dato menos 3. Esto nos retornara a la función NumLet la letra A que es lo que necesitamos finalmente.

NumLet = Mid(dato, 2, Len(dato) - 3)

Finalmente nuestro código quedara de la siguiente manera

Function NumLet(Num As Integer) As String
If Num > 16384 Then Exit Function
    dato = Cells(1, Num).Address
    NumLet = Mid(dato, 2, Len(dato) - 3)
End Function

El cual podemos aplicarlo a una hoja

O también utilizarlo en otra función o subrutina:

Sub Columna()
    MsgBox NumLet(1)
End Sub

Espero que la explicación haya sido clara y puedan usar esta función al máximo.

Contar la cantidad de celdas de un determinado color de fondo

En excel no existe una función predeterminada que nos permita contar la cantidad de celdas que contienen un determinado color de fondo. Sin embargo podemos realizar una función udf que nos permita realizar tal cosa haciendo uso de programación vba

Analisis:

Veremos en nuestra imagen inferior, dos bloques: el bloque A el cual contiene datos desde la celda A2:A12 cada celda con un determinado color (con colores repetidos), y por otro lado el bloque B con una lista de 3 colores únicos los cuales son iguales al del Bloque B.

La idea es contar la cantidad de veces que se repiten los colores del bloque B en el Bloque A, es como si fuera un contar.si pero con colores de fondo de una celda.

Antes de comenzar con nuestro código vamos a explicar que la manera de saber el color de fondo de una celda usando vba es a través del código interior.color que hace referencia a una celda.
Por ejemplo :
Al ejecutar el siguiente código nos muestra un mensaje con el numero de color de la celda .
De esta manera cada color podemos identificarlo con un numero. que en este caso para el amarillo sera 16777215

Sub ColorFondo()
MsgBox Range("A2").Interior.Color
End Sub

Bueno una vez analizado lo que se requiere, crearemos la funcion udf en vba.
Para lo cual ingresamos al editor de vba e insertamos un modulo en el cual escribiremos este codigo que a continuación pasaremos a explicar

Creamos nuestra función  llamada ContarColorRelleno y le colocamos dos parámetros de tipo Range: MatrizColores y ColorCriterio:

MatrizColores: Es la lista de colores que se va a contar, en este caso esta en nuestro bloque A
ColorCriterio: Es el color con el cual deseamos comparar la lista de colores. Este criterio seria nuestro bloque B.

codigo:

Function ContarColorRelleno(MatrizColores As Range, ColorCriterio As Range)

 La explicacion de cada linea del codigo se encuentra comentada de color verde.

Dim cont
Application.Volatile 'Permite actualizar la funcion al editar cualquier celda de la hoja
For Each celda In MatrizColores
    If celda.Interior.Color = ColorCriterio.Interior.Color Then ' Valida si el color es igual
        cont = cont + 1 'si es verdadero entonces incrementa la variable cont
    End If
Next
    ContarColorRelleno = cont 'Asignamos la cantidad de veces que se repite un color a la funcion

Una vez que hayamos finalizado con nuestro la sintaxis queda de la siguiente manera:

=ContarColorRelleno(MatrizColores,ColorCriterio)

Lo usaremos en la celda E2: =ContarColorRelleno($A$2:$A$12,D2)

De esta manera nos cuenta en E2 cuantas celdas de color amarillo existe en nuestro rango A2:A12

Y asi podemos realizarlo con la cantidad de celdas que nos haga falta.

Espero les haya sido de mucha utilidad este pequeño codigo. Pueden descargar el ejemplo en este enlance

Unir todas las hojas de un libro de excel en una sola

En esta oportunidad haré entrega de una pequeña aplicación la cual cumple con la tarea de unir todas las hojas de un libro de excel en una sola.
Esto nos facilita el trabajo de realizarlo manualmente y nos ahorra tiempo.

Pasaré a explicar como funciona esta aplicación paso a paso.

Paso 1:
Presionar la combinación de teclas: Ctrl + H y se nos mostrara la siguiente ventana donde se cargara en una lista todos los libros abiertos para elegir a cual de ellos le vamos a unir sus hojas.

Paso 2:

En la parte que dice "Nombre de la hoja nueva" escribiremos el nombre de la hoja en la cual se unificara los datos de todas las hojas del libro seleccionado en el paso 1.

Paso 3 :

Por ultimo le daremos clic al botón unir el cual creara una nueva hoja con el nombre definido en el paso 2, y nos mostrara el siguiente mensaje.

El proceso ha finalizado y se realizo la consolidación de todas las hojas de nuestro libro en una sola hoja, tal como vemos en la siguiente imagen:

Consideraciones a tener en cuenta:

- Solo se puede seleccionar un solo libro por tarea.

- El aplicativo colocara el nombre de cada hoja en la primera columna.

Descargar aquí

Abrir archivo de excel mediante formulario de login

Aqui se muestra un ejemplo en el cual mediante programacion vba creamos un formulario de login que nos pide ingresar el usuario y contraseña para abrir nuestro libro de excel.

Una vez ingresado los datos correctos y aceptamos, nos dirige hacia el archivo de excel que lo contiene.

Concatenar multiples celdas con excel vba

En esta oportunidad aprenderemos como concatenar múltiples celdas de manera fácil en excel , creando una función definida de usuario (udf) con programación vba (Visual basic for applications)
Esto se puede aplicar a cualquier versión de excel.
Espero les sea de mucha utilidad.

Calcular la edad en excel

Existen varias maneras de calcular la edad en excel, pero en esta oportunidad vamos a mostrar una manera sencilla y exacta de realizarlo.

Presentaremos dos formas de realizarlo, una con formulas y otra con vba

La edad se obtiene restando los años de la fecha actual (29/03/2015) que es la fecha actual de mi sistema, menos el año de la fecha de nacimiento que se encuentra en la celda A2.

Pero si realizamos una resta simple obtendremos 28 como edad, que seria incorrecto, porque aun falta un día para que la edad sea 28, entonces se aplican dos condiciones para que el calculo de la edad sea correcta que mostramos en la siguiente formula

=SI(Y(MES(HOY())>=MES(A2),DIA(HOY())>=DIA(A2)),AÑO(HOY())-AÑO(A2),AÑO(HOY())-AÑO(A2)-1)

Condicion 1:
Si el mes actual es mayor o igual que el mes de nacimiento.

Condicion 2:
Si el dia actual es mayor o igual que el dia de nacimiento.

Explicacion:
La mejor manera de explicarlo seria con unos ejemplos.

Ejemplo 1:

Fecha Nacimiento         Fecha Actual    Edad
 30/03/1987                   29/03/2015         27

Ejemplo 2:
Fecha Nacimiento         Fecha Actual    Edad
 30/03/1987                   30/03/2015         28

Para el ejemplo 1 la edad es 27 ya que no cumple ni la condicion 1 ni la condicion 2
Pero para el ejemplo 2 si se cumplen las dos condiciones.


La otra manera de realizarlo es utilizando vba

Public Function CalcularEdad(FecNac As Date) As Integer
Dim cEdad As Integer
If (Month(Now) >= Month(FecNac) And Day(Now) >= Day(FecNac)) Then
    cEdad = Year(Now) - Year(FecNac)
Else
    cEdad = Year(Now) - Year(FecNac) - 1
End If
    CalcularEdad = cEdad
End Function

Escribir en la celda C2 la siguiente formula para utilizar nuestra funcion creada en vba

=CalcularEdad(A2)

Como hemos visto en estos 2 ejemplos, no ha resultado muy complicado realizar el calculo de la edad de una persona. Tambien existen otras maneras de realizarlo.

Como obtener un numero faltante en excel

Amigos exceleros, en esta oportunidad vamos a crear una formula que me permita mostrar un numero faltante de una lista de excel.
Cabe señalar que en la lista solo debería haber un numero faltante para que la formula muestre ese numero. Eso quiere decir que si en la lista faltan 2 o mas números, la formula no funcionara.

Comencemos:
En la columna A tenemos una lista de números correlativos, y podemos ver a simple vista que falta el numero 10.

Para poder analizarlo mejor hemos creado las formulas en varias celdas:

C2: =CONTAR(A2:A6)+1

Con esta formula mostramos la cantidad de filas + 1

D2: =(MAX(A2:A6)+MIN(A2:A6))/2 

Aqui el calculo se realiza sumando el numero maximo con el numero minimo y dividiendolo entre 2

E2: = SUMA(A2:A6)

Se obtiene la suma de los numeros de la columna A

F2: =(C2*D2)-E2

El calculo para obtener el numero faltante se realiza multiplicando los resultados de la celda C2 y D2 y restandole la suma total.

Por ultimo, si deseamos poner toda la formula en una sola celda simplemente tenemos que hacer la formula anidada y quedara de la siguiente manera:

=((CONTAR(A2:A6)+1)*(MAX(A2:A6)+MIN(A2:A6))/2)-SUMA(A2:A6)

De esta manera igual podra mostrar el mismo resultado,

Encontrar la última fila con datos usando Excel VBA

ENCONTRAR LA ÚLTIMA FILA CON DATOS USANDO EXCEL VBA

Cuando estamos trabajando sobre una hoja de cálculo en excel, queremos saber como encontrar la ultima fila con datos, ya que es necesario para alguna macro que estemos realizando.
Para esto voy a mostrar unos ejemplos de como obtenerlo.

Cuando los datos NO son continuos y existe alguna fila vacia
A veces cuando existe una fila vacia es necesario que el cursor se dirija a la ultima fila que en este caso es la fila 9. Estas funciones son aptas para estos casos.

Usando el objeto Range

Sub Contar_ultima_Celda()
    R = Range("A" & Rows.Count).End(xlUp).Row
End Sub

Usando el objeto Cells

Sub Contar_ultima_Celda2()
    R = Cells(Rows.Count, 1).End(xlUp).Row
End Sub

Cuando los datos son continuos y no hay espacios en blanco en una fila
En el caso que no hayan espacios en blanco que pueda cortar el recorrido del cursor fácilmente se puede utilizar las siguientes funciones. También se puede hacer uso del código anterior

 Sub UltimaFila3()
     j = Range("A1").End(xlDown).Row
End Sub

Sub Contar_ultimaFila4()
r = Cells(1, 1).End(xlDown).Row
End Sub

OBTENER LA  ULTIMA POSICION DE COLUMNA CON DATOS

Con ambas funciones se puede saber la posicion de la ultima columna con datos.

Sub UltimaColumna()
      r = Cells(1 & Columns.Column).End(xlToLeft).Column
End Sub

Sub UltimaColumna2()
     r = Cells(1, 1).End(xlToRight).Column
End Sub

Para que puedan ver la posicion de la ultima fila o columna con datos se puede incluir dentro de cada subrutina un mensaje con la variable que almacena la posicion:

MsgBox "Posicion de la columna " & r

 Espero que les haya sido de mucha utilidad, y ala espera de sus comentarios.

Contar registros unicos en excel

CONTAR REGISTROS ÚNICOS EN EXCEL
En algún momento de nuestro trabajo en excel necesitaremos contar la cantidad de registros unicos de una lista, ya sean números o una cadena de texto.Existe una manera practica para contar los registros únicos en excel.

Ejemplo:
A simple vista podemos ver que en la imagen existen 3 elementos únicos, pero en el caso que tuviéramos  mas registros que contar nos resultaría difícil, Por lo tanto aplicaremos una formula matricial la cual luego de escribir la siguiente función en la celda C1.

 =SUMA(1/CONTAR.SI(A1:A6,A1:A6&""))

Debemos presionar la combinación de teclas CTRL+SHIFT+ENTER para que  se active de manera matricial y muestre las llaves automáticamente que indica que esta en modo formula matricial.

Explicación:
Vamos a partir la explicación por pasos:
Lo que hace la formula es contar la cantidad de veces que se repiten los valores de la columna A (Resultado en Columna C)
-Luego dividir el numero 1 entre la cantidad de veces que se repite el valor.
Luego sumar los valores de la columna D, lo cual me da el mismo resultado de la formula anterior.

Entonces todos esos pasos lo estamos haciendo en una sola celda de manera matricial.
Si tuviéramos un texto que se repitiera 5 veces el resultado de 1 entre 5 seria 0.2 y esto tendríamos que sumarlo 5 veces, con lo cual el resultado siempre va a ser uno, por esta razón siempre se cuenta el valor único.

Valores únicos tomando en cuenta los valores vacíos
También cuenta los espacios como un valor, en nuestra formula se puede ver que al final dela funcion contar.si se agrega el valor &"".

Tener en cuenta que cada vez que se haga algún cambio en la formula se debe presionar la combinación de teclas CTRL+SHIFT+ENTER para ponerla en modo matricial

Funcion Sustituir

FUNCION SUSTITUIR

Esta funcion perteneciente a la categoria texto reemplaza el texto existente con un texto nuevo en una cadena.

SINTAXIS

Texto (obligatorio):Texto o referencia a una celda que contiene texto a la cual se desea sustituir los caracteres.
Texto Original (Obligatorio): Texto original que se quiere sustituir.
Texto nuevo (Obligatorio):  Texto nuevo que reemplazara al texto original
Núm_de_ocurrencia (opcional): Es un valor numérico, el cual indica el numero de ocurrencia del texto original, si no se especifica este valor, la función sustituir reemplazara todas las apariciones del texto original.

EJEMPLO

1. En este ejemplo al texto "Bienvenido a mi web" le estamos sustituyendo los espacios por vacios, fijense que en el parametro Núm_de_ocurrencia que es opcional no le estamos poniendo ningun valor por tal motivo la funcion sustituye todos los espacios en blanco que encuentre en el texto.

=SUSTITUIR("Bienvenido a mi web"," ","")

2. Vamos a realizar un ejemplo que use el parametro Núm_de_ocurrencia. das

=SUSTITUIR("Hola mundo en excel"," ","#",2)

Resultado

Hola mundo#en excel

Aquí sustituye el segundo espacio que encuentra por el caracter "#" ya que en Núm_de_ocurrencia estamos ingresando el valor 2.

Esta función suele ser de mucha utilidad a la hora de trabajar con textos y puede anidarse con otras mas funciones para obtener el resultado esperado.

Funcion Extrae en excel

FUNCIÓN EXTRAE
Esta función que pertenece a la categoría de funciones de texto y nos permite obtener uno o mas caracteres de una cadena de texto indicando el inicio y la cantidad de caracteres que se desea obtener.

SINTAXIS:

EXTRAE(texto,posición_inicial,núm_de_caracteres)

Texto (Obligatorio): Es la cadena de texto de la cual se van a obtener los caracteres.

Posición_inicial (Obligatorio): Valor numérico que indica la posición inicial de la cual se obtendrán los caracteres de la cadena de texto. El primer carácter del texto es 1.

Num_de_caracteres (Obligatorio): Valor numérico el cual indica cuantos caracteres se desea obtener de la cadena de texto a partir de la posición inicial.

Ejemplos

1. En este ejemplo se desea obtener de la celda A4 ("Bienvenidos") el texto "ven", para eso escribimos la función =EXTRAE(A4,5,3) la cual explicaremos.

EXTRAE(A4,   Se escribe la función extrae y el parámetro texto que sera la celda A4   

EXTRAE(A4,5, Seguidamente el numero 5, ya que la letra "v" inicia en la 5 posición del texto

EXTRAE(A4,5,3) Luego escribimos el numero 3 que son 3 caracteres a partir de la letra "v" y cerramos paréntesis y enter para terminar con la función. Vemos que nos muestra el resultado "ven"

Funcion Derecha

FUNCIÓN DERECHA
La función derecha pertenece a la categoría de funciones de texto, la cual devuelve un numero de caracteres especificados desde el final de una cadena de texto. Es decir, obtiene una cadena de texto contando los caracteres a partir de la derecha.

SINTAXIS

DERECHA (texto,[núm_de_caracteres])

texto (Obligatorio): Es la cadena de texto que contiene los caracteres que desea obtener, puede ser una cadena especifica que ingrese en la formula o una referencia a una celda.

[núm_de_caracteres](Opcional): Número que indica la cantidad de caracteres a obtener de la cadena especificada, si no se ingresa ningún valor la función toma por defecto el numero 1.

EJEMPLOS

1.  En la columna "A"  tenemos 4 meses del año,de Enero a Abril, quiero obtener en la celda B1 los tres ultimos caracteres del mes de enero, con lo cual escribimos la formula  =DERECHA(A1,3)
y el resultado es "ero", que son los últimos 3 caracteres del texto "enero"

2. En este ejemplo en el parámetro texto se ingresa la cadena "exceltutos" con comillas dobles por ser un texto y el numero de caracteres 5, lo cual me devuelve el resultado "tutos"

Nota:

- Si no se ingresa ningún valor en el parámetro [núm_de_caracteres] la función toma por defecto el        número 1.

- Si en el parámetro [núm_de_caracteres] se ingresa un numero negativo #¡VALOR!

FUNCION IZQUIERDA EN EXCEL

FUNCION IZQUIERDA
La función izquierda pertenece a la categoría de funciones de texto, la cual devuelve un numero de caracteres especificados desde el principio de una cadena de texto.

SINTAXIS

IZQUIERDA(texto,[núm_de_caracteres])

texto (Obligatorio): Es la cadena de texto que contiene los caracteres que desea obtener, puede ser una cadena especifica que ingrese en la formula ó una referencia a una celda.

[núm_de_caracteres](Opcional): Número que indica la cantidad de caracteres a obtener de la cadena especificada, si no se ingresa ningun valor la funcion toma por defecto el numero 1.

EJEMPLOS

1. En la función izquierda el primer carácter es tomado como el numero 1 ya que se encuentra en la primera posición, por lo tanto en este ejemplo tomamos los primeros 5 caracteres de la celda B1

2. Aquí vemos que se obtiene los primeros 3 caracteres de una cadena de texto que se ingresa en la función.

3. En este caso no se ingresa el numero de caracteres a obtener de la cadena de la celda A1, Como este atributo es opcional automáticamente su valor sera 1.

Nota: Si ingresa un número negativo en el parametro [núm_de_caracteres], excel mostrara el siguiente error. #¡VALOR!

Funcion Esblanco excel

FUNCION ESBLANCO
La funcion es blanco pertenece a sla categoria de informacion .
Esta función comprueba el valor de una celda y devuelve VERDADERO si está vacío y FALSO si contiene algún valor.

Ejemplos:

1.En este ejemplo validamos si la celda A1 esta en blanco o no, lo cual muestra el valor falso porque si contiene un valor.

2. Aquí verificamos si el valor de la celda A2 esta en blanco y devuelve verdadero ya que esta celda si se encuentra vacía.

Nota: Si la celda contiene un espacio, la función mostrara VERDADERO ya que un espacio es considerado también un valor.

Funcion Si en excel

La función comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO, esta función la podemos encontrar dentro del grupo de funciones  lógicas.

Sintaxis

SI(prueba_lógica;valor_si_verdadero;valor_si_falso)

prueba_lógica: Se permite ingresar cualquier valor que pueda ser evaluado como falso o verdadero, por ejemplo 15>10 es una expresión logica, Este operador puede utilizar cualquier operador de comparación

valor_si_verdadero: Es el valor que se devuelve  si el argumento prueba_lógica es verdadero.

valor_si_falso: Es el valor que se devuelve  si el argumento prueba_lógica es Falso.

Ejemplos

Ejemplo 1: En este primer ejemplo tenemos en la columna A los valores del sexo de la persona F y M y en la columna B mostraremos la descripcion Femenino para F y  Masculino para "M",

=SI(A2="F","Femenino","Masculino)

Esto fue un ejemplo con el operador de comparacion "=", hay que tomar en cuenta que cuando se ingresa valores en texto, estos deben ir con comillas dobles (").

Ejemplo 2: Este es un ejemplo con una condicion logica llamada EsBlanco, el cual muestra Verdadero si la celda referenciada esta vacia y falso cuando contiene un dato. Para este ejemplo cuando la celda este en blanco se mostrara el texto "Si", en caso contrario "No"

=SI(ESBLANCO(A2),"Si","No")

Ejemplo 3 (El valor de retorno contiene una función y referencia a una celda): Dentro del valor que retorna la funcion si, ya sea verdadero o falso esta puede contener una funcion o varias, asi como tambien la referencia a una celda.

En el ejemplo tenemos en la columna "A" el monto de las ventas hechas por un vendedor, si la venta supera los S/. 1200 este se hace acreedor de una comision que se encuentra en la celda "E1".

=SI(A2>1200,SUMA(A2,$E$1),A2)

Vemos que si se cumple la condición de que los valores de la columna "A" sean mayores a 1200 se efectuara una función de suma entre el valor de la columna A + la comisión de la celda E1, en caso no se cumpla se mostrara solo el valor de la columna A.

Esto ejemplifica que dentro del valor de retorno puede ir una función o una referencia a una celda.

A la hora de realizar condiciones en excel la función Si es muy útil y especifica.