Mostrando las entradas con la etiqueta Busqueda de Datos en Excel. Mostrar todas las entradas
Mostrando las entradas con la etiqueta Busqueda de Datos en Excel. Mostrar todas las entradas

Como Filtrar Datos Excel con Consulta SQL Vs Bucle While Wend





En este post se presenta una comparación entre macros, se muestra por un lado una macro que permite filtrar datos mediante criterios utilizando un Bucle con la estructura While ... Wend y por otro lado se usa SQL para buscar datos en Excel, los resultados son los mismos, en base de datos chicas el tiempo de búsqueda es prácticamente el mismo, pero en bases de datos más grandes SQL es mucho más rápido.

Descarga el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

Mira una explicación detallada en el vídeo, suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.









 


Al descargar el ejemplo y abrir el libro de Excel se podrá observar que se encuentran dos botones, el primero ejecuta un filtro o búsqueda utilizando un bucle con la estructura While...Wend; el segundo botón realiza la misma búsqueda o filtro de datos utilizando SQL.

Los criterios de búsqueda se encuentran en la Hoja1 celda B1 y Celda E1, en el ejemplo que se muestra se busca todos los datos coincidentes en la base de datos que se encuentra en la Hoja2, que sena iguales a la marca "Coca Cola" cuyo precio de venta (Pv) sea mayor a "Cinco".

Con el bucle While... When, se recorre todas las filas buscando los registros que coincidan con los criterios, en este ejemplo los criterios se asignan a variables que son las siguientes:

marca = UCase(b.Range("B1"))
signo = b.Range("D1")
valor = b.Range("E1")

marcabus = UCase(a.Cells(filabus, "D"))
valorbus = a.Cells(filabus, "E")


Las primeras variables son las de la hoja de destino y las últimas dos variables son los datos que va tomando cada vez que se recorra las filas con el bucle While ... Wend

Si la marca ingresada en B1 es coincidente con la marca de la fila correspondiente de la Hoja2 que en cada momento recorre el bucle y además el valor de venta es mayor al valor buscado, entonces el registro cumple la condición, lo copia y pega en la Hoja1 y así sucesivamente con cada uno de los ítem de la base de datos.

If marcabus = marca And valorbus > valor Then
a.Range("A" & filabus & ":G" & filabus).Copy Destination:=b.Range("A" & fila)

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
Quizá sea de utilidad también

Como crear una factura con excel, guardarla y enviarla por mail automáticamente


Como cargar listbox con datos provenientes de varias hojas

Como repetir en Excel un mismo caracter varias veces

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛



⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

La otra forma de búsqueda es el Filtro de Datos de Excel a través de SQL, para ello primero hay que realizar una conexión con el Libro Excel, que puede ser el mismo u otro libro, en este caso es el mismo, ya que los datos se encuentran en el mimos libro sobre el cual se trabaja.

La conexión con el mismo libro se realiza con el siguiente código, a continuación la SQL, que es el string de consulta que contiene los criterios de búsqueda o filtro de datos.

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"""

sql = "SELECT * FROM [" & "Hoja2$" & "] WHERE Ucase(" & a.Range("D1") & ") LIKE Ucase('%" & b.Range("B1") & "%') AND pv " & b.Range("D1") & " " & b.Range("E1") & " ORDER BY pv ASC"

La SQL se podría leer: Selecciones todas las columnas de la Hoja2 cuando la columna Marca coincida con el texto (marca) ingresado en B1 y el precio de venta sea Mayor al precio que figura en la columna "Pv" de la base de datos.

Una vez ejecutada la SQL los datos filtrados o coincidentes se mantienen en memoria (Recorset), hasta tanto sean usado y liberadas las variables.

Set rs = cn.Execute(sql)
b.Cells(3, 1).CopyFromRecordset Data:=rs

En el código anterior los datos filtrados son copiados a partir de la fila 1 columna 1 es decir celda A3, ya que en la fila 2 van los encabezados de columna.

Para liberar las variables y por ende liberar recursos, se usa:

Set rs = Nothing
cn.Close
Set cn = Nothing


El ejemplos denominado Como Filtrar Datos Excel con Consulta SQL vs. Bucle While ... Wend, se puede descargar desde el link del final que esta en forma posterior a la codificación del ejemplo que se encuentra a continuación.


Código que se inserta en un módulo

Sub ConsutaSQLExcel()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim ctl As Object
Dim cn As ADODB.Connection, rs As ADODB.Recordset, sql As String
On Error Resume Next
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set b = Sheets("Hoja1")
Set a = Sheets("Hoja2")

cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=Yes;"""
sql = "SELECT * FROM [" & "Hoja2$" & "] WHERE Ucase(" & a.Range("D1") & ") LIKE Ucase('%" & b.Range("B1") & "%') AND pv " & b.Range("D1") & " " & b.Range("E1") & " ORDER BY pv ASC"

