Saltar al contenido
PROGRAMAR EN VBA MACROS DE EXCEL

Como recorrer una cadena de caracteres


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

En varios post se ha apelado a los bucles para recorrer filas en busca de datos o para escribir datos en celdas, ya sea con la estructura For … Next; While … WendDo … Loop; For Each … Next todas las anteriores estructuras permiten realizar un bucle para recorrer datos u objetos.

En esta oportunidad voy a mostrar como recorrer una cadena de texto o string; una cadena de texto o string se puede definir como un conjunto de letras, espacios, símbolos; es decir por ejemplo si en la celda A1 tengo el siguiente dato «En programarexcel.com podrás descargar cientos de macros gratis»; todo lo encerrado entre comillas se puede denominar una cadena de texto o string en este caso es una frase compuesta por varias palabra y dichas palabras por varios caracteres y espacios, la macro permite recorrer la cadena de caracteres o string, es decir cada uno de las letras, número o espacios que forman la cadena o string.

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.

  
Es necesario descargar el archivo de ejemplo para poder entender en forma más sencilla lo que aquí se explica, es gratis y no tiene ninguna restricción, con dicho ejemplo podrás como dice el post recorrer una cadena de caracteres mediante una macro, ello en pos de buscar algún carácter en especial o encontrar una subcadena de caracteres en el string principal.

En el libro de ejemplo se podrá observar un la columna B un listado de Compañias Aseguradoras, cuyo nombre se encuentra en MAYÚSCULA; el objeto del ejemplo es recorrer la cadena de caracteres de cada una de las filas donde se encuentra el nombre de la compañia aseguradora, hasta la última fila con datos; determinando la macro al recorrer la cadena o string si el carácter coincide con la letra «A» Mayúscula; en caso de coincidir el carácter, que el bucle recorre en ese momento, con la letra mencionada la cambia por «a» MINÚSCULA.

En otras palabras la macro recorrerá el nombre de todas las compañías aseguradoras que están en la columna B, cambiando la letra «A» mayúscula por la letra «a» minúscula, en el ejemplo se podrá aprender como determinar el largo de una cadena con el siguiente código:

Lar = Len(Tex)

Como extraer caracteres de una cadena y asignarlos a una variable como por ejemplo:

Car = Mid(Tex, i, 1)

Como reemplazar texto son la siguiente función de VBA:

a.Range(«B» & x) = Replace(Tex1, Car, «a»)

Como cambiar un texto a Mayúscula, en este ejemplo no figura, pero cambiar a minúscula se usa LCase en vez de Ucase:

Range(«B» & x) = UCase(Range(«B» & x))

Aunque en este ejemplo no se usa es necesario cuando se trabaja con cadena de textos saber ciertas funciones que permitirán trabajar con cadenas de texto o string, si quieres profundizar visita el link que mostrará un tutorial sobre cadenas de texto o string.

Para lograr  cambiar la letra «A» mayúscula por la «a» minúscula, se realiza un bucle con la estructura For … Next, determinando el largo de la cadena de texto y recorriendo del primer al último carácter determinado por la función Len que cuenta la cantidad de caracteres que tiene la cadena.

El bucle se hace de la siguiente manera.

For i = 1 To Lar
Car = Mid(Tex, i, 1)
Tex1 = Mid(Tex, 1, i)
If Car = «A» Then
‘forma más fácil de reemplazar todos los caracteres en la cadena de texto
‘a.Range(«B» & x) = Replace(Tex, «A», «a»)
a.Range(«B» & x) = Replace(Tex1, Car, «a»)
End If
Next i
Next x

