Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Lista hojas libro y busca mediante criterios

El siguiente código busca en todas las hojas del libro, excepto en la primera, una vez encontrado los datos mediante bucles (parecido a función buscar V), los trae a la primer hoja, a su vez lista el nombre de la hoja en el cual aparece el dato encontrado.

Código a insertar en módulo

Sub Nomhoja()
‘Evito movimientos en la pantalla
Application.ScreenUpdating = False
‘Dimensiono variables
Dim fila, filah, conta As Integer
Dim hoja As Worksheet
Dim mihoja  As String
Dim valorcelda, concatena As String
fila = 2
filah = 2
conta = 0

‘Controlo errores
On Error Resume Next

‘para cada hoja del libro
For Each hoja In Worksheets
‘filahoja = filahoja + 1
 mihoja = hoja.Name
‘Realiza el bucle buscando un dato mientras la fila no esté vacia
 While Sheets(«art»).Cells(fila, 1) <> Empty And mihoja <> «Art»

    ‘ Realizó bucle en hoja donde busca datos
    While Sheets(mihoja).Cells(filah, 1) <> Empty And conta = 0
    dato1 = Sheets(«art»).Cells(fila, 1)
    dato2 = Sheets(mihoja).Cells(filah, 1)
     
      ‘Si el nombre de la columna uno de la hoja art es igual al nombre de la columna uno de la hoja correspondiente
       If dato1 = dato2 Then
 
      ‘es  decir si encontró el dato, copia la dirección en la hoja art y voy a la fila siguiente de la hoja correspondiente
      Sheets(«art»).Cells(fila, 3) = Sheets(mihoja).Cells(filah, 2)
     
      If Sheets(«art»).Cells(fila, 2) <> Empty Then
       ‘Sheets(«art»).Cells(fila, 2) = mihoja
       valorcelda = Sheets(«art»).Cells(fila, 2)
       concatena = valorcelda & «,» & mihoja
       Sheets(«art»).Cells(fila, 2) = concatena
       Else
       Sheets(«art»).Cells(fila, 2) = mihoja
       End If
      ‘Si encontro el dato hago que el contador sea 1 para que no busque más
      conta = 1
     
      Else
     ‘Si no encontro el dato aumento la fila de la hoja address para seguir buscando hasta el final
      filah = filah + 1
      End If
    Wend
   
‘Aumento en uno la fila para seguir buscando el dato siguiente de la hoja art
fila = fila + 1
‘Vuelvo filah y contador a su valor de origen
filah = 2
conta = 0
‘sigo bucle
Wend
‘Vuelvo las variables a su valor de origen
fila = 2
filah = 2
conta = 0
‘Proxima hoja
Next

‘Vuelvo movimientos de la pantalla a su estado original
Application.ScreenUpdating = 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.