uf = b.Range("A" & Rows.Count).End(xlUp).Row
If uf < 3 Then uf = 2
b.Range("A3:G" & uf).Clear
a.Range("A1:G1").Copy Destination:=b.Range("A2")
Set rs = cn.Execute(sql)
b.Cells(3, 1).CopyFromRecordset Data:=rs
b.Range("B:B").NumberFormat = "dd/mm/yyyy"
Set rs = Nothing
cn.Close
Set cn = Nothing
If b.Range("A3") <> Empty Then
MsgBox ("La busqueda se realizó con éxito"), vbInformation, "AVISO"
Else
MsgBox ("No se encontraron regisgros para el criterio de búsqueda"), vbInformation, "AVISO"
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

Sub ConsutaBucleWhileWend()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'On Error Resume Next
Set b = Sheets("Hoja1")
Set a = Sheets("Hoja2")
filabus = 2
fila = 3
uf = b.Range("A" & Rows.Count).End(xlUp).Row
If uf < 3 Then uf = 2
b.Range("A3:G" & uf).Clear
a.Range("A1:G1").Copy Destination:=b.Range("A2")

While a.Cells(filabus, "A") <> Empty
marca = UCase(b.Range("B1"))
signo = b.Range("D1")
valor = b.Range("E1")

marcabus = UCase(a.Cells(filabus, "D"))
valorbus = a.Cells(filabus, "E")
If marcabus = marca And valorbus > valor Then
a.Range("A" & filabus & ":G" & filabus).Copy Destination:=b.Range("A" & fila)
fila = fila + 1
End If
filabus = filabus + 1
Wend

b.Range("B:B").NumberFormat = "dd/mm/yyyy"
If b.Range("A3") <> Empty Then
MsgBox ("La busqueda se realizó con éxito"), vbInformation, "AVISO"
Else
MsgBox ("No se encontraron regisgros para el criterio de búsqueda"), vbInformation, "AVISO"
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛


Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Como Filtrar por Cliente Rango de Fechas e Imprimir Reporte





En este post se muestra Como Filtrar por Cliente Rango de Fechas e Imprimir Reporte en Excel; es decir que la macro que se encuentre en el formulario Excel, permite buscar datos por cliente, rango de fechas o ambos criterios a la vez en Excel, una vez encontrados dichos registros se tiene la opción de imprimir el reporte con datos filtrados por cliente y entre fecha y fecha.

Desde el final del post se puede descargar el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

El vídeo verás la macro en acción con una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.









 


Si bien el ejemplo muestra en primer momento Como Filtrar por Cliente y Rango de Fechas, esto ya fue expuesto anteriormente y explicado en detalle, por lo que sugiero ver su explicación, en este post se centrará en mostrar como imprimir reporte luego de filtrar por cliente o rango, rango de fechas o ambos criterios.


⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
Quizá sea de utilidad también

Como enviar mail con imagen guardada en PC en el cuerpo del mensaje

Como crear y trabajar con una colección de datos únicos para realizar bucle

Como copiar carpetas y archivos con macro

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛



⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Una vez realizado el filtro o búsqueda de datos en el formulario existe un botón que permite imprimir el reporte, básicamente lo que se hace es utilizar una hoja de Excel en forma temporal, en dicha hoja se vuelcan los datos filtrados que están en el listbox, luego se da formatos, imprime y se borra la hoja temporal, al evitar los movimientos de pantalla el usuario no verá absolutamente nada solo tardará unos segundos en realizar lo mencionado e imprimir el informe.

Para crear la hoja se usa el siguiente código:

Sheets("DFSHJFDUYDAYRAIUY544TTTOMYDUTGD").Delete
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "DFSHJFDUYDAYRAIUY544TTTOMYDUTGD"
Set a = Sheets("DFSHJFDUYDAYRAIUY544TTTOMYDUTGD")

