Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como buscar datos entre rango de fechas


(adsbygoogle = window.adsbygoogle || []).push({});

Un suscriptor del canal de YuoTube, me solicitó realizar un ejemplo sobre como buscar datos en un rango de fechas y cargarlos o rellenar el listbox con estos datos; es decir se requería buscar datos de una hoja de Excel entre una fecha inicial y una fecha final, una vez encontrados esos datos, cargarlos en el listbox.

Anteriormente se presentaron muchos ejemplos relacionados con listbox, si estás leyendo este post es posible que te sean de utilidad:



Como escribir en el listbox el total de registros e importes
Como cargar cabecera o header buscar datos por cliente entre rango de fechas y cargar en listbox
Como buscar datos por cliente entre un rango de fechas

Como buscar datos entre rango de fechas
Como pasar datos listbox a otro listbox con enter

Como crear una factura o sale invoice seleccionando cliente de listbox
Como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail
Buscar en listbox mientras se escribe en textbox

Antes de seguir 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 ejemplo de macro muestra como buscar datos entre rango de fecha o entre una fecha inicial y otra fila o entre fecha desde fecha hasta, desde el link del final se puede descargar.

Al descargar el formulario se observará dos textbox y un botón de comando, en el primer textbox se debe ingresar la fecha inicial y en el segundo la fecha final, luego se debe presionar el botón, se podrá observar como que se filtran los datos entre el rango de fechas ingresadas en los textbox, cargándose los datos que se encuentren entre la fecha inicial y la fecha final en el listbox que se encuentra en el formulario.

En este ejemplo además se aprenderá como cargar en variables los datos de textbox, lo cual se hace con el siguiente código, que carga en la variable dato1 y dato2 las fecha inicial y final respectivamente ingresadas en los textbox.

dato1 = CDate(TextBox2)
dato2 = CDate(TextBox3)

También se podrá observar como validar el ingreso de datos en textbox, la primer validación es pára que sea ingresado los datos fecha inicial y final antes de ejecutar la macro y la segunda valida que la fecha final no sea menor a la fecha inicial.

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

Para cargar los datos en el listbox debe pasar el filtro que se da con la instrucción If siguiente, que como se observa compara el dato de la variable fecha inicial y fecha final con el dato de la celda que se está recorriendo en ese momento con el bucle, que es necesario para recorrer todas las celdas de la primera a la última fila con datos, comparando la fecha de la base de datos con las fechas cargadas en los textbox, si se encuentra incluida en el rango de fechas, carga el dato en el listbox.

If dato0 >= dato1 And dato0 <= dato2 Then

También se podrá ver como cargar datos en listbox de varias columnas, lo cual se realiza con el siguiente código:

       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)

El vídeo que sigue muestra una explicación más detallada y gráfica de la macro presentada, 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 como pasar datos de un listbox a otro listbox con enterbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, libro con las mejores macros del sitio web y muchos ejemplos más.

function onYtEvent(payload) {
if (payload.eventType == ‘subscribe’) {
// Add code to handle subscribe event.
} else if (payload.eventType == ‘unsubscribe’) {
// Add code to handle unsubscribe event.
}
if (window.console) { // for debugging only
window.console.log(‘YT event: ‘, payload);
}
}

Código que se inserta en un módulo
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

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
Me.ListBox1.ColumnWidths = «170 pt;50 pt;50 pt;50 pt;50 pt;50 pt;50 pt»
End Sub

Private Sub CommandButton3_Click()
Unload UserForm1
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!A2:» & 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

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.


(adsbygoogle = window.adsbygoogle || []).push({});

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      

Entradas relacionadas

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Comentarios (32)

I’m the manager of JustCBD Store label (justcbdstore.com) and I am currently seeking to expand my wholesale side of company. It would be great if someone at targetdomain can help me 🙂 I thought that the most suitable way to accomplish this would be to connect to vape shops and cbd retail stores. I was hoping if anyone could recommend a reliable website where I can buy CBD Shops B2B Sales Leads I am currently considering creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Unsure which one would be the most ideal selection and would appreciate any advice on this. Or would it be much simpler for me to scrape my own leads? Suggestions?

Responder

I seriously love your site.. Very nice colors & theme. Did you make this web site yourself? Please reply back as I’m trying to create my own website and would love to learn where you got this from or exactly what the theme is called. Thank you!

Responder

Hi there, There’s no doubt that your blog may be having web browser compatibility issues. Whenever I take a look at your web site in Safari, it looks fine however, when opening in I.E., it has some overlapping issues. I merely wanted to give you a quick heads up! Apart from that, great blog!

Responder

That is a great tip particularly to those new to the blogosphere. Short but very precise info… Many thanks for sharing this one. A must read post!

Responder

Everything is very open with a very clear clarification of the challenges. It was truly informative. Your website is extremely helpful. Thank you for sharing!

Responder