(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 (33)

I’m the co-founder of JustCBD Store label (justcbdstore.com) and I’m presently seeking to develop my wholesale side of business. I really hope that someone at targetdomain can help me 🙂 I thought that the best way to do this would be to connect to vape stores and cbd stores. I was hoping if someone could suggest a reliable web-site where I can get CBD Shops Sales Leads I am currently checking out creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Unsure which one would be the best solution and would appreciate any advice on this. Or would it be simpler for me to scrape my own leads? Suggestions?

Responder

тонкость процедуры ликвидация ООО или смежная процедура субсидиарная ответственность учредителя

Responder

The next time I read a blog, I hope that it doesn’t fail me just as much as this one. After all, Yes, it was my choice to read through, nonetheless I genuinely believed you would probably have something helpful to say. All I hear is a bunch of moaning about something that you can fix if you were not too busy searching for attention.

Responder

Aw, this was an incredibly good post. Taking a few minutes and actual effort to create a great article… but what can I say… I put things off a whole lot and don’t seem to get nearly anything done.

Responder

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

Responder

Spot on with this write-up, I absolutely believe this amazing site needs far more attention. I’ll probably be returning to see more, thanks for the advice!

Responder

Great post. I am facing many of these issues as well..

Responder

Good write-up. I certainly appreciate this site. Keep writing!

Responder

Greetings! Very useful advice within this article! It’s the little changes that make the greatest changes. Thanks for sharing!

Responder

It’s nearly impossible to find experienced people about this subject, but you sound like you know what you’re talking about! Thanks

Responder

The next time I read a blog, Hopefully it doesn’t disappoint me as much as this one. I mean, I know it was my choice to read through, nonetheless I truly thought you would have something helpful to talk about. All I hear is a bunch of complaining about something that you could possibly fix if you weren’t too busy seeking attention.

Responder

I’m impressed, I must say. Rarely do I encounter a blog that’s both educative and amusing, and without a doubt, you’ve hit the nail on the head. The problem is something which not enough people are speaking intelligently about. I’m very happy I found this in my hunt for something concerning this.

Responder

Greetings! Very helpful advice in this particular article! It’s the little changes which will make the most important changes. Thanks a lot for sharing!

Responder

Saved as a favorite, I love your website!

Responder

Everything is very open with a really clear clarification of the issues. It was truly informative. Your website is extremely helpful. Thanks for sharing!

Responder

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

Responder

I couldn’t resist commenting. Very well written!

Responder

Hello there! This blog post couldn’t be written any better! Reading through this post reminds me of my previous roommate! He continually kept talking about this. I’ll forward this article to him. Pretty sure he’s going to have a great read. Thank you for sharing!

Responder

You’re so cool! I don’t think I have read through anything like this before. So nice to discover another person with some unique thoughts on this issue. Seriously.. thanks for starting this up. This site is something that is needed on the internet, someone with a little originality!

Responder

The very next time I read a blog, I hope that it does not fail me just as much as this particular one. After all, Yes, it was my choice to read, nonetheless I truly believed you would probably have something interesting to talk about. All I hear is a bunch of whining about something you could possibly fix if you weren’t too busy looking for attention.

Responder

I was excited to discover this site. I want to to thank you for your time due to this fantastic read!! I definitely savored every part of it and I have you bookmarked to look at new things on your web site.

Responder

This is a topic which is near to my heart… Many thanks! Where are your contact details though?

Responder

Your style is very unique compared to other people I have read stuff from. I appreciate you for posting when you’ve got the opportunity, Guess I’ll just book mark this blog.

Responder

An outstanding share! I’ve just forwarded this onto a colleague who has been doing a little homework on this. And he actually bought me breakfast simply because I discovered it for him… lol. So allow me to reword this…. Thanks for the meal!! But yeah, thanks for spending the time to discuss this matter here on your site.

Responder

It’s hard to come by educated people on this topic, but you seem like you know what you’re talking about! Thanks

Responder

This site was… how do you say it? Relevant!! Finally I have found something that helped me. Kudos!

Responder

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

Responder

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

Responder

Howdy! This post could not be written any better! Reading through this post reminds me of my previous roommate! He always kept preaching about this. I am going to send this article to him. Fairly certain he’s going to have a good read. I appreciate you for sharing!

Responder

Everything is very open with a very clear clarification of the issues. It was really informative. Your website is very helpful. Many thanks for sharing!

Responder

It is in point of fact a nice and helpful piece of info.
I’m glad that you shared this useful info with us. Please stay us informed like this.
Thank you for sharing. adreamoftrains best web hosting

Responder

May I simply just say what a comfort to uncover somebody who really understands what they are discussing on the internet. You definitely realize how to bring a problem to light and make it important. A lot more people should read this and understand this side of the story. I can’t believe you are not more popular because you certainly have the gift.

Responder

Ahaa, its fastidious discussion regarding this piece of writing at
this place at this blog, I have read all that, so at this
time me also commenting here.

Responder