Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como crear una coleccion de datos y buscar uno por uno


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

En esta oportunidad se mostrará como crear un listado o colección de datos para luego poder usar ese listado y hacer un bucle para buscar datos uno por uno de todos los registros de la colección de datos.

Como se verá en el ejemplos de crea una colección de datos con todos los nombres de las aseguradoras listadas, luego se usa esos nombre de aseguradoras para buscar en la base de datos los registros relacionados, a su vez en este ejemplo resalta el nombre de las aseguradoras encontrados y los copia en la hoja2, ello es posible mediante la macro que crea un listado o colección de datos que luego son usados en un bucle.

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.

  
Una vez descargado el ejemplo, se podrá observar en el libro de Excel en la hoja 1, uno que ejecuta la macro el otro que vuelve a borrar los datos y dejarlos en el estado original para poder ejecutar la macro uno y otra vez sin problemas; por supuesto que como siembre se puede descargar el ejemplo en forma gratuita desde el final de post.

Se observa que existe en la columna A un listado de compañías aseguradoras, y en la columna B otro listado de compañías aseguradoras, el ejemplo consiste en hacer un listado o colección de datos únicos, es decir sin duplicados, con las compañías aseguradoras que se encuentran en la columna A.

El código que genera la colección de datos es el siguiente, previamente se cargó en la variable r1 el rango donde se encuentran los datos para crear la colección de datos:

For Each celda In Range(r1)
cod.Add celda.Value, CStr(celda.Value)
Next celda

Quizás también interese:
Como usar la función VLookup o BuscarV al presionar enter
Como usar la función VLookup o BuscarV con base datos en otra hoja
Como buscar datos con Vlookup y conservar los hipervinculos en los datos que ese obtienen de la base de datos

El ejemplo toma cada uno de esos nombres de compañías aseguradoras que forman parte de la colección de datos y hace un bucle con cada uno de ellos, es decir recorre desde el primer elemento de la lista hasta el último, una vez encontrado en la columna B el nombre de la compañía aseguradora que se está buscando lo resalta y lo copia en la hoja2; esto es posible con el siguiente código:

For Each dato In cod
busco = dato
Set codigo = Range(r2).Find(busco, LookIn:=xlValues, LookAt:=xlWhole)
If Not codigo Is Nothing Then
dire = codigo.Row
Cells(codigo.Row, «B»).Interior.Color = 255
Cells(codigo.Row, «B»).Copy Destination:=Sheets(«Hoja2»).Range(«A» & j)
j = j + 1
Sheets(«Hoja2»).Cells.Interior.Color = xlNone
End If
Next dato

Si se observa el código anterior con «For Each dato In cod»; se le dice a la macro por cada uno de los elementos de la colección, que los resalte en la columna B y los copia a la hoja2; en resumen se crea una colección de datos y luego se busca estos datos en la base de datos uno por uno.

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 guardar un archivo con una fecha en su nombrebuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mail, como pasar datos de listbox a hoja de Excel 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

Sub BuscaDato()
Dim cod As New Collection, celda, dato
Application.ScreenUpdating = False
On Error Resume Next
Set a = Sheets(«Hoja1»)
uf = a.Range(«B» & Rows.Count).End(xlUp).Row
ufb = a.Range(«A» & Rows.Count).End(xlUp).Row
r1 = «A2:A» & ufb
r2 = «B2:B» & uf
For Each celda In Range(r1)
cod.Add celda.Value, CStr(celda.Value)
Next celda
j = 1
For Each dato In cod
busco = dato
Set codigo = Range(r2).Find(busco, LookIn:=xlValues, LookAt:=xlWhole)
If Not codigo Is Nothing Then
dire = codigo.Row
Cells(codigo.Row, «B»).Interior.Color = 255
Cells(codigo.Row, «B»).Copy Destination:=Sheets(«Hoja2»).Range(«A» & j)
j = j + 1
Sheets(«Hoja2»).Cells.Interior.Color = xlNone
End If
Next dato

Application.ScreenUpdating = True
End Sub

