Usar Filtros Avanzados desde controles en un UserForm para obtener determinados registros en una hoja y trasladarlos a un control ListBox en el mismo UserForm

22/09/2008 - 22:49 por Contaduria | Informe spam
Hola Gente del Foro:

Como estan? Espero que bien. Tengo el siguiente problema para resolver.
Supongamos que en un libro exista una hoja llamada "Datos", que tenga la
siguiente tabla:

Nº Op. Centro de Costo Cuenta Importe
1 Administración Mantenimiento $ 1.000
2 Administración Mantenimiento $ 2.000
3 Depósito Fletes $ 1.000
4 Sucursal Fletes $ 1.000
5 Administración Impuestos $ 1.500
6 Depósito Fletes $ 3.000
7 Sucursal Fletes $ 1.000
8 Depósito Fletes $ 1.500
9 Depósito Mantenimiento $ 2.000

Supongamos también que tengo un UserForm en el cual tengo dos controles del
tipo ComboBox:
1) El ComboBox 1 tiene los items correspondientes a los Centros de Costo, en
el ejemplo: Administración, Depósito, Sucursal y Todos.
2) El ComboBox 2 tiene los items correspondientes a las Cuentas, en el
ejemplo: Mantenimiento, Fletes, Impuestos y Todos.

En el UserForm también existe un control del tipo ListBox. Lo que yo
necesito es que al seleccionar un item del Combo 1 y un item del Combo 2 se
carguen todos los registros que cumplan las condiciones al ListBox y además
el código agregue una línea más al final con la suma de todos los importes.

Ejemplo 1:
Selecciono "Administración" en el ComboBox 1
Selecciono "Mantenimiento" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
1 Administración Mantenimiento $ 1.000
2 Administración Mantenimiento $ 2.000
Total $
3.000

Ejemplo 2:
Selecciono "Depósito" en el ComboBox 1
Selecciono "Fletes" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
3 Depósito Fletes $ 1.000
6 Depósito Fletes $ 3.000
8 Depósito Fletes $ 1.500
Total $
5.500

Ejemplo 3:
Selecciono "Todos" en el ComboBox 1
Selecciono "Fletes" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
3 Depósito Fletes $ 1.000
4 Sucursal Fletes $ 1.000
6 Depósito Fletes $ 3.000
7 Sucursal Fletes $ 1.000
8 Depósito Fletes $ 1.500
Total $
7.500

Ejemplo 4:
Selecciono "Administración" en el ComboBox 1
Selecciono "Todos" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
1 Administración Mantenimiento $ 1.000
2 Administración Mantenimiento $ 2.000
5 Administración Impuestos $ 1.500
Total $
4.500

Sospecho que debe poder hacerse con los filtros avanzados (AdvancedFilter),
pero no se como manejarlos. La unica manera que intenté para resolverlo es
haciendo varios bucles del tipo For - Next en donde el código va comparando
celda por celda si cumple o no con las condiciones, pero la verdad que
funciona muy lento y queda un código excesivamente complejo. Estoy seguro
que con el AdvancedFilter se debe poder hacer mejor. Ayuda por favor!
Espero haya sido claro en mi exposición. Cualquier consulta estoy a su
disposición.
Saludos y desde ya muchas gracias.

Mariano

Preguntas similare

Leer las respuestas

#1 alejo
23/09/2008 - 03:47 | Informe spam
On 22 sep, 15:49, "Contaduria" wrote:
Hola Gente del Foro:

Como estan? Espero que bien. Tengo el siguiente problema para resolver.
Supongamos que en un libro exista una hoja llamada "Datos", que tenga la
siguiente tabla:

Nº Op.    Centro de Costo         Cuenta                Importe
1              Administración            Mantenimiento    $ 1.000
2              Administración            Mantenimiento    $ 2.000
3              Depósito                     Fletes                  $ 1.000
4              Sucursal                      Fletes                  $ 1.000
5              Administración            Impuestos           $ 1.500
6              Depósito                     Fletes                  $ 3.000
7              Sucursal                      Fletes                  $ 1.000
8              Depósito                     Fletes                  $ 1.500
9              Depósito                     Mantenimiento    $ 2.000

