martes, 17 de septiembre de 2013

VBA: El operador de comparación LIKE.

A través de un comentario un lector preguntaba por la manera de verificar si un texto estaba contenido dentro de una celda:

...Si de esta forma la celda que tiene la palabra exacta "Color" es la que cambiara de color. Para cambiar de color la celda solo que la palabra 'Color' esta dentro de una frase y/o conjunto de otras palabras....


Se trata por tanto de comprobar mediante algún operador o instrucción si nuestras celdas contienen alguna otra cadena. En particular hablaremos de comodines, de operadores de comparación (like) y de la función InStr.

Analizaremos, en primer lugar, cuáles son los caracteres comodín empleados en nuestros procedimientos de VB:
1 - Cierre interrogación (?): representa cualquier carácter único.
2 - Asterísco (*): puede indicar cero o más caracteres (indeterminados).
3 - Almohadilla (#): Cualquier dígito único
4 - Cadena entre corchetes ([lista de caracteres]): Cualquier carácter único en la lista dada de caracteres.
Esta 'lista de caracteres' puede especificar un rango de caracteres mediante el uso de un guión (-) para separar los límites superiores e inferiores del rango, pero deben aparecer en orden de clasificación ascendente (por ejemplo, A-Z ó 0-100).


Por otra parte la función InStr nos retorna un tipo Long que especifica la posición de la primera aparición de una cadena dentro de otra.
La sintáxis de la función es:
InStr([inicio, ]CadenaDondeSeBusca, CadenaBuscada[, TipoComparación])


Por último el operador Like, con el que funcionan perfectamente nuestros comodines, y que utilizamos para comparar dos cadenas de caracteres. A la hora de emplear nuestros caracteres comodín, debemos tener presente que es posible utilizar un grupo de uno o más caracteres (en nuestra 'lista de caracteres') entre corchetes ([]) para establecer una coincidencia con un carácter cualquiera de la cadena buscada.

Una vez repasados los conceptos esenciales a la hora de comparar cadenas en nuestras macros, planteemos un ejemplo. Tenemos en nuestra hoja de cálculo en el rango A1:A7

VBA: El operador de comparación LIKE.


Se trata de contruir un sencillo proceso que identifique las celdas que contienen la cadena 'Excel'. Y nuestro primer procedimiento será con el operador Like y algún comodín. Asi que, en un módulo del Editor de VBA añadiremos las líneas de nuestra macro.

Sub OperadorLike()
Dim celda As Object
Dim rng As Range
Set rng = Range("A1:A7")
  
'recorremos cada celda del rango indicado
For Each celda In rng
valor = celda.Value
    'asignamos color si la celda contiene el texto Excel
    'con el operador Like
    If valor Like "*Excel*" Then celda.Interior.Color = 65535
Next celda
End Sub



En este segundo procedimiento que sigue, y que realiza la misma acción, empleamos la función InStr. En el mismo módulo del Editor de VBA anterior añadiremos las líneas de la macro.

Sub FuncionInStr()
Dim celda As Object
Dim rng As Range
Set rng = Range("A1:A7")
  
'recorremos cada celda del rango indicado
For Each celda In rng
valor = celda.Value
    'asignamos color si la celda contiene el texto Excel
    'con la función InStr
    If InStr(valor, "Excel") > 0 Then celda.Interior.Color = 65535
Next celda
End Sub



El resultado en ambos casos es idéntico, se busca celda por celda en el rango indicado, dentro de cada cadena contenida en dicha celda, sólo cuando la cadena 'Excel' esté contenida en ella, se marcará con un color el fondo:

VBA: El operador de comparación LIKE.

42 comentarios:

  1. Doc Excelente aporte y si en vez de pintar de amarillo el fondo de la celda quisieramos escribir un texto determinado por cada like en la celda de la columna del costado?

    ResponderEliminar
    Respuestas
    1. Hola Moises,
      si he entendido bien, bastaría reemplazar:
      If InStr(valor, "Excel") > 0 Then celda.Interior.Color = 65535
      por
      If InStr(valor, "Excel") > 0 Then celda.offset(0,1).value="texto que quieras"

      or
      If valor Like "*Excel*" Then celda.Interior.Color = 65535
      por
      If valor Like "*Excel*" Then celda.offset(0,1).value="texto que quieras"

      Saludos cordiales !

      Eliminar
    2. Doc te pasaste, gracias por el aporte esta excelente! Saludos Cordiales

      Eliminar
    3. Ismael una consulta mas, entiendo que las funciones se refieren a una busqueda estrictamente igual diferenciando mayusculas y minusculas, hay la opcion que sea insensible a mayusculas y minusculas?

      Eliminar
    4. Encontre la respuesta estimado Ismael era colocando Option Compare Text Arriba de los sub!!! =)

      Eliminar
    5. Y si deseo que el "texto que quiera" tengo un color de celda específico? 🤔

      Eliminar
    6. Hola Luis,
      qué tal estás?, un placer saludarte igualmente

      Creo que ya contesté este tema días atrás... puede ser?


      Para lo que comentas echa un vistazo a
      http://excelforo.blogspot.com/2014/06/vba-cambiar-color-de-la-fuente-dentro.html

      Saludos

      Eliminar
  2. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  3. Necesito ayuda: Tengo varias aplicaciones en VBA Excel, cuando ejecuto un form desde un menu, concluyo con el form y lo ejecuto de una vez, no carga los datos en los combos y se inhibe excel. Puedo enviarle la aplicación para que la revisen.

    ResponderEliminar
    Respuestas
    1. Hola Danilo,
      entiendo que esas 'aplicaciones' serán códigos de procedimientos Sub incluidos en algún UserForm...
      Envíame si quieres el fichero .xlsm y le echo un vistazo.
      excelforo@gmail.com
      Saludos

      Eliminar
  4. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  5. Hola, buenos dias.

    Revisando el post, tengo que decir que es una gran ayuda para poder utilizar este comando en VBA.

    Ahora, tengo una pregunta ya que tengo un caso similar, únicamente que yo deseo hacer esta búsqueda para una columna que tiene mas de 2000 registros, te explico con un ejemplo:

    Actualmente tengo un Excel, donde tengo una Columna C con la siguiente data:

    013531-100217
    013531-170117
    013533-250417
    1353345181
    135349978
    NCI-170117
    NCI-49978
    NCI-52001
    NCI-52048
    NCI-52072
    NCI-52126
    NCI-52157
    NCI-52164

    Si te fijas, tengo en una celda C el dato "013531-170117", y en otra celda tengo "NCI-170117" entonces deseo encontrarla, ya que este numero es un indicador para hacer match y es una llave que utilizan para denotar que con el documento NCI afectaron al documento "013531-170117".

    Igualmente podemos ver el ejemplo del caso "135349978" y el documento "NCI-49978" donde el original, fue afectado con esta NCI.

    Me ayudas por favor.

    Muchas gracias desde ya.

    ResponderEliminar
    Respuestas
    1. Hola,
      quizá empleando el método .Find y como valor buscado compondrías una variable a partir de los último cinco dígitos o bien a partir del guión medio '-', para componer como valor buscado
      "NCI-" & dato
      sobre el rango de valores...

      Espero te sirva la idea
      Saludos

      Eliminar
  6. Buen dia

    Tengo una consulta si tengo un texto en una Celda y deseo añadirselo a un texto que tengo y me lo deje en otra celda y pasarlo a TXT. se puede realizar con MACRO ?

    XXXXXXXX [Texto que deso añadir ]

    ResponderEliminar
    Respuestas
    1. Hola Jaime,
      no entiendo si te refieres a guardar el dato en un fichero tipo TXT o pasarlo a tipo texto.
      Obviamente es posible hacerlo (una y otra cosa) con macros...
      si bien el construir un texto es más simple con funciones, servirá la función, por ejemplo
      =CONCATENAR("texto";A1;"otro texto";A2;A3)
      Slds

      Eliminar
    2. Buen dia

      Tengo la siguiente plantilla::

      CALL ZocSend "telnet routing-instance gestion-ipdslam-spoke " IPXXXXX
      CALL ZocSend "^M"
      CALL ZocWait(">>User name:")
      CALL ZocSend "marcalbv^M"
      CALL ZocWait(">>User password:")
      CALL ZocSend "Temporal*7^M"
      CALL ZocWait("Logout Time :")
      CALL ZocSend "enable"
      CALL ZocSend "^M"
      CALL ZocSend "display interface vdsl XXXXX^M"
      CALL ZocSend "^M"
      CALL ZocSend " "
      CALL ZocDelay 2
      CALL ZocSend "quit^M"
      CALL ZocSend "^M"

      En el excel tengo unas 1000 IPs por celda y su puerto y deseo que por celda me asigne a la plantilla la ip y el puerto. y despues pasar todo a un solo TXT. no se si esto se pueda realizar con una macro

      Agradezco su atencion

      Eliminar
    3. Hola,
      habría que saber qué hace cada macro 'ZocSend' 'ZocWait' y 'ZocDelay' (aunque se intuye).
      En todo caso si tienes en cada celda 1000 IPs lo primero que tendrás que hacer es separarlas de alguna manera para poder referirte a ellas y cargar una variable que luego puedas trasladar a tus procedimientos ('ZocSend' 'ZocWait' y 'ZocDelay' )...
      No puede decirte más con la información que aportas
      Un saludo

      Eliminar
  7. Hola Ismael. Muy bueno tus posts. Una consulta. Tengo.en una planilla los siguientes datos. En la columna a hay números como palabras. En la columna b dígito lo que voy a buscar. Por ejemplo si en a dice alfa, y en b busco si la letra g está en esa celda. Si está en la columna c necesito que escriba V de verdadero o F de falso. Ya sea que a haya números letras o palabras. Desde ya muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Favio,
      ya te pedí por email una aclaración
      ;-)
      Saludos

      Eliminar
  8. Hola Ismael!
    Espero te encuentres bien
    Estimado quería pedirte ayuda con un problema que tengo con excel
    Cree una macros con 5000 datos que genera una búsqueda de una palabra ingresada en un textbox y lo muestra en un listbox. El problema es que la búsqueda se genera bien, pero no es exacta, por ejemplo; si busco "Lavadora" no solo me trae ese resultado, sino que también me trae todos los resultados que contengan esa palabra como "hidrolavadora" o tambien "clavadora". Mi duda es la siguiente; ¿Como puedo limpiar esta búsqueda para que solo me muestre lo ingresado en el textbox?

    Muchas gracias de antemano
    Atte. Víctor Moya

    ResponderEliminar
    Respuestas
    1. Hola,
      si estás siguiendo las macros del post, podrías forzar una búsqueda sencilla:
      If valor="Lavadora" Then ....

      Saludos!

      Eliminar
  9. Entiendo, El problema es que no estoy buscando solo el termino "lavadora" como para pasarle el dato en duro, sino que lo hago con un textbox de la siguiente forma

    If LCase(Cells(i, j).Offset(0, 0).Value) Like "*" & LCase(Trim(Me.txtFiltro1.Value)) & "*" Then

    Esto luego recorre los datos con un for pero la busqueda no es exacta

    ResponderEliminar
    Respuestas
    1. bueno, pues
      If valor=Me.txtFiltro1.Value Then ....
      no habría diferencia...
      Saludos

      Eliminar
  10. Si deseo que no se resalte la celda sino que la palabra Excel cambie de color? Cómo cambiaría el codigo. Gracias

    ResponderEliminar
  11. Hola buen día, favor de ayudar a saber que código de vba en excel recorre, extrae y compara datos tipo string.
    Supuestos
    1.- Se requiere extraer de la columna A de la posición 6 y 3 caracteres sean igual "000".
    2.- Si lo extraído es igual a "000" entonces en la columna C marcar con una "x", de lo contrario dejar la celda vacía.
    3.- Datos ejemplo de como quedaria.
    Columna A Columna B Columna C
    Cuenta Nombre Marca
    1010-000 Bancos x
    1010-001 Caja
    1010-021 Bancos MN
    1010-022 Bancos ME
    1020-000 Cxc x
    1020-002 Deudores
    1020-004 Empleados
    1030-000 Inventario x
    1040-000 Activo x
    1050-000 Inversiones x
    1050-999 Acciones

    Mucha agradecería su ayuda.

    Mario Herrera

    ResponderEliminar
    Respuestas
    1. Hola Mario
      y por que no una fórmula sencilla, en tu columna C:
      =SI(EXTRAE(a2;6;3)="000";"x";"")

      no creo una macro aporte nada especial en este caso..
      Slds

      Eliminar
  12. Hola Ismael, voy a usar la formula que recomiendas, muchas gracias por tu ayuda.

    Saludos

    ResponderEliminar
  13. Hola, gran aporte, muy bien explicado.
    Si me permites, puedo pedirte ayuda? Necesito, a partir de un Número de un TextBox (Ej: 0314001) seleccionar un archivo Excel distinto en una carpeta determinada y abrirlo para copiar una hoja del libro donde está el código, teniendo en cuenta que el nombre de los archivos que necesito abrir tienen nombre por ejemplo "BETAINA 30% - 0314001", es decir un nombre y a continuación un cierto número. Por si no me explico bien, necesito abrir un archivo a partir de sólo una parte del nombre del mismo. Desde ya muchas gracias. Saludos.

    ResponderEliminar
    Respuestas
    1. Hola
      tendrás que componer el nombre del fichero a partir del textbox
      workbooks.open TuRuta & "\" & "BETAINA 30% -" & tEXTbOX1.Value & ".xlsx"
      Saludos

      Eliminar
  14. Hola.
    Primeramente gracias por su rápida respuesta. Logre lo que necesitaba con la función Dir y unos comodines "*". La misma busca en una carpeta entre varios archivos Excel, seleccionando el archivo que comienza con una cadena de texto determinada (el valor del TextBox) sin importar el nombre completo del archivo, es suficiente que coincida solo el valor del txtbox.
    Gracias.
    Saludos.

    ResponderEliminar
  15. Hola, muy buen aporte. Quisiera felicitarte primeramente por tus ganas de beneficiar a la comunidad virtual, no queda mucha gente asi como vos. Lo quisiera preguntarte y que estoy necesitando saber es como lograr una busqueda en un combobox solo escribiendo una parte del texto del combobox. Por ej: Si en el combobox tengo la palabra "hola mundo", y cuando yo ingrese "mun", me reconozca como valida la busqueda y me traiga el valor : "hola mundo".

    Yo tengo una busqueda que me encuentra todos los textos que contiene un combobox (previa recarga del combobox) pero no logro hacer que me traiga el texto cuando le ingreso solo alguna parte del mismo.

    Aca mi codigo funcionando:

    ultimafila = Range("A" & Rows.Count).End(xlUp).Row ' Obtiene el valor de la ultima fila con datos
    Dim rng As Range: Set rng = Worksheets("gente").Range("A2:A" & ultimafila) ' Setea el rango desde la segunda fila hasta la ultima con datos

    With Me
    'Limpia los listboxs
    .ListBox1.Clear
    .ListBoxvector.Clear


    j = 2 'Variable para ubicar la fila que corresponde al item encontrado que luego es puesto en un listbox
    For Each i In rng.Value

    If (i <> "") * (i Like "*" & .ComboBox1.Value & "*") Then
    .ListBox1.AddItem i 'Guarda en listbox1 los valores ingresados en el combobox que coinciden con la base de datos paciente
    .ListBoxvector.AddItem j 'Guarda en listboxvector los valores de j

    End If
    j = j + 1
    Next i

    End With

    Este codigo esta funcionando. Una vez que se ingresa algun texto en el combobox, si es que encuentra coincidencia, lo carga en un listbox.
    Pero quisiera que me cargara todas las coincidencias solo ingresando una parte de un texto.
    Espero haberme explicado bien.

    Saludos y muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola,
      muchas gracias en primer lugar por tus palabras.
      En cuanto a la pregunta, se suele emplear el operador LIKE para comparar cada registro, tal como has hecho...
      Lo que tienes debería funcionarte como indicas, si acaso te faltaría el recorrido por cada elemento de la base de datos para ir cargando el listbox, esto es un
      FOR EACH dato IN rango
      if (dato Like "*" & .ComboBox1.Value & "*") Then
      ...

      espero te oriente
      saludos

      Eliminar
  16. Hola muchas gracias por la rapida respuesta.
    Te cuento que lop pude solucionar a traves de la propiedad MatchEntry.
    Lo que tuve que hacer es asignar la opcion (2 - fmMatchEntryNone)
    en la propiedad MatchEntry del combobox que queria buscar y luego de eso ponerme los datos en un listbox.

    Lo que sucedia era que si en la propiedad MatchEntry esta asignada la opcion (1 - fmMatchEntryComplete) en el combobox se me autocompletaba la busqueda con la primera coincidencia que encontraba y solo ponia un dato en el listbox. Por lo que cambiando esa propiedad, el combobox no autocompleta y en el listbox se cargan todas las coindicencias con el texto ingresado.

    PD: Una ultima consulta, sabrias como hacer para que en esa busqueda en el combobox me pueda buscar un texto sin importar si es mayuscula o miniscula? Osea que NO sea sensible a mayusculas y minisculas. EN este momento el codigo es (sensible) a mayusculas y minisculas.

    Codigo de la carga del combobox por ENTER:

    '3.1)CARGA Y ACTUALIZA EL COMBOBOX DE APELLIDO: Rutina que muestra en un combobox los datos coincidentes en
    ' relacion entre una busqueda especifica con la base de datos
    '----------------------------------------------------------------------------------------------------------

    Private Sub ComboBox1_Enter()

    Application.ScreenUpdating = False
    Dim X As Integer
    Worksheets("gente").Activate

    Me.ComboBox1.Clear

    For X = 2 To Range("A" & Rows.Count).End(xlUp).Row
    If Cells(X, 1) <> Empty Then ComboBox1.AddItem Range("A" & X).Value
    Next

    Application.ScreenUpdating = True

    End Sub


    Desde ya muchisimas gracias.
    Saludos desde Sgo del Estero - Argentina.

    ResponderEliminar
    Respuestas
    1. Hola,
      se suele emplear UCASE o LCASE para unificar la búsqueda

      if (UCASE(dato) Like "*" & UCASE(.ComboBox1.Value) & "*") Then

      convertir todo a mayúscula o minúscula

      Saludos

      Eliminar
  17. Hola,

    una ayuda, se puede hacer comodín con números? Es decir tengo una columna llena de numeros

    100201
    200201
    300201
    201400
    100300
    150250

    etc

    y que yo al poner en una celda "201" me filtre todos los q contengan ese numero como lo hace un autofiltro manual?

    Saludos y gracias.

    ResponderEliminar
    Respuestas
    1. Hola Fenix,
      en principio sí es posible... si te fallara aplicando el comparativo directamente, pasa el número a String (con Cstr(número) por ejemplo)

      Saludos cordiales

      Eliminar
  18. Y si por el contrario quiero buscar y agregar texto a las celdas que NO CONTIENEN el caracter?

    Es decir, en mi columna tengo los siguientes numeros:

    900-57-900-513
    900-57-900-514
    900-57-900-515
    51900900
    900-57-900-513
    900-57-900-514
    900-57-900-515
    51900900
    900-57-900-513
    900-57-900-514
    900-57-900-515
    51900900

    Y yo quiero que los que no contengan el guion, sean marcados.
    Nota(el rango puede ser de al menos 4 K celdas)


    ResponderEliminar
    Respuestas
    1. Hola Rodrigo,
      qué tal estás?, un placer saludarte igualmente
      Con macros podrías recorrer esas 4000 celdas y evaluar la condición:
      If InStr(valor, "-") = 0 Then celda.Interior.Color = 65535

      Un cordial saludo

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.