Sub BorraColor()
Range(«B1:B1000»).Interior.Color = xlNone
Sheets(«Hoja2»).Cells.Clear
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 un comentario

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

Comentarios (75)

I’m the co-founder of JustCBD brand (justcbdstore.com) and I’m presently trying to grow my wholesale side of company. It would be great if anybody at targetdomain share some guidance . I thought that the most effective way to accomplish this would be to reach out to vape companies and cbd retailers. I was really hoping if anyone could suggest a reputable site where I can buy Global Vape Company Email List I am currently checking out creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Not exactly sure which one would be the very best selection and would appreciate any assistance on this. Or would it be much simpler for me to scrape my own leads? Suggestions?

Responder

There’s definately a great deal to learn about this issue. I really like all the points you’ve made.

Responder

I was able to find good advice from your blog posts.

Responder

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

Responder

Saved as a favorite, I like your web site!

Responder

Hello there! I could have sworn I’ve been to this blog before but after browsing through a few of the posts I realized it’s new to me. Nonetheless, I’m certainly happy I stumbled upon it and I’ll be book-marking it and checking back often!

Responder

Hi there! I understand this is sort of off-topic but I needed to ask.
Does running a well-established blog such as yours take a
lot of work? I am brand new to writing a blog
however I do write in my journal everyday. I’d like to
start a blog so I can share my own experience and feelings online.
Please let me know if you have any kind of recommendations or tips for brand new aspiring
bloggers. Appreciate it!

Responder

Hi there! Do you know if they make any plugins to assist with Search Engine Optimization?
I’m trying to get my blog to rank for some targeted keywords
but I’m not seeing very good gains. If you know
of any please share. Appreciate it!

Responder

Hi, I do think this is a great blog. I stumbledupon it 😉 I am going to return yet again since I saved as a favorite it. Money and freedom is the greatest way to change, may you be rich and continue to help other people.

Responder

This website truly has all the information and facts I needed concerning this subject and didn’t know who to ask.

Responder

I love reading a post that can make people think. Also, thank you for allowing for me to comment!

Responder

I like reading through a post that can make men and women think. Also, thank you for allowing me to comment!

Responder

It’s nearly impossible to find educated people for this subject, however, you seem like you know what you’re talking about! Thanks

Responder

Everything is very open with a precise clarification of the challenges. It was definitely informative. Your website is very useful. Many thanks for sharing!

Responder

I seriously love your blog.. Very nice colors & theme. Did you make this amazing site yourself? Please reply back as I’m hoping to create my own personal website and want to find out where you got this from or exactly what the theme is named. Cheers!

Responder

Achieving your fitness goal doesn’t need a certified personal trainer or an expensive gym memberships, it’s not hard to exercise at home. It is easy to go down a training and fitness rabbit hole, however, when you’re looking for the best home exercise equipment to outfit your personal home gym.

Responder

Good day! I simply want to offer you a huge thumbs up for the great info you have got here on this post. I am coming back to your website for more soon.

Responder

You could certainly see your expertise within the article you write.
The arena hopes for more passionate writers like you who aren’t afraid to say how they believe.
At all times go after your heart. 31muvXS cheap flights

Responder

There is certainly a great deal to know about this subject. I like all the points you’ve made.

Responder

magnificent publish, very informative. I wonder
why the other experts of this sector don’t understand this.

You should proceed your writing. I am sure, you have a great readers’ base already!

3aN8IMa cheap flights

Responder

I’m very pleased to find this site. I wanted to thank you for ones time just for this wonderful read!! I definitely appreciated every little bit of it and i also have you saved as a favorite to look at new information on your blog.

Responder

Can I simply just say what a comfort to discover someone who genuinely understands what they’re discussing on the web. You definitely realize how to bring a problem to light and make it important. More people should read this and understand this side of the story. I was surprised you’re not more popular given that you definitely have the gift.

Responder

Have you ever thought about creating an e-book or guest authoring on other blogs?
I have a blog centered on the same subjects you discuss
and would really like to have you share some stories/information. I know my subscribers would appreciate your work.
If you are even remotely interested, feel free to shoot me an e mail.
3aN8IMa cheap flights