Para pasar los datos filtrados que están en el listbox a la hoja temporal de Excel se usa el siguiente código:

For x = 1 To UserForm1.ListBox1.ListCount - 5
a.Cells(x + 2, "A") = ListBox1.List(x, 0)
a.Cells(x + 2, "B") = CDate(ListBox1.List(x, 1))
a.Cells(x + 2, "C") = ListBox1.List(x, 2)
a.Cells(x + 2, "D") = ListBox1.List(x, 3)
a.Cells(x + 2, "E") = ListBox1.List(x, 4)
a.Cells(x + 2, "F") = ListBox1.List(x, 5)
a.Cells(x + 2, "G") = CDec(ListBox1.List(x, 6))
Next

a.Cells(x + 4, "A") = ListBox1.List(x + 2, 0)
a.Cells(x + 5, "A") = ListBox1.List(x + 3, 0)
a.Cells(x + 4, "B") = ListBox1.List(x + 2, 1)
a.Cells(x + 5, "B") = ListBox1.List(x + 3, 1)

Posteriormente se agrega el encabezado a las columnas y se da formato al reporte, pudiendo cada uno adaptar al formato de reporte que necesite.

a.Activate
a.Range("A1") = "REPORTE DE VENTAS"

With a.Range("A1:G1")
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.RowHeight = 20
.Font.Size = 16
End With

a.Range("A2") = "CLIENTE"
a.Range("B2") = "FECHA"
a.Range("C2") = "COMPROBANTE"
a.Range("D2") = "TIPO"
a.Range("E2") = "SUC"
a.Range("F2") = "N COMP"
a.Range("G2") = "IMPORTE"
uf = a.Range("G" & Rows.Count).End(xlUp).Row
a.Range("G2:G" & uf).NumberFormat = "#.#,0"
a.Range("B2:B" & uf).NumberFormat = "dd/mm/yyyy"
a.Range("A:G").Columns.AutoFit
a.Range("A:A").ColumnWidth = 31


Luego se prepara para la impresión, configurando la hoja de Ex determinando área de impresión cantidad de páginas de alto y ancho y luego imprime en la impresora determinada con el siguiente código:

ActiveSheet.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False

El código del ejemplo llamado Como Filtrar por Cliente Rango de Fechas e Imprimir Reporte se encuentra a continuación y posterior a ello el link de descarga de ejemplo.


Código que se inserta en un módulo

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
On Error Resume Next
Set b = Sheets("Hoja1")
uf = b.Range("A" & Rows.Count).End(xlUp).Row
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
If dato2 = Empty Or dato1 = emtpy Then
MsgBox ("Debe ingresar datos para consulta entre rango de fechas"), vbCritical, "AVISO"
Exit Sub
End If
If dato2 < dato1 Then
MsgBox ("La fecha final no puede ser mayor a la fecha inicial"), vbCritical, "AVISO"
Exit Sub
End If

b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear

'Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem

If dato1 = Empty Or dato2 = Empty Then

For i = 2 To uf
   dato0 = CDate(b.Cells(i, 2).Value)
   If dato0 >= dato1 And dato0 <= dato2 Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = b.Cells(i, 7)
   End If
Next i


Else

If dato2 < dato1 Then
MsgBox ("La fecha final no puede ser mayor a la fecha inicial"), vbCritical, "AVISO"
Exit Sub
End If

For i = 2 To uf
   strg = b.Cells(i, 1).Value
   dato0 = CDate(b.Cells(i, 2).Value)
   If UCase(strg) Like UCase(TextBox1.Value) & "*" And dato0 >= dato1 And dato0 <= dato2 Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = b.Cells(i, 7)
   End If
Next i

End If


'Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets("Hoja1").Cells(1, ii + 1)
Next ii

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total Importe"

For x = 0 To UserForm1.ListBox1.ListCount - 1
t = CDec(UserForm1.ListBox1.List(x, 6))
tot = tot + t
t = 0
Next x
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = Format(tot, " ""U$S"" #,##0.00 ")

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total de registros:"
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = UserForm1.ListBox1.ListCount - 5

Me.ListBox1.ColumnWidths = "170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 pt"
End Sub

Private Sub CommandButton3_Click()
Unload UserForm1
End Sub