El bucle al recorre de la primer a la última carácter de la cadena de texto en la variable Car se asigna el carácter que se va recorriendo en ese momento para ello con la función Mid se va extrayendo el carácter que recorre el bucle en ese momento; la variable Text1 va acumulando todos los caracteres recorridos hasta el momento; posteriormente con If se determina si el carácter que está en la variable Car es = a «A» en caso positivo reemplaza en la cadena Text1 (que acumula todos los caracteres recorridos por el buce), la letra «A» por «a»; luego sigue haciendo lo mismo hasta el último carácter y hasta la última fila con datos, recomiendo bajar archivo y ver vídeo tutorial sobre el ejemplo presentado.

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 Recorre fila buscando y comparando datos de dos columnas en hojas distintasbuscar en listbox mientras escribes en textbox, como crear una factura o sale invoice y grabar guardar PDF XLS y enviar por mailconectar Excel con Access 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);
}
}

Otros vídeos que pueden ser de interés:

   

Código que se inserta en un módulo
Sub RecorreCaracteres()
Dim Tex As Variant, Car As Variant, Lar As Integer
Application.ScreenUpdating = False
On Error Resume Next
Set a = Sheets(«Hoja1»)
uf = a.Range(«A» & Rows.Count).End(xlUp).Row
For x = 2 To uf
Tex = a.Range(«B» & x)
Lar = Len(Tex)

For i = 1 To Lar
Car = Mid(Tex, i, 1)
Tex1 = Mid(Tex, 1, i)
If Car = «A» Then
‘forma más fácil de reemplazar todos los caracteres en la cadena de texto
‘a.Range(«B» & x) = Replace(Tex, «A», «a»)
a.Range(«B» & x) = Replace(Tex1, Car, «a»)
End If
Next i
Next x
Application.ScreenUpdating = True
End Sub

Sub mayus()
uf = Range(«A» & Rows.Count).End(xlUp).Row
For x = 2 To uf
Range(«B» & x) = UCase(Range(«B» & x))
Next x
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 (43)

Hello There. I found your weblog the use of msn. This is a really smartly written article.
I’ll make sure to bookmark it and return to learn extra of your useful
info. Thank you for the post. I will certainly return.

Responder

I am extremely inspired along with your writing abilities
as neatly as with the format in your blog. Is this a paid subject matter or
did you customize it your self? Anyway stay up
the excellent high quality writing, it’s uncommon to see a nice
blog like this one today..

Responder

Magnificent beat ! I would like to apprentice while you amend your website, how could i subscribe for
a blog site? The account aided me a acceptable deal. I had been tiny bit acquainted of this your broadcast offered bright clear concept

Responder

Hi there mates, its fantastic piece of writing about cultureand
entirely explained, keep it up all the time.

Responder

Very great post. I just stumbled upon your blog and wanted to
mention that I have truly enjoyed surfing around your weblog posts.

In any case I’ll be subscribing in your feed and I hope you write again very soon!

Responder

That is the fitting weblog for anyone who needs to find out about this topic. You realize so much its almost exhausting to argue with you (not that I truly would want…HaHa). You undoubtedly put a brand new spin on a subject thats been written about for years. Nice stuff, just great!

Responder

Pretty nice post. I just stumbled upon your weblog and wished to say that I’ve truly enjoyed surfing around your blog posts.
In any case I’ll be subscribing to your feed and I hope you write again very soon!

Responder

electric ranges are very convenient to use compared to gas ranges-

Responder

Heya i’m for the first time here. I came across this board and I find It
truly useful & it helped me out a lot. I hope to give something back and aid
others like you aided me.

Responder

Excellent, what a website it is! This weblog presents valuable information to us, keep it up.

Responder

I always used to read paragraph in news papers but
now as I am a user of net so from now I am using net for content, thanks to web.

Responder

You can definitely see your expertise in the work you write.
The world hopes for even more passionate writers such as you who aren’t afraid to mention how they believe.
At all times follow your heart.

Responder

I like the valuable info you provide in your articles.
I will bookmark your weblog and check again here frequently.
I am quite certain I’ll learn many new stuff right here!
Best of luck for the next!

Responder

I do accept as true with all of the ideas you’ve offered for your post.

They’re very convincing and can definitely work.

Nonetheless, the posts are too short for newbies. May just you please prolong them a bit from subsequent time?
Thanks for the post.

Responder

