Macros para asignar valores a una celda

Contenidos

1 Asignar un valor a la celda activa

2 Asignar valores a una celda específica

3 Asignar el mismo valor a un conjunto de celdas

4 Asignar valores a una celda en una hoja de trabajo específica

5 Asignar una fórmula a una celda específica

6 Asignar un valor a una hoja y a una celda específica utilizando la propiedad Cells

Asignar un valor a la celda activa

Sub AsignarValores1()

'Asignarle un valor a la celda activa

'

    ActiveCell.Value = "Hola, Mundo"

End Sub

Asignar valores a una celda específica

Sub AsignarValores2()

' Asignar un valor a una celda específica

    Range("B3").Value = 100

End Sub

Asignar el mismo valor a un conjunto de celdas

Sub AsignarValores3()

' Asignar el mismo valor a un conjunto de celdas

    Range("B1:B5").Value = 500

End Sub

Asignar valores a una celda en una hoja de trabajo específica

Sub AsignarValores4()

' Asignar un valor a una celda en una hoja de trabajo específica

    Worksheets("Hoja2").Range("B2").Value = 500

End Sub

Asignar una fórmula a una celda específica

Sub AsignarFormula()

' Asigna una fórmula a una celda específica

    Range("A1").FormulaLocal = Rnd()

End Sub

Asignar un valor a una hoja y a una celda específica utilizando la propiedad Cells

Sub Seleccionar()

' Seleccionar la celda (1, 1)

    Worksheets(1).Cells(1, 1).Value = 100

End Sub

Macros para buscar palabras en un texto

Contenidos 1 Macro para buscar palabras en un rango de celdas dado 2 Macro para buscar y resaltar palabras dentro de un texto 3 Macro para buscar en un texto una lista de palabras Macro para buscar palabras en un rango de celdas dado La macro cambia de color el fondo de la celda que contiene la palabra La macro es sensible a las mayúsculas y minúsculas Puede encontrar palabras completas o parte de una palabra Utiliza el operador LIKE para localizar la palabra Sub BuscarPalabras() Dim Celda As Range Dim palabra As String     palabra = InputBox("Palabra a buscar")     palabra = "*" & palabra & "*"          For Each Celda In Selection         If Celda.Value Like palabra Then             Celda.Interior.ColorIndex = 36         End If              Next Celda End Sub En el siguiente ejemplo, se busca la palabra "José" Resultado de la búsqueda. En el siguiente ejemplo, se busca la palabra "ez" Macro para buscar y resaltar palabras dentro de un texto La macro cambia de color (rojo) la palabra encontrada dentro del texto La macro es sensible a las mayúsculas y minúsculas Puede encontrar palabras completas o parte de una palabra Utiliza la función InStr para localizar la palabra Sub ResaltarPalabras() Dim Celda As Range Dim palabra As String     palabra = InputBox("Palabra a buscar")          For Each Celda In Selection         posicion = InStr(Celda.Value, palabra)                  If posicion > 0 Then             Celda.Characters(posicion, Len(palabra)).Font.Color = vbRed         End If              Next Celda End Sub En el siguiente ejemplo, se buscó la palabra "José" La siguiente macro resalta las diferentes ocurrencias de la palabra en el la misma celda. Sub ResaltarPalabras2() Dim Celda As Range Dim palabra As String Dim posicion As Integer     palabra = InputBox("Palabra a buscar")          For Each Celda In Selection         posicion = InStr(1, Celda.Value, palabra)                  Do Until posicion = 0             Celda.Characters(posicion, Len(palabra)).Font.Color = vbRed             posicion = posicion + 1             posicion = InStr(posicion, Celda.Value, palabra)         Loop              Next Celda End Sub En el ejemplo que sigue se buscó la palabra "amo" Macro para buscar en un texto una lista de palabras La macro trabaja de la siguiente manera: Solicita el rango donde se encuentra la lista de palabras a buscar (una palabra por cada renglón) Solicita el rango de las celdas donde se encuentra el texto donde se buscan las palabras Si encuentra la palabra en el renglón del texto, coloca la palabra encontrada en la columna a la  derecha del renglón.  Limpia la celda de la derecha del texto en el caso de no encontrar la palabra Sub buscarPalabras() Dim palabra As Variant Dim celda As Variant Dim direccionCelda As String Dim rangoPalabras As Range Dim rangoTexto As Range Dim contenidoCelda As String Dim contenidopalabra As String 'Proporcionar el rango de la palabra a buscar Set rangoPalabras = Application.InputBox(Prompt:="Seleccionar el rango de entrada de las palabras a buscar", _         Title:="Rango palabras", Type:=8) 'Proporcionar el rango del texto Set rangoTexto = Application.InputBox(Prompt:="Seleccionar el rango de entrada del texto donde se busca", _         Title:="Rango texto", Type:=8) 'Buscar en cada celda del texto la palabra For Each celda In rangoTexto         'Buscar cáda palabra en la celda activa     For Each palabra In rangoPalabras         contenidoCelda = LCase(celda.Value)      'convertir a minúscula las celdas del texto         contenidopalabra = LCase(palabra.Value)  'convertir a minúscula la palabra a buscar         direccionCelda = celda.Address                 If contenidoCelda Like "*" & contenidopalabra & "*" Then             Range(direccionCelda).Offset(0, 1) = contenidopalabra             Exit For 'salir del ciclo cuando encuentre la palabra         Else            Range(direccionCelda).Offset(0, 1) = ""         End If     Next palabra Next celda End Sub En el siguiente ejemplo, la lista de palabras a buscar se encuentra en la columna D. El rango del texto donde se busca la palabra se encuentra en la columna A.  Si se encuentra la palabra se escribe a la derecha de la celda donde se encontró, columna B.

