Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como usar If Then Else en macro

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

La instrucción If …Then…Else se utiliza para ejecutar uno o varios códigos determinados,  pero dependiendo de una condición; se puede anidar tantas funciones If …Then…Else como se necesiten, pero es más eficiente cuando se debe analizar muchas condiciones utilizar Select Case, que se verá en otro post.

Cuando se debe ejecutar una sola instrucción después de evaluar una condición se puede usar una forma sencilla como: If condición Then código a realizar.

Antes de continuar, recomiendo que leas un excelente libro sobre Excel el que te ayudará operar las planillas 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.

Ahora bien, cuando después de evaluar la condición, se deben realizar varias condiciones se debe usar la siguiente forma:

If condición Then

código 

Else (opcional)

código

End If

En caso que no se cumpla la condición se puede establecer que se debe realizar en caso contrario, para ello se utiliza Else; es decir se evalúa la condición en caso verdadero se ejecuta un código en caso contrario (Else) se ejecuta otro código, para terminar se debe utilizar End If, caso contrario el código podría no comportarse como se requiere o podría producir un error de sintaxis.

  
En los siguientes link encontrarás ejemplos del uso de If …Then…Else:

Instrucción If …Then…Else anidada

Formulario Ingreso y Egreso de Stock, encontraras en varias partes del código el uso de la Instrucción If …Then…Else 

En este link encontrarás otra gran cantidad de ejemplos que usan la Instrucción If …Then…Else 

El código que se encuentra a continuación pertenece a un ejemplo de macro que tiene por finalidad llenar un combobox y luego sumar los datos repetidos, descargar desde este link, se podrá observar también el uso de la instrucción bajo estudio, donde también se usa otra instrucción For…Next.

Suscribe al blog para recibir en tu correo todas las actualizaciones, dispones también de un canal de You Tube donde encontrarás explicaciones de macros con mayor detalle.

Suscribe a nuestro canal de You Tube para recibir en tu correo vídeos explicativos sobre macros interesantes, como  por ejemplo formulario que crea un listado de todas las hojas para poder luego seleccionarlas, buscar en listbox mientras escribes en textbox, ordenar hojas libro excel por su nombre, conectar Excel con Access y muchos ejemplos más.

Código que se inserta en un módulo

Public nomlibro, dire, dire1, p As String 
Private Sub ComboBox1_Change()
Range(«r2:u3») = ClearContents
If ComboBox2 = Empty Then
MsgBox («Debe seleccionar Nombre»), vbInformation, «AVISO»
ComboBox2.SetFocus
Exit Sub
End If
ComboBox2_Change
End Sub
Private Sub ComboBox2_Change()
On Error Resume Next
Application.ScreenUpdating = False
Dim uf, ufcat, filadir, filacat, contad, contap, contadc, contamc As Integer
Range(«r2:u3») = ClearContents

If ComboBox1 = Empty Then
MsgBox («Debe seleccionar Nombre»), vbInformation, «AVISO»
ComboBox1.SetFocus
Exit Sub
End If

dire = ComboBox2
nomlibro = ActiveWorkbook.Name
p = ActiveWorkbook.Path
dire1 = p & «» & nomlibro

