Si, el título puede parecer algo confuso, pero obedece a una consulta que recibí x mail y me planteaba el siguiente caso: un libro con equis cantidad de hojas (pueden ser 10, 5, 20 o 50) en las cuales existen tablas, todas con el mismo formato, como el que se describe a continuación:
lo que Mónica necesita es "juntar" toda esa información en una sola tabla, totalizando los campos "valor1" y "valor2". Recordemos que existen una "n" cantidad de hojas, así que pueden existir muchas tablas como estas.
Aqui el principal problema son los nombres presentes en la columna "acciones". Ella necesita totalizar por nacionalidad, pero esos nombres pueden ser distintos: "argentina, argentina xxx, acciones de argentina, etc, etc". ¿Como hago para totalizar solamente bajo el concepto "argentina", si poseo una serie de nombres ambiguos? La unica ventaja que tengo es que sé que, por lo menos, la palabra "argentina" se encuentra dentro de cada nombre. Obviamente que el inconveniente se repite con todas las nacionalidades. El nombre puede estar solo o bien dentro de una cadena de texto.
Para salir rápido del apuro se me ocurrió una macro. Podría utilizar la herramienta "consolidar" de Excel, pero el tema vendría un poco rebuscado: cada vez que se agregue una pagína debería agregarla al consolidado y, encima, ver como resuelvo el tema de los nombres ambiguos.
Primero, creo la hoja "consolidado", mas o menos con el siguiente aspecto:
esta tabla es para "totalizar" por nacionalidad.
La macro tiene los siguientes pasos:
1) recorrer una por una las hojas del libro
2) tomar la primer nacionalidad de "consolidaddo" y buscarla en la hoja. Si está, sumo los valores y los
envío a la tabla totalizadora (segunda imagen)
3) como se vió en el caso de "argentinas", puede estar mas de una vez, y quizás dentro de otra cadena, asi
que debo repetir la busqueda hasta que no la encuentre mas.
4) mostrar los resultados
Aqui les dejo el código resuelto. En muchos aspectos se puede ir puliendo:
Private Sub cmdTotalizar_Click()
'defino las variables que utilizaré:
Dim Nacion, Hoja, Fila1, UltFila1, X, Time1, Time2, UltFila2, Cont, FilaX
If ActiveSheet.Name <> "Consolidado" Then
MsgBox "Esta macro solo puede ejecutarse desde la hoja 'Consolidar'", vbCritical, "Error"
Exit Sub
End If
'pregunto:
If MsgBox("confirma el inicio del consolidado?", vbYesNo + vbQuestion, "Atención") = vbNo Then
Exit Sub
End If
'tomo el tiempo de inicio:
Time1 = Now
'me fijo cual es la ultima fila ocupada, por si se agregaron mas nacionalidades:
UltFila1 = Cells(Cells.Rows.Count, 1).End(xlUp).Row
'como la ultima fila de la tabla es "totales", le resto 1 al resultado:
UltFila1 = UltFila1 - 1
'borro los datos existentes en la tabla "consolidado"
Range("b2:e" & UltFila1).ClearContents
Cont = 0
'recorro todas las hojas del libro, una x una
For Each Hoja In ActiveWorkbook.Sheets
If Hoja.Name <> "Consolidado" Then
UltFila2 = Sheets(Hoja.Name).Cells(Cells.Rows.Count, 1).End(xlUp).Row
'ahora recorro la tabla, que comienza en A2 (la primer fila es el encabezado):
For X = 2 To UltFila1
'tomo el nombre de la nacionalidad. utilizo a trim() para eliminar posibles espacios en blanco
Nacion = Trim(Cells(X, 1).Value)
'busco en la hoja actual el banco. como esta busqueda es "por partes" (xlpart) me
'detectará si "colombia" está dentro de "colombia corrientes", etc, etc
'si la nación está, en Fila1 guardo en que fila de la hoja se encuentra:
buscardenuevo:
On Error Resume Next
'si Cont=0, es la primer busqueda del Banco
If Cont = 0 Then
Fila1 = Sheets(Hoja.Name).Range("A1:A" & UltFila2).Find(What:=Nacion, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
ElseIf Cont = 1 Then
'si Cont = 1 es la segunda busqueda de la Nacion. Le doy a FilaX el valor de Fila1 + 1, para
'que ahora busque desde una celda mas abajo y no repita la Nacion enterior.
'Luego concateno FilaX en el rango de búsqueda
FilaX = Fila1 + 1
Fila1 = Sheets(Hoja.Name).Range("A" & FilaX & ":A" & UltFila2).Find(What:=Nacion,
'defino las variables que utilizaré:
Dim Nacion, Hoja, Fila1, UltFila1, X, Time1, Time2, UltFila2, Cont, FilaX
If ActiveSheet.Name <> "Consolidado" Then
MsgBox "Esta macro solo puede ejecutarse desde la hoja 'Consolidar'", vbCritical, "Error"
Exit Sub
End If
'pregunto:
If MsgBox("confirma el inicio del consolidado?", vbYesNo + vbQuestion, "Atención") = vbNo Then
Exit Sub
End If
'tomo el tiempo de inicio:
Time1 = Now
'me fijo cual es la ultima fila ocupada, por si se agregaron mas nacionalidades:
UltFila1 = Cells(Cells.Rows.Count, 1).End(xlUp).Row
'como la ultima fila de la tabla es "totales", le resto 1 al resultado:
UltFila1 = UltFila1 - 1
'borro los datos existentes en la tabla "consolidado"
Range("b2:e" & UltFila1).ClearContents
Cont = 0
'recorro todas las hojas del libro, una x una
For Each Hoja In ActiveWorkbook.Sheets
If Hoja.Name <> "Consolidado" Then
UltFila2 = Sheets(Hoja.Name).Cells(Cells.Rows.Count, 1).End(xlUp).Row
'ahora recorro la tabla, que comienza en A2 (la primer fila es el encabezado):
For X = 2 To UltFila1
'tomo el nombre de la nacionalidad. utilizo a trim() para eliminar posibles espacios en blanco
Nacion = Trim(Cells(X, 1).Value)
'busco en la hoja actual el banco. como esta busqueda es "por partes" (xlpart) me
'detectará si "colombia" está dentro de "colombia corrientes", etc, etc
'si la nación está, en Fila1 guardo en que fila de la hoja se encuentra:
buscardenuevo:
On Error Resume Next
'si Cont=0, es la primer busqueda del Banco
If Cont = 0 Then
Fila1 = Sheets(Hoja.Name).Range("A1:A" & UltFila2).Find(What:=Nacion, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
ElseIf Cont = 1 Then
'si Cont = 1 es la segunda busqueda de la Nacion. Le doy a FilaX el valor de Fila1 + 1, para
'que ahora busque desde una celda mas abajo y no repita la Nacion enterior.
'Luego concateno FilaX en el rango de búsqueda
FilaX = Fila1 + 1
Fila1 = Sheets(Hoja.Name).Range("A" & FilaX & ":A" & UltFila2).Find(What:=Nacion,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
End If
'si se produce un error, la nacion no está en la tabla de la hoja
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
End If
'si se produce un error, la nacion no está en la tabla de la hoja
'entonces "limpio" el error y continúo, dejando a Cont en 0.
If Err.Number = 91 Then
Err.Clear
Cont = 0
Else
'si no hay error, el banco está, encontes sumo. tengo en Fila1 en que fila
'de la tabla está y sé que el "valor1" se encuentra en la segunda columna:
Cells(X, 2).Value = Cells(X, 2).Value + Sheets(Hoja.Name).Cells(Fila1, 2).Value
'y valor2 en la cuarta
If Err.Number = 91 Then
Err.Clear
Cont = 0
Else
'si no hay error, el banco está, encontes sumo. tengo en Fila1 en que fila
'de la tabla está y sé que el "valor1" se encuentra en la segunda columna:
Cells(X, 2).Value = Cells(X, 2).Value + Sheets(Hoja.Name).Cells(Fila1, 2).Value
'y valor2 en la cuarta
Cells(X, 2).Value = Cells(X, 2).Value + Sheets(Hoja.Name).Cells(Fila1, 4).Value
'activo la variable "bandera":
Cont = 1
'y me voy a buscar de nuevo, ya que la Nacion puede estar mas de una vez
GoTo buscardenuevo
End If
Next X
End If
Next Hoja
'destruyo el objeto:
Set Hoja = Nothing
'tiempo de finalización
Time2 = Now
'muestro cuantos segundos me demandó la tarea:
MsgBox "Proceso finalizado en " & Format((Time2 - Time1), "ss") & " segundo/s", vbInformation, "aviso"
End
End Sub
'activo la variable "bandera":
Cont = 1
'y me voy a buscar de nuevo, ya que la Nacion puede estar mas de una vez
GoTo buscardenuevo
End If
Next X
End If
Next Hoja
'destruyo el objeto:
Set Hoja = Nothing
'tiempo de finalización
Time2 = Now
'muestro cuantos segundos me demandó la tarea:
MsgBox "Proceso finalizado en " & Format((Time2 - Time1), "ss") & " segundo/s", vbInformation, "aviso"
End
End Sub
Con For Each.... recorro las hojas del libro. Luego utilizo un bucle For..Next para ir tomando las nacionalidades de la tabla "consolidada". Una vez que tengo el nombre, lo busco en la hoja con la herramienta "buscar" de Excel, con el argumento xlPart, así busca la nacionalidad dentro de cualquier cadena de texto.
Verán que utilizo un GoTo para volver a buscar sin salir del bucle, ya que cada Nacion puede estar mas de una vez, lo que me obliga a ir cambiando el rango de búsqueda.... si no Excel me devolvería siempre la primera.
Resultaría por demás de extraño que alguien se encuentre exactamente con el mismo caso, pero seguro existirán muchos similares. Adaptando pequeñas lineas de código se puede implementar en cualquier proyecto de similares características.
Comentarios
Publicar un comentario