Supongamos también que tengo un UserForm en el cual tengo dos controles del
tipo ComboBox:
1) El ComboBox 1 tiene los items correspondientes a los Centros de Costo, en
el ejemplo: Administración, Depósito,  Sucursal y Todos.
2) El ComboBox 2 tiene los items correspondientes a las Cuentas, en el
ejemplo: Mantenimiento, Fletes, Impuestos y Todos.

En el UserForm también existe un control del tipo ListBox. Lo que yo
necesito es que al seleccionar un item del Combo 1 y un item del Combo 2 se
carguen todos los registros que cumplan las condiciones al ListBox y además
el código agregue una línea más al final con la suma de todos los importes.

Ejemplo 1:
Selecciono "Administración" en el ComboBox 1
Selecciono "Mantenimiento" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op.    Centro de Costo         Cuenta                Importe
1             Administración            Mantenimiento    $ 1.000
2             Administración            Mantenimiento    $ 2.000
Total                                                                    $
3.000

Ejemplo 2:
Selecciono "Depósito" en el ComboBox 1
Selecciono "Fletes" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op.    Centro de Costo         Cuenta                Importe
3              Depósito                     Fletes                  $ 1.000
6              Depósito                     Fletes                  $ 3.000
8              Depósito                     Fletes                  $ 1.500
Total                                                                      $
5.500

Ejemplo 3:
Selecciono "Todos" en el ComboBox 1
Selecciono "Fletes" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op.    Centro de Costo         Cuenta                Importe
3              Depósito                     Fletes                  $ 1.000
4              Sucursal                      Fletes                  $ 1.000
6              Depósito                     Fletes                  $ 3.000
7              Sucursal                      Fletes                  $ 1.000
8              Depósito                     Fletes                  $ 1.500
Total                                                                      $
7.500

Ejemplo 4:
Selecciono "Administración" en el ComboBox 1
Selecciono "Todos" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op.    Centro de Costo         Cuenta                Importe
1             Administración            Mantenimiento    $ 1.000
2             Administración            Mantenimiento    $ 2.000
5             Administración            Impuestos           $ 1.500
Total                                                                    $
4.500

Sospecho que debe poder hacerse con los filtros avanzados (AdvancedFilter),
pero no se como manejarlos. La unica manera que intenté para resolverlo es
haciendo varios bucles del tipo For - Next en donde el código va comparando
celda por celda si cumple o no con las condiciones, pero la  verdad que
funciona muy lento y queda un código excesivamente complejo. Estoy seguro
que con el AdvancedFilter se debe poder hacer mejor. Ayuda por favor!
Espero haya sido claro en mi exposición. Cualquier consulta estoy a su
disposición.
Saludos y desde ya muchas gracias.

Mariano



Amigo Mariano,

Yo no soy un duro en programacion de Macros o Exce VBa, pero me doy
cuenta que eso lo puedes hacer simplemente con una tabla
dinámica . ..

Cual es el problema ? A veces no hay que complicarse tanto para llegar
a un fin. La tabla dinamica te suma en la parte de abajo según el
criterio, además te da la opcion de incluso filtrar datos por los dos
criterios que quieres utilizar .. .
Respuesta Responder a este mensaje
#2 Contaduria
23/09/2008 - 20:36 | Informe spam
Alejo:

Gracias por la ayuda. Pero en una tabla dinámica no me sirve, porque
necesito que la información sea configurada y mostrada desde controles en un
UserForm, ya que esto es parte de un sistema que en realidad es más grande y
tiene otras cosas.
Saludos.

Mariano

Insumos
Mar del Plata
(0223) 475-5511
Open Sports... elegí deporte!
www.opensports.com.ar



"alejo" escribió en el mensaje
news:
On 22 sep, 15:49, "Contaduria" wrote:
Hola Gente del Foro:

Como estan? Espero que bien. Tengo el siguiente problema para resolver.
Supongamos que en un libro exista una hoja llamada "Datos", que tenga la
siguiente tabla:

Nº Op. Centro de Costo Cuenta Importe
1 Administración Mantenimiento $ 1.000
2 Administración Mantenimiento $ 2.000
3 Depósito Fletes $ 1.000
4 Sucursal Fletes $ 1.000
5 Administración Impuestos $ 1.500
6 Depósito Fletes $ 3.000
7 Sucursal Fletes $ 1.000
8 Depósito Fletes $ 1.500
9 Depósito Mantenimiento $ 2.000

Supongamos también que tengo un UserForm en el cual tengo dos controles