If dire = dire1 Then
        TextBox1 = Clear
        TextBox2 = Clear
        TextBox3 = Clear
        TextBox4 = Clear
        
        
        contad = 0
        contap = 0
        contadc = 0
        contamc = 0
        perfind = ComboBox1
        ufcat = Sheets(«Proyectos»).Range(«H» & Rows.Count).End(xlUp).Row
        
        For i = 2 To ufcat
        a = Sheets(«Proyectos»).Cells(i, 6)
        If a = perfind Then contad = contad + 1
        If Sheets(«Proyectos»).Cells(i, 7) = perfind And Sheets(«Proyectos»).Cells(i, 8) = Sheets(«Proyectos»).Cells(1, 19) Then contap = contap + 1
        If Sheets(«Proyectos»).Cells(i, 7) = perfind And Sheets(«Proyectos»).Cells(i, 8) = Sheets(«Proyectos»).Cells(1, 20) Then contadc = contadc + 1
        If Sheets(«Proyectos»).Cells(i, 7) = perfind And Sheets(«Proyectos»).Cells(i, 8) = Sheets(«Proyectos»).Cells(1, 21) Then contamc = contamc + 1
        Next i
        
        If nomlibro <> dire Then
        Sheets(«Proyectos»).Cells(2, 18) = contad
        Sheets(«Proyectos»).Cells(2, 19) = contap
        Sheets(«Proyectos»).Cells(2, 20) = contadc
        Sheets(«Proyectos»).Cells(2, 21) = contamc
        
        Else
        
        Sheets(«Proyectos»).Cells(3, 18) = contad
        Sheets(«Proyectos»).Cells(3, 19) = contap
        Sheets(«Proyectos»).Cells(3, 20) = contadc
        Sheets(«Proyectos»).Cells(3, 21) = contamc
        End If
        
        TextBox1 = contad
        TextBox2 = contap
        TextBox3 = contadc
        TextBox4 = contamc
Else
  Application.Workbooks.Open dire
  Sheets(«Proyectos»).Select
        TextBox1 = Clear
        TextBox2 = Clear
        TextBox3 = Clear
        TextBox4 = Clear
            
        contad = 0
        contap = 0
        contadc = 0
        contamc = 0
        
        perfind = ComboBox1
        ufcat = Sheets(«Proyectos»).Range(«H» & Rows.Count).End(xlUp).Row
        
        For i = 2 To ufcat
        a = Sheets(«Proyectos»).Cells(i, 6)
        If a = perfind Then contad = contad + 1
        If Sheets(«Proyectos»).Cells(i, 7) = perfind And Sheets(«Proyectos»).Cells(i, 8) = Sheets(«Proyectos»).Cells(1, 19) Then contap = contap + 1
        If Sheets(«Proyectos»).Cells(i, 7) = perfind And Sheets(«Proyectos»).Cells(i, 8) = Sheets(«Proyectos»).Cells(1, 20) Then contadc = contadc + 1
        If Sheets(«Proyectos»).Cells(i, 7) = perfind And Sheets(«Proyectos»).Cells(i, 8) = Sheets(«Proyectos»).Cells(1, 21) Then contamc = contamc + 1
        Next i
        
        If dire = dire1 Then
        Sheets(«Proyectos»).Cells(2, 18) = contad
        Sheets(«Proyectos»).Cells(2, 19) = contap
        Sheets(«Proyectos»).Cells(2, 20) = contadc
        Sheets(«Proyectos»).Cells(2, 21) = contamc
        
        Else
        ActiveWorkbook.Close False
        Sheets(«Proyectos»).Cells(3, 18) = contad
        Sheets(«Proyectos»).Cells(3, 19) = contap
        Sheets(«Proyectos»).Cells(3, 20) = contadc
        Sheets(«Proyectos»).Cells(3, 21) = contamc
        End If
        
        TextBox1 = contad
        TextBox2 = contap
        TextBox3 = contadc
        TextBox4 = contamc
End If
Application.ScreenUpdating = True
End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
ComboBox1.Clear
Sheets(«Nom»).Select
Range(«A2»).Select
While ActiveCell <> Empty
ComboBox1.AddItem ActiveCell
ActiveCell.Offset(1, 0).Select
Wend
ComboBox2.Clear
Sheets(«Archivo»).Select
Range(«A2»).Select
While ActiveCell <> Empty
ComboBox2.AddItem ActiveCell
ActiveCell.Offset(1, 0).Select
Wend
Sheets(«Proyectos»).Select
Application.ScreenUpdating = True
End Sub

Código a insertar en un módulo

Sub cargaform()
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.


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

Going which will help much specially for competitive
keywords. Are you having problems getting equally as much targeted visitors to your site as you would
like? These are people that claim that advertising does not work.

Feel free to surf to my blog :: joker123 apk