Private Sub CommandButton4_Click()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next

'Elimina hoja y crea hoja dando el mismo nombre que la eliminada
Sheets("DFSHJFDUYDAYRAIUY544TTTOMYDUTGD").Delete
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "DFSHJFDUYDAYRAIUY544TTTOMYDUTGD"
Set a = Sheets("DFSHJFDUYDAYRAIUY544TTTOMYDUTGD")

For x = 1 To UserForm1.ListBox1.ListCount - 5
a.Cells(x + 2, "A") = ListBox1.List(x, 0)
a.Cells(x + 2, "B") = CDate(ListBox1.List(x, 1))
a.Cells(x + 2, "C") = ListBox1.List(x, 2)
a.Cells(x + 2, "D") = ListBox1.List(x, 3)
a.Cells(x + 2, "E") = ListBox1.List(x, 4)
a.Cells(x + 2, "F") = ListBox1.List(x, 5)
a.Cells(x + 2, "G") = CDec(ListBox1.List(x, 6))
Next

a.Cells(x + 4, "A") = ListBox1.List(x + 2, 0)
a.Cells(x + 5, "A") = ListBox1.List(x + 3, 0)
a.Cells(x + 4, "B") = ListBox1.List(x + 2, 1)
a.Cells(x + 5, "B") = ListBox1.List(x + 3, 1)

a.Activate
a.Range("A1") = "REPORTE DE VENTAS"

With a.Range("A1:G1")
.Merge
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
.RowHeight = 20
.Font.Size = 16
End With

a.Range("A2") = "CLIENTE"
a.Range("B2") = "FECHA"
a.Range("C2") = "COMPROBANTE"
a.Range("D2") = "TIPO"
a.Range("E2") = "SUC"
a.Range("F2") = "N COMP"
a.Range("G2") = "IMPORTE"
uf = a.Range("G" & Rows.Count).End(xlUp).Row
a.Range("G2:G" & uf).NumberFormat = "#.#,0"
a.Range("B2:B" & uf).NumberFormat = "dd/mm/yyyy"
a.Range("A:G").Columns.AutoFit
a.Range("A:A").ColumnWidth = 31
Application.PrintCommunication = True
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$G$" & uf + 4
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.PrintCommunication = True
ActiveSheet.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
a.Delete
Sheets("Hoja1").Select
MsgBox "El reporte se imprimió con éxito", vbCritical, "AVISO"
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Private Sub TextBox1_Change()
On Error Resume Next
Set b = Sheets("Hoja1")
uf = b.Range("A" & Rows.Count).End(xlUp).Row
If Trim(TextBox1.Value) = "" Then
   Me.ListBox1.RowSource = "Hoja1!A1:G" & uf
   Exit Sub
End If


b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
'Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem


For i = 2 To uf
   strg = b.Cells(i, 1).Value
   If UCase(strg) Like UCase(TextBox1.Value) & "*" Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = b.Cells(i, 7)
   End If
Next i



'Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets("Hoja1").Cells(1, ii + 1)
Next ii


UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total Importe"

For x = 0 To UserForm1.ListBox1.ListCount - 1
t = CDec(UserForm1.ListBox1.List(x, 6))
tot = tot + t
t = 0
Next x
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = Format(tot, " ""U$S"" #,##0.00 ")

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total de registros:"
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = UserForm1.ListBox1.ListCount - 5


UserForm1.TextBox2 = Clear
UserForm1.TextBox3 = Clear

Me.ListBox1.ColumnWidths = "170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 pt"
End Sub


Private Sub TextBox2_Change()
If Len(UserForm1.TextBox2) = 10 Then UserForm1.TextBox3.SetFocus
End Sub

Private Sub TextBox3_Change()
If Len(UserForm1.TextBox3) = 10 Then UserForm1.CommandButton2.SetFocus
End Sub

Private Sub UserForm_Initialize()
Dim fila As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set b = Sheets("Hoja1")
uf = b.Range("A" & Rows.Count).End(xlUp).Row
uc = b.Cells(1, Columns.Count).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, "$") + 1, InStr(2, uc, "$") - 2)
With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "170 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 pt"
    .RowSource = "Hoja1!A1:" & wc & uf
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo Fin
If CloseMode <> 1 Then Cancel = True
Fin:
End Sub



Código que se inserta en un módulo

