ESTRUCTURA SELECT CASE PARA REEMPLAZAR CARACTERES EN UNA CADENA DE TEXTO O NÚMEROS

Hola a todos:

Hace unos días respondí a una consulta en la que un lector me preguntaba sobre la forma de incorporar a un proceso un código que pudiese reemplazar determinados caracteres, por ejemplo: la ñ por la n.

La aplicación que estaba desarrollando iniciaba consultas a internet y necesitaba controlar que estos caracteres se reemplazasen por los que él indicaba. Además también necesitaba hacer algunas modificaciones con otros datos en los que debía reemplazar algunos números por otros.

Para realizar esta tarea, opté por hacer uso de una estructura Select Case, dado que me permitía realizar todo lo que me pedía.

Veámoslo con un ejemplo sencillo, tenemos varias informaciones en las cuatro primeras líneas:

ESTRUCTURA SELECT CASE PARA REEMPLAZAR CARACTERES EN UNA CADENA DE TEXTO O NUMERO

Para el primer caso, se necesita cambiar la ñ por una n, en los siguientes casos, si los números son el 1, el 2 o el 3, debemos mostrar un 1, si son el 4, el 5 o el 6 debemos mostrar un 2 y si son el 7, el 8 o el 9, un 3. Y en la última fila varios números combinamos.

Pues bien, para hacer este ejercicio vamos a utilizar la siguiente macro:

Sub EJEMPLO_SELECT_CASE()
'Definimos variables
Dim sDato As String, nItem As String
Dim i As Integer, j As Integer, sLargo As Integer
With Sheets("DATOS")
Final = Application.CountA(.Range("A:A"))
'Iniciamos bucle por cada celda de la columna A
For i = 2 To Final
sLargo = Len(.Cells(i, 1))
'Si hay datos entonces iniciamos segundo bucle
If sLargo > 0 Then
'Extraemos cada carácter de la celda seleccionada
For j = 1 To sLargo
nItem = Mid(.Cells(i, 1), j, 1)
'Iniciamos estructura Select Case
Select Case nItem
Case "Ñ"
nItem = "N"
Case "ñ"
nItem = "n"
Case 0 To 3
nItem = "1"
Case 4 To 6
nItem = "2"
Case 7 To 9
nItem = "3"
End Select
'Agrupamos de nuevo la palabra
sDato = sDato & nItem
Next j
End If
'Pasamos el dato a la columna B
.Cells(i, 2) = sDato
'Igualamos Sdato a 0
sDato = 0
'Seguimos con la próxima palabra/string
Next i
End With
End Sub

Como podéis observar, tenemos varios bucles for – next, uno para recorrer las filas y otro para recorrer la cadena de texto dentro de esa fila.

A continuación aplicamos el Select Case que hemos programado según los parámetros requeridos, cambiar la ñ por la n (también si es mayúscula) y por grupo de números para obtener este resultado:

ESTRUCTURA SELECT CASE PARA REEMPLAZAR CARACTERES EN UNA CADENA DE TEXTO O NUMERO1

Como podéis observar, los cambios se realizan según lo esperado, y podríamos seguir añadiendo condiciones o (casos) a nuestro Select según nuestras necesidades.

Y esto ha sido todo, espero que os sea de utilidad 🙂

Descarga el archivo de ejemplo pulsando en: ESTRUCTURA SELECT CASE PARA REEMPLAZAR CARACTERES EN UNA CADENA DE TEXTO O NÚMERO

 
¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

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

EXTRAER SALDOS NEGATIVOS Y POSITIVOS EN LOS MOVIMIENTOS DE UNA CUENTA

Hola a todos.

Llevo varios post escritos en los que el tema a tratar son las consultas de los lectores. Creo que esto es muy importante, dado que son casos totalmente reales y seguro que se dan más frecuentemente.

Para el caso de hoy voy a presentar un pequeño proceso que extrae los saldos positivos y negativos de los movimientos de una cuenta contable. Esta consulta me la formuló una lectora, que decía así:

Buenos días:

Tengo una relación de cuentas y movimientos por fechas y necesitaría poner los importes negativos en una columna y los positivos en otra. Y también que aparezca el sumatorio de los saldos al final de cada columna, ¿podrías ayudarme?. Te envío un archivo de prueba.

Muchas gracias.

