EJECUTAR UN BUCLE EN UN RANGO DE FILAS FILTRADAS

Hola a todos!.

Hace unos días, un lector me trasladó la pregunta de cómo podría ejecutar un bucle (loop) en un rango de filas filtradas. Esto se puede realizar de diferentes formas, pero para este post quiero aprovechar y realizar un ejercicio completo con un caso práctico.

Imaginad que trabajáis de un teatro y se ha decido que para una función concreta, la asignación de sitios en las diferentes zonas del teatro se hará de una manera determinada:

Nos han enviado un listado (hoja LISTA) en el que aparecen los siguientes campos ID, FECHA, HORA, NOMBRE y LUGAR (zona del teatro), esta es la lista:

EJECUTAR UN BUCLE EN UN RANGO DE FILAS FILTRADAS

Pues bien, lo que se pretende es que asignemos un 50% de la cantidad total del número de solicitudes a cada zona y teniendo en cuenta la fecha más antigua y la hora más antigua de cada petición. Es decir que si el número total de personas que han solicitado un lugar en la Zona Premium es de 122 se marcarán los 61 primeros registros teniendo en cuenta la fecha más antigua y la hora más antigua como criterio.

Para hacer este proceso es necesario realizar varios filtros y posteriormente marcar a los clientes beneficiados. Obviamente, se puede hacer manualmente, pero … ¡el tiempo es oro! y no es cuestión de estar varias horas haciendo este proceso, o lo que es peor, imaginad que a partir de ahora siempre se hará así!.

Por lo tanto, vamos a proponer un sencillo código que nos va a ayudar a realizar esto en un segundo 🙂

Antes de ver la macro, debemos añadir otra hoja en la que vamos a indicar el nombre de todas las zonas del teatro:

EJECUTAR UN BUCLE EN UN RANGO DE FILAS FILTRADAS2

Y ahora, en un módulo estándar pegáis esta macro:

Sub BUCLE_EN_CELDAS_FILTRADAS()
'Definimos variables
Dim Fin As Integer, Final As Integer, nCasos As Integer
Dim nRango As Range, Contador As Integer, n As Integer, Dato As Range
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Limpiamos contenidos en el campo MARCA
With Sheets("LISTA")
Fin = Application.CountA(.Range("A:A"))
Final = Application.CountA(Sheets("ZONAS").Range("A:A"))
If Fin > 1 Then .Range("F2:F" & Fin).ClearContents
'Iniciamos bucle filtrando según ZONA y aplicando el 50 al total de personas
'con esta cifra marcaremos con otro bucle los n casos iniciales

For i = 2 To Final
'Pero antes, ordenamos por fecha, de más antigua a más reciente
nCasos = Round(Application.WorksheetFunction.CountIf(.Range("E2:E" & Fin), Sheets("ZONAS").Cells(i, 1)) * 0.5, 0)
.Range("A1:E" & Fin).AutoFilter Field:=5, Criteria1:=Sheets("ZONAS").Cells(i, 1)
With ActiveWorkbook.Worksheets("LISTA").AutoFilter.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("B1:B" & Fin), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.Apply
End With
'Ordenamos hora, de más antigua a más reciente
With ActiveWorkbook.Worksheets("LISTA").AutoFilter.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("C1:C" & Fin), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.Apply
End With
'Pasamos un segundo bucle para marcar con una X el 50% de los casos seleccionados
Set nRango = .Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
For Each Dato In nRango.Rows
n = 1
Contador = Contador + n
If Contador <= nCasos Then
Dato.Cells(1, 6).Value = "X"
Else
Exit For
End If
Next
Contador = 0
Selection.AutoFilter
Next
End With
'Ordenamos por ZONA y mostramos la información
Range("A1").Select
Selection.AutoFilter
With ActiveWorkbook.Worksheets("LISTA").AutoFilter.Sort
.SortFields.Clear
.SortFields.Add Key:=Range("E1:E" & Fin), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers
.Header = xlYes
.Apply
Selection.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

Como podéis observar, realizamos dos tipo de bucle, un for-next y un for each – next. Pero es este último (for each) en el que realmente pasamos un bucle sobre las celdas que hemos filtrado previamente según los parámetros establecidos en los datos del primer bucle.

Pulsamos el botón y tenemos este resultado:

EJECUTAR UN BUCLE EN UN RANGO DE FILAS FILTRADAS3

El código se encarga de filtrar, marcar y presentar los datos ordenador por zonas. En solo un segundo (para 800 registros).

Creo que es un ejercicio muy interesante y que podéis aplicar a multitud de tareas y proyectos. 🙂