Sub muestra1()
UserForm1.Show
End Sub

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛



Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends      

Como Buscar por Cliente Rango de Fechas y Totalizar en Listbox





La macro muestra Como Buscar por Cliente Rango de Fechas y Totalizar Importes en el Listbox., la macro una vez que filtra por algunos de los criterios mencionados, cuenta la cantidad de registros filtrados y a su vez suma los importes de la columna total del Listbox y los muestra en el mismo Listbox.

Desde el final del post se puede descargar el ejemplo en forma gratuita sin ninguna restricción, el código se puede adaptar a cada necesidad, Aporta por favor para sostener el sitio si está dentro de tus posibilidades, desde ya muchas gracias.

Si te estás iniciando en la operación de Excel o requieres afirmar conocimientos, recomiendo leer un excelente libro sobre Excel que te ayudará operar las planillas u hojas de cálculo, haz click acá, si quieres aprender sobre Excel, en inglés, entonces debes hacer click here. Si lo que necesitas es aprender o profundizar sobre la programación de macros con VBA este es unos de los mejores cursos on line que he visto en internet.

  

El vídeo verás la macro en acción con una explicación más detallada de su codificación y funcionamiento, recomiendo observar para una más fácil comprensión de la macro; suscribe a nuestra web desde la parte superior derecha de la página ingresando tu mail y a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, trabajando con filas, celdas, columnas, rangos y muchos ejemplos más.








 


Este ejemplo es una variante del que se presentó anteriormente denominado Como Buscar por Cliente y Rango de Fechas, en el mismo se encuentra explicado en forma detallada como se filtra por Cliente, Rangos de Fechas o ambos criterios, por lo que sugiero su lectura.

En este ejemplos se adiciona algo solicitado por un suscriptor de nuestro canal de YouTube, que solicitaba poder totalizar importes, en este caso la sumatoria de importes de la columna 6 del listbox se muestra en en mismo listbox al final como así también la cantidad de registros filtrados que coinciden con los criterios de búsqueda en el libro Excel.


⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛
Quizá sea de utilidad también

Como crear una factura con excel, guardarla y enviarla por mail automáticamente


Como enviar mail con archivo Excel y PDF mediante Outlook con Excel

Como hacer un link o hiperlink a google maps con Excel

⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛



⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛

Realizado el filtro la macro muestra los datos en el Listbox, luego del último registro procede a agregar tres filas en el listbox y luego escribir en la fila de la columna 0 del listbox "Total en US$"; eso se hace con el siguiente código:

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total en U$S"

Posteriormente se procede a recorrer todas las filas  de la columna 6 del listbox que contiene los importes a sumar, sumando cada uno de los importes de las distintas filas, mostrando el total en la misma fila donde escribió "Total en U$S pero en la columna siguiente;   con el siguiente código:

For X = 0 To UserForm1.ListBox1.ListCount - 1
t = CDec(UserForm1.ListBox1.List(X, 6))
tot = tot + t
t = 0
Next X
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = Format(tot, " ""U$S"" #,##0.00 ")

Luego adiciona una fila más al listbox y  en la columna 0 se escribe "Total de registros" y en la columna uno la cantidad de registros filtrados, se usa el siguiente código:

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total de registros:"
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = UserForm1.ListBox1.ListCount - 5

Se muestra seguidamente código completo que va en el formulario y en un módulo del ejemplo llamado Como Buscar por Cliente Rango de Fecha y Totalizar en Listbox , seguidamente está el link para descargar el ejemplo.


Código que se inserta en un formulario

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()
On Error Resume Next
Set b = Sheets("Hoja1")
uf = b.Range("A" & Rows.Count).End(xlUp).Row
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
If dato2 = Empty Or dato1 = emtpy Then
MsgBox ("Debe ingresar datos para consulta entre rango de fechas"), vbCritical, "AVISO"
Exit Sub
End If
If dato2 < dato1 Then
MsgBox ("La fecha final no puede ser mayor a la fecha inicial"), vbCritical, "AVISO"
Exit Sub
End If

b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear

'Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem

If dato1 = Empty Or dato2 = Empty Then

For i = 2 To uf
   dato0 = CDate(b.Cells(i, 2).Value)
   If dato0 >= dato1 And dato0 <= dato2 Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = b.Cells(i, 7)
   End If
Next i


Else