del
tipo ComboBox:
1) El ComboBox 1 tiene los items correspondientes a los Centros de Costo,


en
el ejemplo: Administración, Depósito, Sucursal y Todos.
2) El ComboBox 2 tiene los items correspondientes a las Cuentas, en el
ejemplo: Mantenimiento, Fletes, Impuestos y Todos.

En el UserForm también existe un control del tipo ListBox. Lo que yo
necesito es que al seleccionar un item del Combo 1 y un item del Combo 2


se
carguen todos los registros que cumplan las condiciones al ListBox y


además
el código agregue una línea más al final con la suma de todos los


importes.

Ejemplo 1:
Selecciono "Administración" en el ComboBox 1
Selecciono "Mantenimiento" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
1 Administración Mantenimiento $ 1.000
2 Administración Mantenimiento $ 2.000
Total $
3.000

Ejemplo 2:
Selecciono "Depósito" en el ComboBox 1
Selecciono "Fletes" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
3 Depósito Fletes $ 1.000
6 Depósito Fletes $ 3.000
8 Depósito Fletes $ 1.500
Total $
5.500

Ejemplo 3:
Selecciono "Todos" en el ComboBox 1
Selecciono "Fletes" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
3 Depósito Fletes $ 1.000
4 Sucursal Fletes $ 1.000
6 Depósito Fletes $ 3.000
7 Sucursal Fletes $ 1.000
8 Depósito Fletes $ 1.500
Total $
7.500

Ejemplo 4:
Selecciono "Administración" en el ComboBox 1
Selecciono "Todos" en el ComboBox 2
Resultado: El ListBox muestra:
Nº Op. Centro de Costo Cuenta Importe
1 Administración Mantenimiento $ 1.000
2 Administración Mantenimiento $ 2.000
5 Administración Impuestos $ 1.500
Total $
4.500

Sospecho que debe poder hacerse con los filtros avanzados


(AdvancedFilter),
pero no se como manejarlos. La unica manera que intenté para resolverlo es
haciendo varios bucles del tipo For - Next en donde el código va


comparando
celda por celda si cumple o no con las condiciones, pero la verdad que
funciona muy lento y queda un código excesivamente complejo. Estoy seguro
que con el AdvancedFilter se debe poder hacer mejor. Ayuda por favor!
Espero haya sido claro en mi exposición. Cualquier consulta estoy a su
disposición.
Saludos y desde ya muchas gracias.

Mariano



Amigo Mariano,

Yo no soy un duro en programacion de Macros o Exce VBa, pero me doy
cuenta que eso lo puedes hacer simplemente con una tabla
dinámica . ..

Cual es el problema ? A veces no hay que complicarse tanto para llegar
a un fin. La tabla dinamica te suma en la parte de abajo según el
criterio, además te da la opcion de incluso filtrar datos por los dos
criterios que quieres utilizar .. .
Respuesta Responder a este mensaje
#3 Ivan
24/09/2008 - 00:30 | Informe spam
hola Mariano,

cuanto tiempo (de hecho creo recordar que hace ya bastante tuviste
alguna consulta [muy] similar, aunque lo mismo mi neurona empieza
[sigue :-)] a fallarme)

sea configurada y mostrada desde controles en un UserForm,



seguro que es muy simplificable, pero si quieres haz alguna prueba con
el codigo que te pongo a continuacion:

no esta comentado, excepto la descripcion de los parametros, pero en
definitiva lo que hace es =>

a) cargar en un listbox (1er parametro)

b) el contenido de una lista (la 1ª celda<Range> de los titulos como
2º parametro)

c) tras filtrarla y pegar el resultado en una hoja (3er parametro nombre de esta hoja <String>)

d) con las coincidencias encontradas en las columnas pasadas en el 4º
parametro <Variant/Matriz>

e) de los respectivos criterios pasados en el 5º parametro <Variant/
Matriz>

Puedes poner tantos criterios/campos como gustes, pero tal y como
esta diseñado el codigo, deben de ser de tipo texto,

y por supuesto, ordenados igual en el 4º y 5º parametros

[y ... no tiene control de errores]

OJO a los posibles saltos de linea en el codigo


