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 una respuesta

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

Comentarios (26)

I have been browsing online greater than three hours today, but I never
discovered any fascinating article like yours.
It is beautiful value sufficient for me. Personally,
if all web owners and bloggers made excellent content as
you did, the internet shall be a lot more useful than ever before.

Responder

Hi! I know this is kind of off topic but I was wondering which blog
platform are you using for this site? I’m getting fed up of WordPress because I’ve had issues with hackers and
I’m looking at alternatives for another platform. I would be great if you could point me in the direction of a good
platform.

Responder

If you are going for best contents like myself, just go to see this web site daily as it offers quality contents, thanks

Responder

I reviewed this content frequently thus I merely made up my mind that it finally was the right time to post a comment to say keep on giving us more!

Responder

You have made some decent points there. I checked on the internet to find out more about the issue and found most people will go
along with your views on this site.

Responder

Hi there to all, the contents present at this website are
in fact awesome for people knowledge, well, keep up the nice work fellows.

Responder

Howdy just wanted to give you a quick heads up and let you know a few of the images aren’t
loading properly. I’m not sure why but I think its a linking issue.
I’ve tried it in two different internet browsers and both show the same results.

Responder

It is appropriate time to make some plans for the future
and it is time to be happy. I’ve read this post and if I could I wish to suggest you
few interesting things or advice. Maybe you could write next articles referring to this article.
I desire to read more things about it!

Responder

Definitely believe that which you stated. Your favourite justification appeared to be on the internet the simplest thing to understand of.
I say to you, I definitely get annoyed even as folks consider worries that they
just do not recognize about. You controlled to hit the nail
upon the highest as smartly as defined out the whole thing with no need side-effects
, other folks can take a signal. Will likely be back to get more.
Thank you

Responder

Just desire to say your article is as astonishing.
The clarity in your post is simply excellent and i can assume
you’re an expert on this subject. Fine with your permission let me to grab your feed to
keep updated with forthcoming post. Thanks a million and
please carry on the enjoyable work.

Responder

Appreciating the time and effort you put into your website and
detailed information you present. It’s awesome to come across a blog every once in a while that isn’t the same old rehashed material.
Excellent read! I’ve saved your site and I’m including your RSS feeds to my Google account.

Responder

As the admin of this website is working, no doubt very shortly it will be well-known, due to
its feature contents.

Responder

Today, I went to the beachfront with my children. I found a sea shell and gave it to
my 4 year old daughter and said «You can hear the ocean if you put this to your ear.» She placed the shell to her ear
and screamed. There was a hermit crab inside and it pinched
her ear. She never wants to go back! LoL I know this is totally off topic but I had to tell someone!

Responder

If you would like to increase your know-how only keep visiting this
website and be updated with the most recent news
posted here.

Responder

I am regular visitor, how are you everybody? This article posted at
this web site is genuinely fastidious.

Responder

It’s hard to find well-informed people in this particular topic, but you sound like you know what you’re talking about!
Thanks

Responder

Why people still use to read news papers when in this technological
world the whole thing is presented on web?

Responder

I every time spent my half an hour to read this website’s
posts every day along with a mug of coffee.

Responder

Hi would you mind letting me know which web host you’re utilizing?
I’ve loaded your blog in 3 completely different web browsers and I must say this
blog loads a lot quicker then most. Can you recommend a good web hosting provider
at a reasonable price? Thanks a lot, I appreciate it!

Responder

What i don’t realize is actually how you are now not really a lot
more well-liked than you might be right now. You’re very intelligent.

You realize therefore considerably in terms of this subject, made me personally believe it from a
lot of numerous angles. Its like women and men aren’t fascinated unless it is one
thing to accomplish with Woman gaga! Your individual
stuffs great. All the time handle it up!

Responder

If you are going for best contents like I do, just
visit this web page daily for the reason that it presents quality contents, thanks

Responder

I was recommended this blog by my cousin. I am not sure whether this post is written by him as no one else know such
detailed about my difficulty. You are amazing! Thanks!

Responder

I’m gone to say to my little brother, that he should also pay a visit this website on regular basis to take
updated from hottest reports.

Responder

I’ll right away grab your rss feed as I can’t in finding
your email subscription link or e-newsletter service. Do you have any?

Please allow me know so that I may subscribe. Thanks.

Responder

Today’s employers don’t review all of the 400 plus resumes submitted – per employment posting! Alternatively, they’ll immediately throw out ninety-five percent of them, if a resume does nothing to grab the reader’s interest.

Responder

Hi, i think that i noticed you visited my website so i came to go back the desire?.I am attempting to find
things to enhance my website!I suppose its good enough to make use of a few
of your ideas!!

Responder