Como siempre os dejo el archivo de descarga:

Descarga el archivo de ejemplo pulsando en: EJECUTAR UN BUCLE EN UN RANGO DE FILAS FILTRADAS

Anuncios

GENERAR Y EXTRAER NÚMEROS PRIMOS EN EXCEL CON VBA

Parece que los números primos están de moda, llevo un tiempo leyendo varios artículos relacionados con esta materia y realmente sus aplicaciones son muy interesantes.

Antes de nada, que creo que es importante, vamos a definir qué es un número primo: se denominan números primos aquellos números naturales que únicamente pueden ser divididos ya sea por 1 o por sí mismos.

Una vez que tenemos claro este concepto, podemos empezar a pensar en cómo nos las podemos idear para generar o para detectar números primos. Sin duda, para realizar estas tareas vamos a tener que usar algún tipo de loop o bucle que determine cifra a cifra si se trata de un número primo y en caso de serlo, anotarlo o marcarlo.

Pero el bucle necesita una herramienta que le indique si el número a evaluar es o no primo, para ello debemos utilizar la siguiente fórmula:

a - (b * Fix(a / b))

Esta fórmula realiza lo siguiente: Divide dos números y devuelve el resto o residuo de la división. Es decir, que en caso de ser número primo el número que vayamos a evaluar, debe ser 0 en dos ocasiones (en nuestro bucle), una cuando se divide entre sí mismo.

Esta fórmula se puede sustituir por el operador “MOD” en vba y funcionaría de la misma forma o por esta otra fórmula (parecida a la primera) :

a - (b * (a \ b))

En todos los casos actúa de la misma forma y sería solo cuestión de gustos o estilo el utilizar una u otra. Si os interesa os dejo el enlace donde podréis profundizar:

https://msdn.microsoft.com/es-es/library/se0w9esz.aspx

Nosotros vamos a utilizar la primera fórmula. Imaginad que nos han pedido las siguientes macros:

– Una macro que genere números primos (desean obtener los números primos entre el 1 y el 200).
– Una macro que sea capaz de extraer números primos de un listado que nos han entregado.
– Una macro que sea capaz de marcar en el listado que nos han entregado (verde) cada uno de los números primos.

Bien, (parece que el buzón de peticiones se ha cerrado por unas horas… : ) Veamos entonces la primera macro:

Sub GENERAR_NPRIM()
Dim n As Long
Dim j As Long
Dim i As Long
Dim Contador As Long
With Sheets(1)
'si exite un listado anterior lo eliminamos
fin = Application.CountA(.Range("a:a"))
If fin > 1 Then .Range("a2:a" & fin).Clear
'empezamos a generar en la celda 2
n = 2
'creamos un bucle desde 1 a límite que hayamos elegido
For i = 1 To .Cells(2, 2)
'Insertamos contador a cero
Contador = 0
'creamos un segundo bucle para comprobar si el número es o no primo
'si es igual a cero y el contador acumula 2 ceros únicamente, es primo
For j = 1 To .Cells(2, 2)
'para verificar si es primo utilizamos esta fórmula (podemos usar la función mod)
'que mostrará el resto o el residuo fruto de la división de i entre j, cumpliendo la condición
'si el resultado es cero.
primus = i - (j * Fix(i / j))
If primus = 0 Then Contador = Contador + 1
Next j
'una vez que sepamos es número primo, lo añadimos a la columna "A"
'y así con el siguiente
If Contador = 2 Then
.Range("A" & n) = i
n = n + 1
End If
Next i
End With
End Sub

Con esta macro generamos los números primos que se encuentren entre el 1 y el número que indiquemos en nuestra hoja Excel. En el ejemplo que pongo, he elegido 200:

GENERAR Y EXTRAER NUMEROS PRIMOS EN EXCEL CON VBA

Tan solo debemos indicar la cifra y pulsar en Generar, en ese momento la columna A se irá rellenando con los números primos.

Ahora vamos a por la segunda macro, una macro que nos permita extraer de una matriz numérica aquellos números que sean primos:

GENERAR Y EXTRAER NUMEROS PRIMOS EN EXCEL CON VBA2

** Es importante indicar que los números deben ser consecutivos e introducirse en orden por filas (no por columnas).

La macro que vamos a utilizar esta:

Sub EXTRAER_NPRIM()
Dim i As Range
Dim j As Range
Dim Contador As Long
Dim n As Long
With Sheets(1)
'si exite un listado anterior lo eliminamos
fin = Application.CountA(.Range("D:D"))
If fin > 1 Then .Range("D2:D" & fin).Clear
'incluimos un control error en caso de ejecutar la macro sin seleccionar datos
On Error GoTo Control
'área será el rango que vayamos a seleccionar
Set area = Application.Intersect(Selection, .UsedRange)
'empezamos a generar en la celda 2
n = 2
'iniciamos un bucle que recorra cada uno de las celdas en "área"
For Each i In area
'Insertamos contador a cero
Contador = 0
'iniciamos un segundo bucle comprando cada uno de los números de nuestra selección con el resto
'verificando así si se trata de un número primo
For Each j In area
primus = i - (j * Fix(i / j))
If primus = 0 Then Contador = Contador + 1
Next j
'una vez que sepamos es número primo, lo añadimos a la columna "A"
'y así con el siguiente
If Contador = 2 Then
.Range("D" & n) = i
n = n + 1
End If
Next i
Control: If Err.Number = "6" Or Err.Number = "13" Then
MsgBox ("EL RANGO SELECCIONADO NO CONTIENE DATOS"), vbExclamation, "EXTRAER NÚMEROS PRIMOS"
End If
End With
End Sub

A diferencia de la primera macro, en este caso utilizamos un bucle for each, dado que lo que vamos a evaluar son las celdas de un rango definido (área).

Para ejecutar la macro, es necesario seleccionar todo el rango de datos y luego pulsar en el botón: “Extraer números primos”. En ese momento en la columna “D” se irán colocando los números de la matriz que son primos. Dado que la matriz va desde el 1 al 200 los datos deben ser idénticos a los que hemos generado en la primera macro. Este es el resultado:

GENERAR Y EXTRAER NUMEROS PRIMOS EN EXCEL CON VBA3

Efectivamente, los datos de la columna “D” son idénticos a los de la columna “A”, verificando doblemente los cálculos.

Por último, esta otra macro marcará de color verde en la matriz los números que haya detectado como primos:

Sub MARCAR_NPRIM()
Dim i As Range
Dim j As Range
Dim Contador As Long
With Sheets(1)
'incluimos un control error en caso de ejecutar la macro sin seleccionar datos
On Error GoTo Control
'área será el rango que vayamos a seleccionar
Set area = Application.Intersect(Selection, .UsedRange)
'si exiten celdas marcadas anteriormente, eliminamos color
With Selection.Interior
.Pattern = xlNone
End With
'iniciamos un bucle que recorra cada uno de las celdas en "área"
For Each i In area
'Insertamos contador a cero
Contador = 0
'iniciamos un segundo bucle comprando cada uno de los números de nuestra selección con el resto
'verificando así si se trata de un número primo
For Each j In area
primus = i - (j * Fix(i / j))
If primus = 0 Then Contador = Contador + 1
Next j
If Contador = 2 Then
'en el momento que sabemos que el número es primo, marcamos en verde la celda
area(i).Interior.Color = vbGreen
End If
Next i
Control: If Err.Number = "6" Or Err.Number = "13" Then
MsgBox ("EL RANGO SELECCIONADO NO CONTIENE DATOS"), vbExclamation, "MARCAR NÚMEROS PRIMOS"
End If
End With
End Sub

Este caso no deja de ser una variante de la macro anterior, solo que ahora no extraemos datos, solo debemos marcar. Este es el resultado:

Importante, en esta macro, debemos seleccionar todo el rango de datos, desde el 1 al 200, en caso contrario, no marcará correctamente los números.

GENERAR Y EXTRAER NUMEROS PRIMOS EN EXCEL CON VBA4

y con esta macro finalizamos los trabajos que nos habían pedido sobre números primos.

Aunque en las dos últimas macros debemos seleccionar el rango de datos para extraer y marcar los números primos, si seleccionamos un rango sin datos el control de error nos avisará.

Debemos recordar que estamos trabajando con Excel y con VBA, y aunque son herramientas muy potentes, tienen limitaciones, en este caso las propias variables que hemos declarado y por otra parte las limitaciones de la hoja.

Aunque el archivo es .xls lo podéis guardar como .xlsm y tendréis todos las mejoras de 2007 en adelante.

De todas formas intentar generar los números primos entre 1 y 200.000 puede tardar varias horas. Y eso es todo, me ha parecido un ejercicio muy interesante y creo que bastante útil en algunas áreas profesionales.

PD: el 1 no se considera número primo, por ello no he programado las macros para que lo detecten.

Descarga el archivo de ejemplo pulsando en: GENERAR Y EXTRAER NÚMEROS PRIMOS EN EXCEL CON VBA