' Parametros:
'
' .-> lstbox = el listbox
' .-> celdaini = 1ª celda de la lista, incluida la fila
' de titulos. Pej: Range("A1")
' .-> hojaDestino = nombre de una hoja, que puede estar oculta
' y que podrias crear al abrir el form y
' eliminarla al cerrarlo, y que estaria
' destinada a los registros filtrados
' .-> columnas = matriz col los numeros de las columnas en
' donde se van a comparar los criterios
' .-> datos = matriz con el contenido de los combos y en
' el mismo orden de las columnas.
'
Private Sub cargarListBox( _
ByRef lstBox As MSForms.ListBox, _
ByVal celdaIni As Range, _
ByVal hojaDestino As String, _
ByRef columnas As Variant, _
ByRef datos As Variant)

Dim rngLista As Range, rngDestino As Range, _
rngCriterio As Range
Set rngLista = celdaIni.CurrentRegion
With rngLista.Parent
Set rngCriterio = .Range(.Cells(1, _
rngLista.Columns.Count + 3), _
.Cells(2, rngLista.Columns.Count + 3))
With .Parent.Worksheets(hojaDestino)
Set rngDestino = .Range(.Cells(1, 1), _
.Cells(1, rngLista.Columns.Count))
End With
End With
rngDestino.CurrentRegion.Clear
rngCriterio.CurrentRegion.Clear
Dim n As Integer, criterio As String
criterio = ""
For n = o To UBound(columnas)
If datos(n) <> "" Then _
criterio = criterio & Cells(2, columnas(n)) _
.Address(0, 0) & "=""" & datos(n) & ""","
Next
ListBox1.RowSource = ""
If criterio <> "" Then criterio = _
"=and(" & Left(criterio, Len(criterio) - 1) & ")"
rngCriterio.Cells(2, 1).Value = criterio
rngLista.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rngCriterio, _
CopyToRange:=rngDestino, _
Unique:=False
With lstBox
.Clear
.ColumnCount = rngDestino.Columns.Count
.ColumnHeads = True
.RowSource = rngDestino.Parent.Name & "!" & _
rngDestino.CurrentRegion.Offset(1).Resize( _
rngDestino.CurrentRegion.Rows.Count - 1, _
rngDestino.Columns.Count).Address(0, 0)
End With
rngCriterio.Clear
Set rngCriterio = Nothing
Set rngDestino = Nothing
Set rngLista = Nothing
End Sub

' y esta seria una aplicacion en tu ejemplo,
' con los controles llamados 'ListBox1', 'ComboBox1' y 2,
' la hoja de la lista "Hoja1" y como nombre de la hoja
' de destino "oculta".
' NOTA: en todos los combos iria el mismo codigo
'
Private Sub ComboBox1_Change()
Dim col As Variant, dts As Variant
col = Array(2, 3)
dts = Array(ComboBox1.Text, ComboBox2.Text)
cargarListBox ListBox1, Worksheets("Hoja1").[a1], _
"oculta", col, dts
End Sub
'

bueno, al menos parece funcionar

un saludo
Ivan

' --codigo-
Respuesta Responder a este mensaje
#4 Ivan
24/09/2008 - 00:48 | Informe spam
hola de nuevo (aunque a mi todavia no me aparece el anterior mensaje
que he enviado)

en dicho mensaje cambia el codigo por este otro (las explicaciones y
forma de aplicacion son las mismas). Es practicamente igual pero se me
habia quedado una cosilla por ahi [ojo: lo mismo salta alguna
otra :-D] . Para no liarla lo vuelvo a poner entero retocado.


Private Sub cargarListBox( _
ByRef lstBox As MSForms.ListBox, _
ByVal celdaIni As Range, _
ByVal hojaDestino As String, _
ByRef columnas As Variant, _
ByRef datos As Variant)

Dim rngLista As Range, rngDestino As Range, _
rngCriterio As Range
Set rngLista = celdaIni.CurrentRegion
With rngLista.Parent
Set rngCriterio = .Range(.Cells(1, _
rngLista.Columns.Count + 3), _
.Cells(2, rngLista.Columns.Count + 3))
With .Parent.Worksheets(hojaDestino)
Set rngDestino = .Range(.Cells(1, 1), _
.Cells(1, rngLista.Columns.Count))
End With
End With
rngDestino.CurrentRegion.Columns.Clear
rngCriterio.CurrentRegion.Clear
Dim n As Integer, criterio As String
criterio = ""
For n = o To UBound(columnas)
If datos(n) <> "" Then _
criterio = criterio & Cells(2, columnas(n)) _
.Address(0, 0) & "=""" & datos(n) & ""","
Next
If criterio <> "" Then criterio = _
"=and(" & Left(criterio, Len(criterio) - 1) & ")"
rngCriterio.Cells(2, 1).Value = criterio
rngLista.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rngCriterio, _
CopyToRange:=rngDestino, _
Unique:=False
With lstBox
.RowSource = ""
.ColumnCount = rngDestino.Columns.Count
.ColumnHeads = True
Dim fila As Long
fila = rngDestino.CurrentRegion.Rows.Count - 1
If fila < 1 Then fila = 1
.RowSource = rngDestino.Parent.Name & "!" & _
rngDestino.CurrentRegion.Offset(1).Resize( _
fila, rngDestino.Columns.Count).Address(0, 0)
End With
rngCriterio.Clear
Set rngCriterio = Nothing
Set rngDestino = Nothing
Set rngLista = Nothing
End Sub

un saludo
Ivan
Respuesta Responder a este mensaje
#5 Contaduria
25/09/2008 - 17:20 | Informe spam
Estimado Ivan:

Muchisimas gracias por tu ayuda. Tu solución es lo que buscaba y funciona
perfectamente.
Y con respecto a tu pregunta, la respuesta es si, hace tiempo me habías
pasado una solución de filtros avanzados, pero referida a datos ingresados
en determinadas celdas en una hoja. Quise adaptarlo a controles en un
UserForm pero sin éxito. Pero yo sabía que la cuestión podía solucionarse
con los filtros avanzados, solo se requeria el conocimiento necesario que
vos amablemente has compartido conmigo.
Muchas gracias otra vez por solucionar mis problemas.

Mariano

Insumos
Mar del Plata
(0223) 475-5511
Open Sports... elegí deporte!
www.opensports.com.ar



"Ivan" escribió en el mensaje
news:
hola de nuevo (aunque a mi todavia no me aparece el anterior mensaje
que he enviado)

en dicho mensaje cambia el codigo por este otro (las explicaciones y
forma de aplicacion son las mismas). Es practicamente igual pero se me
habia quedado una cosilla por ahi [ojo: lo mismo salta alguna
otra :-D] . Para no liarla lo vuelvo a poner entero retocado.


Private Sub cargarListBox( _
ByRef lstBox As MSForms.ListBox, _
ByVal celdaIni As Range, _
ByVal hojaDestino As String, _
ByRef columnas As Variant, _
ByRef datos As Variant)

Dim rngLista As Range, rngDestino As Range, _
rngCriterio As Range
Set rngLista = celdaIni.CurrentRegion
With rngLista.Parent
Set rngCriterio = .Range(.Cells(1, _
rngLista.Columns.Count + 3), _
.Cells(2, rngLista.Columns.Count + 3))
With .Parent.Worksheets(hojaDestino)
Set rngDestino = .Range(.Cells(1, 1), _
.Cells(1, rngLista.Columns.Count))
End With
End With
rngDestino.CurrentRegion.Columns.Clear
rngCriterio.CurrentRegion.Clear
Dim n As Integer, criterio As String
criterio = ""
For n = o To UBound(columnas)
If datos(n) <> "" Then _
criterio = criterio & Cells(2, columnas(n)) _
.Address(0, 0) & "=""" & datos(n) & ""","
Next
If criterio <> "" Then criterio = _
"=and(" & Left(criterio, Len(criterio) - 1) & ")"
rngCriterio.Cells(2, 1).Value = criterio
rngLista.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=rngCriterio, _
CopyToRange:=rngDestino, _
Unique:=False
With lstBox
.RowSource = ""
.ColumnCount = rngDestino.Columns.Count
.ColumnHeads = True
Dim fila As Long
fila = rngDestino.CurrentRegion.Rows.Count - 1
If fila < 1 Then fila = 1
.RowSource = rngDestino.Parent.Name & "!" & _
rngDestino.CurrentRegion.Offset(1).Resize( _
fila, rngDestino.Columns.Count).Address(0, 0)
End With
rngCriterio.Clear
Set rngCriterio = Nothing
Set rngDestino = Nothing
Set rngLista = Nothing
End Sub

un saludo
Ivan
Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaSiguiente Respuesta Tengo una respuesta
Search Busqueda sugerida