Responder

Pretty! This was a really wonderful post. Thank you for supplying this info.

Responder

You’ve made some really good points there. I checked on the web for additional information about the issue and found most individuals will go along with your views on this website.

Responder

Just want to say your article is as astounding. The clearness in your post is just cool
and i can assume you’re an expert on this subject.
Fine with your permission let me to grab your RSS
feed to keep updated with forthcoming post. Thanks
a million and please continue the rewarding work. 34pIoq5 cheap flights

Responder

May I simply say what a relief to discover somebody that truly understands what they are discussing on the web. You definitely understand how to bring an issue to light and make it important. A lot more people really need to check this out and understand this side of the story. It’s surprising you’re not more popular because you definitely have the gift.

Responder

You made some really good points there. I looked on the internet for additional information about the issue and found most individuals will go along with your views on this website.

Responder

You made some really good points there. I checked on the internet for more info about the issue and found most individuals will go along with your views on this site.

Responder

An outstanding share! I’ve just forwarded this onto a co-worker who was conducting a little research on this. And he in fact bought me dinner simply because I discovered it for him… lol. So let me reword this…. Thank YOU for the meal!! But yeah, thanx for spending the time to discuss this matter here on your blog.

Responder

You’re so awesome! I do not believe I’ve truly read through anything like this before. So good to discover someone with unique thoughts on this subject. Seriously.. thanks for starting this up. This web site is one thing that is needed on the web, someone with a bit of originality!

Responder

I enjoy looking through a post that will make people think. Also, thanks for allowing for me to comment!

Responder

Amazing! This blog looks exactly like my old one! It’s on a totally different topic but it has pretty much the
same page layout and design. Great choice of colors!

Responder

This website really has all of the information I wanted about this subject and didn’t know who to ask.

Responder

I blog quite often and I really thank you for your content. This great article has really peaked my interest. I will bookmark your blog and keep checking for new details about once a week. I opted in for your RSS feed too.

Responder

This website was… how do I say it? Relevant!! Finally I have found something which helped me. Thanks!

Responder

Saved as a favorite, I like your web site!

Responder

Having read this I thought it was rather informative. I appreciate you finding the time and effort to put this short article together. I once again find myself spending a significant amount of time both reading and commenting. But so what, it was still worth it!

Responder

It’s difficult to find knowledgeable people on this subject, however, you seem like you know what you’re talking about! Thanks

Responder

I would like to thank you for the efforts you’ve put in writing this blog. I’m hoping to view the same high-grade blog posts by you in the future as well. In fact, your creative writing abilities has inspired me to get my own, personal blog now 😉

Responder

May I just say what a relief to find a person that truly understands what they’re discussing on the web. You definitely understand how to bring a problem to light and make it important. A lot more people have to check this out and understand this side of the story. It’s surprising you are not more popular given that you certainly have the gift.

Responder

Way cool! Some extremely valid points! I appreciate you penning this article plus the rest of the site is also very good.

Responder

Very good info. Lucky me I recently found your blog by accident (stumbleupon). I’ve bookmarked it for later!

Responder

bookmarked!!, I really like your blog!

Responder

May I just say what a comfort to discover someone who really understands what they’re talking about on the web. You certainly understand how to bring a problem to light and make it important. More people should read this and understand this side of the story. I was surprised that you aren’t more popular since you most certainly possess the gift.

Responder

I am very happy to read this. This is the type of manual that needs to be given and not the random misinformation that is at the other blogs. Appreciate your sharing this greatest doc.

Responder

Nice post. I learn something new and challenging on websites I stumbleupon on a daily basis. It’s always helpful to read through content from other writers and practice a little something from their web sites.

Responder

The very next time I read a blog, I hope that it doesn’t fail me as much as this particular one. I mean, Yes, it was my choice to read, nonetheless I genuinely believed you’d have something useful to say. All I hear is a bunch of moaning about something you can fix if you were not too busy looking for attention.