Responder

Verschreibung Viagra Cialis Levitra boycle https://ascialis.com/# – generic cialis no prescription Fermsossesof Priligy Forum Uk juibly Cialis guarmoma cialis comprar receta

Responder

you’re actually a excellent webmaster. The site loading speed is incredible.
It sort of feels that you’re doing any distinctive trick.

Moreover, The contents are masterwork. you’ve done a magnificent activity
in this subject!

Responder

Start letting planet knows about you way before your start.
Have a subscriber channel that is appropriately tagged and stay
busy. In case, of yams and sweet potatoes, they have
a good content of ‘beta carotene’.

Responder

I am the owner of JustCBD brand (justcbdstore.com) and I am currently planning to develop my wholesale side of business. I am hoping someone at targetdomain share some guidance ! I thought that the most effective way to accomplish this would be to talk to vape stores and cbd retailers. I was really hoping if anybody at all could suggest a dependable web-site where I can purchase Vape Shop Leads I am presently taking a look at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. On the fence which one would be the most ideal selection and would appreciate any support on this. Or would it be much simpler for me to scrape my own leads? Ideas?

Responder

I’m the owner of JustCBD Store label (justcbdstore.com) and I’m presently aiming to develop my wholesale side of company. I am hoping someone at targetdomain is able to provide some guidance ! I thought that the most suitable way to do this would be to talk to vape companies and cbd retail stores. I was really hoping if anybody could suggest a trusted site where I can purchase CBD Shops B2B Email Marketing List I am presently looking at creativebeartech.com, theeliquidboutique.co.uk and wowitloveithaveit.com. Unsure which one would be the most suitable selection and would appreciate any assistance on this. Or would it be simpler for me to scrape my own leads? Suggestions?

Responder

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

Responder

Hi, I do think this is a great website. I stumbledupon it 😉 I may return once again since I book marked it. Money and freedom is the best way to change, may you be rich and continue to guide others.

Responder

Cytotec Diu Pharmacie En Ligne Immassymet https://bbuycialisss.com/# – Cialis Telaseangego cialis generique effet secondaire emogue cialis generic release date woormuriousy Kamagra Oral Jelly Kaufen Deutschland

Responder

Hello there! This is my first visit to your blog!
We are a collection 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!

Responder

You are so interesting! I do not suppose I’ve truly read anything like this before. So nice to discover somebody with genuine thoughts on this topic. Seriously.. many thanks for starting this up. This website is something that is required on the internet, someone with some originality!

Responder

Oh my goodness! Awesome article dude! Many thanks, However I am having difficulties with your RSS. I don’t know why I cannot subscribe to it. Is there anybody getting similar RSS issues? Anybody who knows the solution will you kindly respond? Thanks!!

Responder

I really love your blog.. Great colors & theme. Did you make this amazing site yourself? Please reply back as I’m trying to create my very own website and would like to learn where you got this from or what the theme is named. Thank you!

Responder

I’m impressed, I must say. Rarely do I encounter a blog that’s equally educative and amusing, and let me tell you, you have hit the nail on the head. The issue is something too few men and women are speaking intelligently about. I am very happy that I stumbled across this during my search for something relating to this.

Responder

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

Responder

Hello! I just wish to give you a huge thumbs up for the excellent information you have got here on this post. I’ll be returning to your blog for more soon.

Responder

Aw, this was a really good post. Finding the time and actual effort to generate a great article… but what can I say… I put things off a lot and never seem to get nearly anything done.

Responder

I’m amazed, I have to admit. Seldom do I come across a blog that’s both equally educative and amusing, and let me tell you, you have hit the nail on the head. The problem is something too few folks are speaking intelligently about. I’m very happy I came across this during my hunt for something relating to this.

Responder

Hello there, I do believe your blog could be having internet browser compatibility problems. Whenever I look at your web site in Safari, it looks fine but when opening in Internet Explorer, it has some overlapping issues. I just wanted to provide you with a quick heads up! Besides that, wonderful website!