Macros para contar celdas en blanco o con datos

Contar el número de celdas en blanco en un rango seleccionado (Se usa la propiedad Application.WorksheetFunction.CountBlank(Selection)para  llamar desde Visual Basic funciones Excel) Tabla original de datos Sub ContarCeldasBlanco() 'Cuenta el número de celdas en blanco en un rango dado Dim n As Integer     n = Application.WorksheetFunction.CountBlank(Selection)     MsgBox n & " celdas en blanco" End Sub Tabla mostrando el resultado obtenido con el procedimiento Contar el número de celdas que contienen datos (Cuenta las celdas no vacías en un rango dado) Sub ContarCeldas() 'Cuenta el número de celdas que contienen datos (números o etiquetas) Dim n As Integer     n = Application.WorksheetFunction.CountA(Selection)      MsgBox n & " celdas que contienen datos" End Sub

Macros para contar y sumar condicionalmente usando funciones de Excel

Contenidos

1. 1 Función Countif
2. 2 Función CountIfs
3. 3 Función SumIf
4. 4 Función SumIfs

---

Para los siguientes ejemplos, se han considerado nombrar los siguientes rangos en la tabla anterior:

• Región: A2:A10
• Estado.  B2:B10
• Ventas:  C2:C10

---

Función Countif

• Una condición

• CountIf(rango, criterio)
• Los criterios se pueden expresar como 100, "100", ">100", "Norte", A1
• Los criterios para buscar textos pueden utilizar comodines como el * (asterisco) y  ? (signo de interrogación)

Contar el número de renglones de la columna "Región" = "Norte"

Sub Contar1()

      MsgBox Application.WorksheetFunction.CountIf(Range("Región"), "Norte")

End Sub

Contar el número de renglones de la columna "Estado" que empiecen con la letra "C"

Sub Contar2()

    MsgBox Application.WorksheetFunction.CountIf(Range("Estado"), "C*")

End Sub

Contar el número de renglones de la columna "Ventas " >= 200

Sub Contar3()

    MsgBox Application.WorksheetFunction.CountIf(Range("Ventas"), ">=200")

End Sub

---

Función CountIfs

• Varias condiciones

• CountIfs(rango_1, criterio_1, rango_2, criterio_2)
• Se aceptan hasta 127 pares de rangos y criterios
• Los criterios se pueden expresar como 100, "100", ">100", "Norte", A1
• Los criterios para buscar textos pueden utilizar comodines como el * (asterisco) y  ? (signo de interrogación)

Contar el número renglones que en la columna "Región" = "Norte" y en columna "Ventas" >= 200

Sub Contar4()

    MsgBox Application.WorksheetFunction.CountIfs(Range("Región"), "Norte", Range("Ventas"), ">=200")

End Sub

---

Función SumIf

• Una condición
• SumIf(rango, criterio)
• Los criterios se pueden expresar como 100, "100", ">100", "Norte", A1
• Los criterios para buscar textos pueden utilizar comodines como el * (asterisco) y  ? (signo de interrogación)

Sumar los renglones de la columna "Ventas" >=200

Sub Sumar1()

    MsgBox Application.WorksheetFunction.SumIf(Range("Ventas"), ">=200")

End Sub

---

Función SumIfs

• Varias condiciones
• (rango_de_trabajo, rango_1, criterio_1, rango_2, criterio_2)
• Los criterios se pueden expresar como 100, "100", ">100", "Norte", A1
• Los criterios para buscar textos pueden utilizar comodines como el * (asterisco) y  ? (signo de interrogación)

Sumar los renglones de la columna "Ventas", donde la columna "Región" = "Norte" y la columna "Estado" comience con "C*"

Sub Sumar2()

    MsgBox Application.WorksheetFunction.SumIfs(Range("Ventas"), Range("Región"), "Norte", Range("Estado"), "C*")

End Sub

Sumar la columna "Ventas", donde las "Ventas" sean >= 200 y <= 250

