Archive

Posts Tagged ‘For each’

Rellenar celdas en blanco en Excel

Todos nos hemos encontrado alguna vez con archivos que contienen celdas en blanco y se nos vuelve necesario ingresarles un valor para no tener celdas vacías o simplemente para completar datos faltantes.

Método tradicional

El primer método implica usar las funciones que ofrece Excel para elegir las celdas en blanco y después ingresarle un valor.

La manera de elegir las celdas en blanco es:

Inicio > Buscar y seleccionar > Ir a…

Aquí Excel elegirá sólo las celdas que no tengan datos y así podremos insertarle un valor. Una vez elegidas las celdas, ingresamos el valor deseados y posteriormente pulsamos las teclas Ctrl + Enter para que sean copiadas a todas.

image

Excel nos permité elegir celdas con determinado criterio.

Método con macros vba

El segundo método sigue la misma lógica que el primero, pero sacándole provecho a las macros y añadiendo esta funcionalidad a otras aplicaciones que pretendamos realizar. El ejemplo nos pedirá un valor a ingresar.

Código de la macro

Sub RellenarCeldas()
Dim Celdas As Range
Dim Valor As Variant
Valor = InputBox("Ingresa el valor", "EXCELeINFO")
Selection.SpecialCells(xlCellTypeBlanks).Select
For Each Celda In Selection
    Celda.Value = Valor
Next Celda
End Sub

image

Excel solicitará el valor a insertar.

Uso de For each … Next en vba Excel

El constructor For each … Next nos permite la manipulación de objetos dentro de una colección. Una colección es el conjunto de elementos de un mismo tipo, por ejemplo, colección de celdas, de hojas, de libros, de ventanas, objetos gráficos, etc.

A diferencia del constructor For … Next, que nos permite elegir el inicio y fin de una numeración, la cual se puede asignar para varios motivos, For  each … Next incluye todos los objetos de las colecciones de Office.

Expongo 3 macros sencillas que nos permitirán conocer el uso del constructor, lo demás será cuestión de aplicar los métodos o procedimientos de cada objeto en cuestión.

Colección de celdas

Aplicable en la manipulación de celda contenidas en un rango elegido, por ejemplo: cambiar color, tamaño, insertar comentario, aplicar una función, etc. El siguiente ejemplo nos arroja un objeto MsgBox donde nos dice la ruta de cada celda elegidas, así como su contenido:

Sub CadaCelda()
'Se declara la variable Celda como rango
Dim Celda As Range
For Each Celda In Selection
    MsgBox "La celda " & Celda.Address & " Tiene el contenido " & Celda.Value, vbInformation, "EXCELeINFO"
Next Celda
End Sub

Colección de hojas

Las acciones que se pueden realizar con las hojas, entre otras, son: eliminar, cambiar nombre, color de etiqueta, ocultar, mostrar, mover. El ejemplo muestra mediante un MsgBox él número correspondiente al índice de cada hoja, así como su nombre:

Sub CadaHoja()
'Se declara la variable Hoja como objecto
Dim Hoja As Object
For Each Hoja In ThisWorkbook.Sheets
    MsgBox "La hoja " & Hoja.Index & " Se llama " & Hoja.Name, vbInformation, "EXCELeINFO"
Next Hoja
End Sub

Colección de archivos

También se podrá utilizar la colección Windows, para poder manipular ventanas, valga la redundancia. Con los libros se podrá guardar, cerrar, imprimir, etc. La macro siguiente nos muestra el nombre de cada archivo abierto. Nótese que también nos muesta el nombre de los archivos ocultos:

Sub CadaLibro()
'Se declara la variable Libro como objecto
Dim Libro As Object
For Each Libro In Application.Workbooks
    MsgBox "El libro tiene el nombre " & Libro.Name, vbInformation, "EXCELeINFO"
Next Libro
End Sub

Como nos hemos dado cuenta el constructor tiene una estructura sencilla y digerible, lo demás será cuestión de conocer que más podemos hacer con todos los objetos de Excel, instruirnos y echar a volar nuestra curiosidad.

Categories: Excel, vba Tags: , , ,

Macro para convertir serie en fecha en Excel

April 13, 2011 6 comments

Twittear este post Compartir en Facebook

En base a una consulta en los foros de TODOEXCEL donde solicitan que una serie de números, por ejemplo “140382” se convirtiera a formato de fecha “14-mar-11”, aprovecho para compartir la macro que realiza tal función.

Código:

'---------------------------------------------------------------------------------------
' Procedure : EXCELeINFOConvertirSerieEnFecha
' Author    : Sergio A Campos H
' Date      : 13/04/2011
' Purpose   : Convertir serie en fecha
'---------------------------------------------------------------------------------------
'
Sub EXCELeINFOConvertirSerieEnFecha()
    Dim Celda, Dia, Mes, Anio, FechaCompleta, Cuenta
    Application.Calculation = xlCalculationManual
    Application.StatusBar = "Convirtiendo ..."
    Cuenta = 0
    For Each Celda In Selection
        If Len(Celda.Value) = 5 Then
            Fecha = "0" & Celda.Value
        Else
            Fecha = Celda.Value
            If Len(Celda.Value) < 5 Then
                Cuenta = Cuenta + 1
            End If
        End If
        Anio = Right(Fecha, 2)
        Mes = Mid(Fecha, 3, 2)
        Dia = Left(Fecha, 2)
        Unidos = Mes & "-" & Dia & "-" & Anio
        FechaCompleta = WorksheetFunction.Text(Unidos, "dd-mmm-yyy")
        Celda.Value = FechaCompleta
    Next Celda
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
    MsgBox Cuenta & " celdas con longitud menor a 5", vbInformation
End Sub