Responder

I like reading through an article that will make people think. Also, thank you for allowing for me to comment!

Responder

Greetings! Very useful advice within this post! It’s the little changes which will make the greatest changes. Thanks a lot for sharing!

Responder

bookmarked!!, I really like your website!

Responder

You’re so interesting! I don’t think I’ve read through a single thing like that before. So wonderful to find another person with some unique thoughts on this issue. Really.. thank you for starting this up. This web site is something that is needed on the web, someone with some originality!

Responder

Hello, There’s no doubt that your web site could be having browser compatibility issues. Whenever I look at your web site in Safari, it looks fine however, when opening in IE, it’s got some overlapping issues. I simply wanted to provide you with a quick heads up! Apart from that, excellent blog!

Responder

Hello there! I could have sworn I’ve visited this website before but after going through a few of the articles I realized it’s new to me. Nonetheless, I’m certainly happy I stumbled upon it and I’ll be bookmarking it and checking back frequently!

Responder

I really like reading an article that can make people think. Also, many thanks for permitting me to comment!

Responder

After I originally left a comment I seem to have clicked on the -Notify me when new comments are added- checkbox and now every time a comment is added I receive four emails with the exact same comment. Is there an easy method you can remove me from that service? Thanks!

Responder

Howdy! I simply wish to give you a huge thumbs up for the excellent information you have got here on this post. I will be coming back to your website for more soon.

Responder

Thank you a bunch for sharing this with all of us you actually
understand what you are talking about! Bookmarked.
Kindly also visit my web site =). We may have a link change agreement among us

Responder

It’s hard to come by well-informed people on this topic, but you sound like you know what you’re talking about! Thanks

Responder

Oh my goodness! Awesome article dude! Thank you so much, However I am going through troubles with your RSS. I don’t understand the reason why I am unable to join it. Is there anybody else having identical RSS problems? Anybody who knows the answer can you kindly respond? Thanx!!

Responder

I’m excited to uncover this website. I want to to thank you for your time just for this fantastic read!! I definitely savored every part of it and i also have you book marked to see new things on your web site.

Responder

Buy Amoxicillin Antibiotics Without Prescription boycle https://ascialis.com/ – Cialis Fermsossesof Priligy Furiex juibly Cialis guarmoma overnight generic cialis

Responder

Good post. I learn something totally new and challenging on sites I stumbleupon every day. It will always be useful to read through content from other writers and practice something from other sites.

Responder

May I simply just say what a relief to find someone who truly understands what they’re discussing online. You actually understand how to bring a problem to light and make it important. More people should look at this and understand this side of your story. I was surprised that you aren’t more popular because you certainly possess the gift.

Responder

If you are going for finest contents like myself, simply pay a quick visit
this website all the time as it provides quality contents, thanks

Responder

Your style is so unique in comparison to other folks I’ve read stuff from. Thanks for posting when you’ve got the opportunity, Guess I will just book mark this web site.

Responder

An intriguing discussion is definitely worth comment. I believe that you should publish more about this issue, it may not be a taboo subject but generally people don’t discuss these topics. To the next! All the best!!

Responder

Very good post. I am facing a few of these issues as well..

Responder

This is a topic which is near to my heart… Best wishes! Exactly where are your contact details though?

Responder

tesco viagra online uk generic viagra
medicine in science medicine dictionary
american healthcare blood pressure ranges
symptoms of hiv cdc find dr near me

Responder

An outstanding share! I’ve just forwarded this onto a co-worker who has been doing
a little homework on this. And he actually bought me dinner because I discovered it
for him… lol. So allow me to reword this…. Thank YOU
for the meal!! But yeah, thanks for spending time to talk about this matter here
on your web page. adreamoftrains web hosting services

Responder

Right here is the perfect website for anyone who wishes to find out about this topic. You know a whole lot its almost tough to argue with you (not that I actually will need to…HaHa). You definitely put a new spin on a topic that’s been written about for a long time. Wonderful stuff, just excellent!

Responder