Nice post. I learn something new and challenging on sites I stumbleupon everyday. It’s always helpful to read through articles from other authors and use a little something from their web sites.

Responder

I simply want to say I’m very new to weblog and honestly loved you’re web page. Very likely I’m likely to bookmark your website . You actually have good articles and reviews. Thanks a lot for revealing your web-site.

Responder

It’s remarkable to go to see this website and reading the
views of all mates on the topic of this article, while I am also eager of
getting know-how. adreamoftrains web hosting companies

Responder

Hello there! I know this is kind of off topic but I was
wondering if you knew where I could find a captcha plugin for my comment form?

I’m using the same blog platform as yours and I’m having problems finding one?
Thanks a lot!

Responder

I’m impressed, I have to admit. Rarely do I come across a blog that’s both equally educative and entertaining, and without a doubt, you’ve hit the nail on the head. The issue is an issue that too few men and women are speaking intelligently about. I’m very happy that I found this in my hunt for something concerning this.

Responder

What’s up, of course this article is really pleasant and I have learned lot of things from
it concerning blogging. thanks.

Responder

Hi there! This is my first visit to your blog! We are a
team of volunteers and starting a new initiative in a community in the same niche.

Your blog provided us valuable information to work on. You have done a outstanding job!
cheap flights 34pIoq5

Responder

I think this is one of the most important information for me.
And i’m glad reading your article. But should remark on few general things,
The web site style is ideal, the articles is really great : D.
Good job, cheers 2CSYEon cheap flights

Responder

I think this is among the most important info for
me. And i’m glad reading your article. But want to remark on few general things, The site style is great,
the articles is really nice : D. Good job, cheers

Responder

Good blog you have here.. It’s hard to find high quality writing like yours nowadays.

I honestly appreciate individuals like you! Take care!!
34pIoq5 cheap flights

Responder

Wow, this article is pleasant, my sister is analyzing these kinds of things, so I am going to inform her.

Responder

Oh my goodness! Incredible article dude! Thank you, However I am encountering issues with your RSS. I don’t know the reason why I am unable to subscribe to it. Is there anybody getting similar RSS problems? Anyone that knows the solution can you kindly respond? Thanks!!

Responder

I’d like to thank you for the efforts you have put in penning this blog. I am hoping to check out the same high-grade blog posts by you later on as well. In truth, your creative writing abilities has motivated me to get my very own website now 😉

Responder

That is a really good tip especially to those fresh to the blogosphere. Simple but very precise info… Thanks for sharing this one. A must read article!

Responder

I have to thank you for the efforts you’ve put in writing this blog. I’m hoping to see the same high-grade blog posts by you later on as well. In fact, your creative writing abilities has encouraged me to get my own, personal site now 😉

Responder

You are so interesting! I do not believe I’ve truly read something like that before. So great to discover somebody with genuine thoughts on this issue. Really.. thanks for starting this up. This site is something that’s needed on the web, someone with some originality!

Responder

You’ve made some good points there. I looked on the web for more information about the issue and found most people will go along with your views on this site.

Responder

Hi there! This article couldn’t be written much better! Going through this post reminds me of my previous roommate! He continually kept talking about this. I am going to forward this information to him. Pretty sure he’ll have a great read. Many thanks for sharing!

Responder

An interesting discussion is definitely worth comment. I do think that you ought to publish more on this topic, it might not be a taboo matter but typically people do not speak about such topics. To the next! Kind regards!!

Responder

Right here is the right website for anybody who would like to find out about this topic. You realize so much its almost tough to argue with you (not that I really will need to…HaHa). You definitely put a fresh spin on a subject that has been written about for years. Great stuff, just wonderful!

Responder

This is a really good tip especially to those fresh to the blogosphere. Simple but very precise info… Thank you for sharing this one. A must read article!

Responder

This is a topic that’s near to my heart… Best wishes! Where are your contact details though?

Responder

An outstanding share! I have just forwarded this onto a coworker who has been doing a little homework on this. And he actually bought me breakfast due to the fact that I found it for him… lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanx for spending the time to discuss this issue here on your web page.

Responder

Do you mind if I quote a couple of your articles as long as
I provide credit and sources back to your blog?
My blog site is in the exact same area of interest as yours and my visitors would certainly benefit from a lot of the information you present here.
Please let me know if this okay with you.
Thanks a lot!

Responder

Undeniably consider that that you stated. Your favourite justification seemed to be on the internet the simplest
factor to take note of. I say to you, I definitely get annoyed while other people think about
issues that they plainly do not know about.
You managed to hit the nail upon the highest and also defined out the entire thing without having side-effects , other folks could take a signal.

Will probably be back to get more. Thanks

Responder

Hi, its nice post about media print, we all know media is a wonderful source
of facts.

My page: biden we just did 46 hat

Responder

This is a topic that’s close to my heart… Thank you! Where are your contact details though?

My web site … biden we did hat

Responder