Hello, its fastidious article about media print, we all understand media is a great source of facts.

Responder

Hi, this weekend is fastidious in support of
me, because this point in time i am reading this
fantastic educational paragraph here at my home.

Responder

What a data of un-ambiguity and preserveness of valuable familiarity regarding unpredicted feelings.

Responder

Hello there, You’ve done a great job. I will certainly digg it and
personally suggest to my friends. I am sure they will be benefited from this website.

Responder

Hi there, I found your site via Google even as searching for a comparable topic,
your website got here up, it seems great.

I’ve bookmarked it in my google bookmarks.
Hello there, simply become aware of your weblog through Google, and located
that it is truly informative. I’m going to be careful for brussels.
I’ll appreciate should you proceed this in future. A lot of
people might be benefited from your writing. Cheers!

Responder

If you are going for finest contents like I do,
just go to see this website every day since it gives feature contents,
thanks

Responder

What’s up colleagues, its fantastic piece of writing concerning
educationand fully explained, keep it up all the time.

Responder

Fine way of explaining, and pleasant article to take
facts regarding my presentation subject, which i
am going to present in school.

Responder

I am regular visitor, how are you everybody? This piece of writing posted at this web site is
truly fastidious.

Responder

Hey there are using WordPress for your site platform?
I’m new to the blog world but I’m trying to get started and create my own. Do
you require any html coding expertise to make your own blog?
Any help would be greatly appreciated!

Responder

Thanks for sharing your thoughts. I truly appreciate your efforts and I am waiting for your next post thanks once again.

Responder

What’s up everyone, it’s my first pay a visit at this website, and article is
in fact fruitful designed for me, keep up posting such posts.

Responder

I think this is among the most significant information for me.

And i am glad reading your article. But want to remark on some general things,
The web site style is ideal, the articles is really great : D.
Good job, cheers

Responder

Having read this I believed it was really informative. I appreciate you finding the time and effort to put this informative article together.
I once again find myself personally spending a lot of time both reading and commenting.

But so what, it was still worthwhile!

Responder

Incredible points. Solid arguments. Keep up the
great spirit.

Responder

Appreciate the recommendation. Let me try it out.

Responder

I have read several excellent stuff here. Definitely worth bookmarking for revisiting.
I wonder how much attempt you put to create this kind of wonderful informative site.

Responder

Pretty nice post. I just stumbled upon your weblog and wanted to say that I have really
enjoyed surfing around your blog posts. In any case I will be subscribing to
your rss feed and I hope you write again soon!

Responder

Asking questions are really nice thing if you are not understanding something fully,
but this post offers fastidious understanding yet.

Responder

I don’t know whether it’s just me or if everybody else experiencing problems with your blog.
It looks like some of the written text on your posts are running
off the screen. Can somebody else please comment and let me know if this
is happening to them as well? This may be a problem with my internet browser because I’ve
had this happen previously. Appreciate it

Responder

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

Responder

Simply want to say your article is as amazing.
The clearness in your submit is just spectacular and that i can think you are an expert in this subject.
Well along with your permission let me to take hold of your RSS feed to stay up to date with
approaching post. Thank you a million and
please continue the enjoyable work.

Responder

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

Responder

This post is really a good one it helps new the web people,
who are wishing for blogging.

Responder

It’s going to be ending of mine day, however before ending
I am reading this great post to increase my know-how.

Responder

You’re so interesting! I do not suppose I’ve truly read something like
this before. So good to find someone with some genuine thoughts on this issue.

Really.. thanks for starting this up. This site is something that’s needed on the
web, someone with a little originality!

Responder

Stunning story there. What occurred after? Thanks!

Responder

Undeniably believe that which you stated. Your favorite reason appeared to be on the net the simplest thing to be aware of.
I say to you, I certainly get annoyed while people think
about worries that they plainly do not know about.
You managed to hit the nail upon the top and defined
out the whole thing without having side effect , people can take a signal.

Will probably be back to get more. Thanks

Responder

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

Responder