If dato2 < dato1 Then
MsgBox ("La fecha final no puede ser mayor a la fecha inicial"), vbCritical, "AVISO"
Exit Sub
End If

For i = 2 To uf
   strg = b.Cells(i, 1).Value
   dato0 = CDate(b.Cells(i, 2).Value)
   If UCase(strg) Like UCase(TextBox1.Value) & "*" And dato0 >= dato1 And dato0 <= dato2 Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = b.Cells(i, 7)
   End If
Next i

End If


'Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets("Hoja1").Cells(1, ii + 1)
Next ii

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total en U$S"

For X = 0 To UserForm1.ListBox1.ListCount - 1
t = CDec(UserForm1.ListBox1.List(X, 6))
tot = tot + t
t = 0
Next X
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = Format(tot, " ""U$S"" #,##0.00 ")

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total de registros:"
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = UserForm1.ListBox1.ListCount - 5

Me.ListBox1.ColumnWidths = "170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 pt"
End Sub

Private Sub CommandButton3_Click()
Unload UserForm1
End Sub

Private Sub TextBox1_Change()
On Error Resume Next
Set b = Sheets("Hoja1")
uf = b.Range("A" & Rows.Count).End(xlUp).Row
If Trim(TextBox1.Value) = "" Then
   Me.ListBox1.RowSource = "Hoja1!A1:G" & uf
   Exit Sub
End If


b.AutoFilterMode = False
Me.ListBox1 = Clear
Me.ListBox1.RowSource = Clear
dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)
'Adiciona un item al listbox reservado para la cabecera
UserForm1.ListBox1.AddItem


For i = 2 To uf
   strg = b.Cells(i, 1).Value
   If UCase(strg) Like UCase(TextBox1.Value) & "*" Then
       Me.ListBox1.AddItem b.Cells(i, 1)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = b.Cells(i, 2)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = b.Cells(i, 3)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = b.Cells(i, 4)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = b.Cells(i, 5)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = b.Cells(i, 6)
       Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = b.Cells(i, 7)
   End If
Next i



'Carga los datos de la cabecera en listbox
For ii = 0 To 7
UserForm1.ListBox1.List(0, ii) = Sheets("Hoja1").Cells(1, ii + 1)
Next ii


UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total en U$S"

For X = 0 To UserForm1.ListBox1.ListCount - 1
t = CDec(UserForm1.ListBox1.List(X, 6))
tot = tot + t
t = 0
Next X
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = Format(tot, " ""U$S"" #,##0.00 ")

UserForm1.ListBox1.AddItem
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 0) = "Total de registros:"
UserForm1.ListBox1.List(UserForm1.ListBox1.ListCount - 1, 1) = UserForm1.ListBox1.ListCount - 5


UserForm1.TextBox2 = Clear
UserForm1.TextBox3 = Clear

Me.ListBox1.ColumnWidths = "170 pt;70 pt;50 pt;50 pt;50 pt;50 pt;50 pt"
End Sub

Private Sub TextBox2_Change()
If Len(UserForm1.TextBox2) = 10 Then UserForm1.TextBox3.SetFocus
End Sub

Private Sub TextBox3_Change()
If Len(UserForm1.TextBox3) = 10 Then UserForm1.CommandButton2.SetFocus
End Sub

Private Sub UserForm_Initialize()
Dim fila As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set b = Sheets("Hoja1")
uf = b.Range("A" & Rows.Count).End(xlUp).Row
uc = b.Cells(1, Columns.Count).End(xlToLeft).Address
wc = Mid(uc, InStr(uc, "$") + 1, InStr(2, uc, "$") - 2)
With Me.ListBox1
    .ColumnCount = 7
    .ColumnWidths = "170 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 pt"
    .RowSource = "Hoja1!A1:" & wc & uf
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error GoTo Fin
If CloseMode <> 1 Then Cancel = True
Fin:
End Sub


Código que se inserta en un módulo

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub CommandButton2_Click()




⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛⇛


Si te fue de utilidad puedes INVITARME UN CAFÉ y de esta manera ayudar a seguir manteniendo la página, CLICK para descargar en ejemplo en forma gratuita.


If this post was helpful INVITE ME A COFFEE and so help keep up the page, CLICK to download free example.


Si te gustó por favor compártelo con tus amigos
If you liked please share it with your friends