Para hacernos una idea de los datos, he confeccionado un pequeño ejemplo con datos generados aleatoriamente, estos serían los movimientos, las fechas y el resto de la información:

EXTRAER SALDOS NEGATIVOS Y POSITIVOS EN LOS MOVIMIENTOS DE UNA CUENTA.jpg

Para poder realizar la consulta que nos plantea nuestra compañera existen varios métodos, podemos usar un filtro avanzado (pero en VBA), podemos usar una consulta SQL usando ADO, o podemos usar un bucle que vaya acumulando los datos en la columna específica. Para este ejercicio usaré el tercer método, y en concreto una instrucción For-Next (podríamos utilizar cualquier otra estructura de bucle).

La macro que vamos a utilizar es la siguiente:

Sub extraer_saldos()
'Declaramos las variables a utilizar
Dim i As Integer, n As Integer, contador As Integer
'Desactivamos parpadeo de pantalla
Application.ScreenUpdating = False
'Hacemos referencia a la hoja Datos
With Sheets("DATOS")
'Cuando ejectemos la macro, limpiamos el área seleccionada si contiene información
fin = Application.CountA(.Range("A:A"))
If fin > 1 Then .Range("F2:I" & fin, "K2:N" & fin).Clear
'Si no marcamos el mes,lanzamos un mensaje de advertencia y paramos salimos del proceso
nMes = .Cells(2, 16).Value
If nMes = vbNullString Then
MsgBox ("INDICA EL MES (EN NÚMERO) PARA EXTRAER LA INFORMACIÓN"), vbExclamation
Exit Sub
End If
'iniciamos el bucle para pasar los datos con signo negativo a las columnas especificadas
n = 2
For i = 2 To fin
contador = 0
If .Cells(i, 4) < 0 And Month(.Cells(i, 2)) = nMes Then contador = contador + 1
If contador = 1 Then
.Range("F" & n) = .Cells(i, 1).Value
.Range("G" & n) = .Cells(i, 2).Value
.Range("H" & n) = .Cells(i, 3).Value
.Range("I" & n) = .Cells(i, 4).Value
n = n + 1
End If
Next i
'realizamos el sumatorio en la última celda de los importes negativos
RngNeg = Application.CountA(.Range("F:F"))
.Cells(RngNeg + 1, 9) = Application.WorksheetFunction.Sum(.Range("I2:I" & RngNeg))
With .Cells(RngNeg + 1, 9)
.Font.Bold = True
.Style = "Currency"
End With
'iniciamos el bucle para pasar los datos con signo positivo a las columnas especificadas
n = 2
For i = 2 To fin
contador = 0

If .Cells(i, 4) > 0 And Month(.Cells(i, 2)) = nMes Then contador = contador + 1
If contador = 1 Then
.Range("K" & n) = .Cells(i, 1).Value
.Range("L" & n) = .Cells(i, 2).Value
.Range("M" & n) = .Cells(i, 3).Value
.Range("N" & n) = .Cells(i, 4).Value
n = n + 1
End If
Next i
'realizamos el sumatorio en la última celda de los importes negativos
RngNeg = Application.CountA(.Range("K:K"))
.Cells(RngNeg + 1, 14) = Application.WorksheetFunction.Sum(.Range("N2:N" & RngNeg))
With .Cells(RngNeg + 1, 14)
.Font.Bold = True
.Style = "Currency"
End With
End With
Application.ScreenUpdating = True
End Sub

Cuando ejecutamos la macro, el resultado que obtener es el siguiente:

extraer-saldos-negativos-y-positivos-en-los-movimientos-de-una-cuenta1

Como podéis observar, hemos extraído los movimientos positivos y negativos del mes de febrero y realizado el sumatorio en cada columna para obtener el saldo final.

En este caso específico, la lectora solicitaba filtrar bajo el criterio del mes, pero podríamos realizar el filtro en base a la cuenta, el cliente, etc. Es en la macro donde podréis especificar nuevos condicionales que se adapten a vuestras necesidades.

Es un proceso simple y con muchas posibilidades, os animo a ir examinando el código poco a poco y, si es necesario, lo vayáis adaptando a vuestros trabajos.

Descarga el archivo pulsando en: EXTRAER SALDOS NEGATIVOS Y POSITIVOS EN LOS MOVIMIENTOS DE UNA CUENTA