Responder

Hi there, I think your site might be having web browser compatibility problems. Whenever I look at your blog in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping issues. I just wanted to give you a quick heads up! Besides that, great blog!

Responder

Good day! Do you use Twitter? I’d like to follow you if that would be ok. I’m undoubtedly enjoying your blog and look forward to new posts.

Responder

You’re so interesting! I do not suppose I’ve read anything like that before. So wonderful to find another person with a few genuine thoughts on this subject matter. Seriously.. thanks for starting this up. This site is one thing that is required on the internet, someone with a bit of originality!

Responder

Pretty! This has been a really wonderful post. Thank you for supplying this information.

Responder

Everything is very open with a precise clarification of the issues. It was truly informative. Your site is very helpful. Many thanks for sharing!

Responder

Hello, i think that i saw you visited my weblog so i came to “return the favor”.I am trying to find things to enhance my site!I suppose its ok to use a few of your ideas!!

Responder

The very next time I read a blog, I hope that it doesn’t fail me as much as this particular one. I mean, Yes, it was my choice to read through, nonetheless I actually believed you would probably have something helpful to say. All I hear is a bunch of moaning about something that you could possibly fix if you weren’t too busy searching for attention.

Responder

This website really has all the information and facts I needed concerning this subject and didn’t know who to ask.

Responder

I blog often and I really thank you for your content. Your article has really peaked my interest. I’m going to book mark your blog and keep checking for new details about once a week. I opted in for your RSS feed too.

Responder

I used to be able to find good information from your blog posts.

Responder

Pretty! This has been an incredibly wonderful post. Thank you for supplying this info.

Responder

Hello there! I simply want to offer you a huge thumbs up for your great info you’ve got here on this post. I am coming back to your blog for more soon.

Responder

I enjoy looking through a post that will make people think. Also, thank you for allowing me to comment!

Responder

You ought to take part in a contest for one of the best websites on the net. I’m going to recommend this website!

Responder

I’m impressed, I must say. Rarely do I encounter a blog that’s equally educative and entertaining, and without a doubt, you’ve hit the nail on the head. The issue is something which too few people are speaking intelligently about. I’m very happy I stumbled across this during my search for something regarding this.

Responder

I wanted to thank you for this very good read!! I certainly enjoyed every little bit of it. I have you saved as a favorite to check out new stuff you post…

Responder

I wanted to thank you for this fantastic read!! I certainly enjoyed every little bit of it. I have you bookmarked to check out new stuff you post…

Responder

Having read this I thought it was rather enlightening. I appreciate you taking the time and energy to put this content together. I once again find myself spending way too much time both reading and posting comments. But so what, it was still worthwhile!

Responder

This website truly has all of the information I needed concerning this subject and didn’t know who to ask.

Responder

I want to to thank you for this good read!! I definitely enjoyed every little bit of it. I’ve got you bookmarked to look at new things you post…

Responder

Very good write-up. I absolutely appreciate this website. Continue the good work!

Responder

I’m amazed, I have to admit. Seldom do I encounter a blog that’s both equally educative and entertaining, and without a doubt, you have hit the nail on the head. The problem is something which not enough folks are speaking intelligently about. Now i’m very happy I stumbled across this during my hunt for something regarding this.

Responder

Having read this I thought it was very informative. I appreciate you spending some time and effort to put this information together. I once again find myself spending a significant amount of time both reading and commenting. But so what, it was still worthwhile!

Responder

This page certainly has all the info I wanted about this subject and didn’t know who to ask.

Responder

Next time I read a blog, Hopefully it does not fail me just as much as this particular one. I mean, I know it was my choice to read through, however I actually thought you would have something useful to talk about. All I hear is a bunch of moaning about something that you could possibly fix if you were not too busy searching for attention.

Responder

I want to to thank you for this good read!! I definitely enjoyed every bit of it. I have you saved as a favorite to look at new stuff you post…

Responder

Greetings! Very helpful advice in this particular post! It’s the little changes that produce the biggest changes. Thanks a lot for sharing!

Responder