Sub Sumar3()
    MsgBox Application.WorksheetFunction.SumIfs(Range("Ventas"), Range("Ventas"), ">=200", Range("Ventas"), "<=250")
End Sub

Macros para contar objetos de una colección

Contenidos
1 Contar el número de filas en una selección dada
2 Contar el número de columnas de una selección dada
3 Total de filas en el rango utilizado en la hoja activa
4 Total de columnas en el rango utilizado en la hoja activa
5 Primera Fila en el rango utilizado en la hoja activa
6 Total de hojas en el libro de trabajo activo
7 Número de libros abiertos

Contar el número de filas en una selección dada

Sub ContarFilas()
' Contar el número de filas de una selección dada
Dim  Filas  As Double
    Filas = Selection.Rows.Count
    MsgBox Filas
End Sub
Contar el número de columnas de una selección dada

Sub ContarColumnas()
' Contar el número de columnas de una selección dada
Dim Columnas As Double
    Columnas = Selection.Columns.Count
    MsgBox Columnas
End Sub

Total de filas en el rango utilizado en la hoja activa

Sub TotalFilas()
'Total de filas en el rango utilizado en la hoja activa
Dim Filas As Double
    MsgBox ActiveSheet.UsedRange.Rows.Count
End Sub

Total de columnas en el rango utilizado en la hoja activa

Sub TotalColumnas()
'Total de columnas en el rango utilizado en la hoja activa
    MsgBox ActiveSheet.UsedRange.Columns.Count
End Sub

Primera Fila en el rango utilizado en la hoja activa

Sub PrimeraFila()
'Primera fila en el rango utilizado en la hoja activa
    MsgBox ActiveSheet.UsedRange.Row
End Sub

Total de hojas en el libro de trabajo activo

Sub TotalHojas()
'Total de hojas en el libro de trabajo
    MsgBox ActiveWorkbook.Sheets.Count
End Sub

Número de libros abiertos

Sub LibrosAbiertos() 
    MsgBox Workbooks.Count 
End Sub

Trucos de Excel Avanzado

Para revisar mas trucos de macros puede examinar la lista disponible en

http://www.excel-avanzado.com/trucos-de-excel-avanzado


Macros de la clase AQUI

Macro para enviar emails desde Excel

Figura 1. Enviar correo a lista de clientes.

Para esta macro tendremos un texto prefenido que se enviará a cada cliente y el cuerpo del mail cambiará los datos con relación a cada cliente. Los datos que cambiarán son los relativos a las columnas de la tabla.

Esta macro no usará un formulario de Outlook para hacer los envíos, sino que desde el mismo código le definiremos los parámetros del email que se enviarán.

Los parámetros que se usarán son:

1. Asunto (del correo).
2. Correo (el emai del cliente).
3. Destinatario (el nombre del cliente).
4. Saldo.
5. Fecha de vencimiento.

El texto que se enviará lo definimos dentro del código vba y va concatenado con los valores de cada cliente en la tabla. El texto que se enviará es el siguiente:

Apreciable [nombre]

Queremos informarle que su fecha de pago venció el día [fecha].

El saldo que debe liquidar es [saldo].

Atentamente:
Tarjetas de crédito.

Código vba

Para que el código funcione, dentro del IDE de vba deberemos marcar la referencia de Outlook Object Library. Para marcarla elegimos el menú Herramientas > Referencias.

Sub EnviarEmail()
'
' Declaramos variables
'
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim cell As Range
Dim Asunto As String
Dim Correo As String
Dim Destinatario As String
Dim Saldo As String
Dim Msg As String
    '
    Set OutlookApp = New Outlook.Application
    '
    'Recorremos la columna EMAIL
    '
    For Each cell In Range("B11:B23")
        '
        'Asignamos valor a las variables
        '
        Asunto = "Saldo vencido"
        Destinatario = cell.Offset(0, -1).Value
        Correo = cell.Value
        Saldo = Format(cell.Offset(0, 1).Value, "$#,##0")
        FechaVencimiento = Format(cell.Offset(0, 2).Value, "dd/mmm/yyyy")
        '
        'Cuerpo del mensaje
        '
        Msg = "Apreciable " & Destinatario & vbNewLine & vbNewLine
        Msg = Msg & "Queremos informarle que su fecha de pago venció el día "
        Msg = Msg & FechaVencimiento & "." & vbNewLine & vbNewLine
        Msg = Msg & "El saldo que debe liquidar es "
        Msg = Msg & Saldo & vbNewLine & vbNewLine
        Msg = Msg & "Atentamente:" & vbNewLine
        Msg = Msg & "Tarjetas de crédito."
        '
        Set MItem = OutlookApp.CreateItem(olMailItem)
        With MItem
            .To = Correo
            .Subject = Asunto
            .Body = Msg
            .Send
            '
        End With
        '
    Next
    '
End Sub