Comparar cadenas de texto en VBA
Resuelto
Hurborg
-
Hurborg -
Hurborg -
Bonjour à tous,
As I am not a VBA programming expert on Excel 2010 (I also have Windows 8 on my PC), I need your fruitful help.
What I am trying to do is simple in words, but I am encountering some issues while programming.
There are three columns I, J, and K in an Excel sheet.
- I is "Operations"
- J is "Example"
- K is "Assignment"
In column I (called "Operations"), I have a list of characters such as:
- Payment by card
- Transfer in your favor
- Withdrawal at the ATM
- Direct debit
- Issued transfer
In the Example column, I have only one cell that contains the list of characters below:
Pole Employment Loire 133640037
Transfer In Your Favor
13 851 68056722 02012014
13364003778
What I want to do in VBA is the following program:
Excel looks at the value of the single cell in the "Example" column and compares it with the 5 cells in the "Operations" column. If one of the 5 values from this last column is contained in the single cell of the "Example" column, then Excel writes in the "Assignment" column, at the same height as the unique cell of column J, the string contained.
In other words, if in the following string of characters:
Pole Employment Loire 133640037
Transfer In Your Favor
13 851 68056722 02012014
13364003778
the string "Transfer in your favor" is present, then in the "Assignment" column it will say "Transfer in your favor"
Here is the code I am using but which does not work, hence my approach ;-):
Symptoms:
Excel does not indicate any programming errors, but the code that seemed correct to me does not allow me to achieve what I am trying to do.
It is starting from "If" and "Like" that it messes up ^^!
I leave you here my file on which I am working.
Thank you immensely, if you have questions do not hesitate ;-)
http://cjoint.com/?DAokMQEoqnj
As I am not a VBA programming expert on Excel 2010 (I also have Windows 8 on my PC), I need your fruitful help.
What I am trying to do is simple in words, but I am encountering some issues while programming.
There are three columns I, J, and K in an Excel sheet.
- I is "Operations"
- J is "Example"
- K is "Assignment"
In column I (called "Operations"), I have a list of characters such as:
- Payment by card
- Transfer in your favor
- Withdrawal at the ATM
- Direct debit
- Issued transfer
In the Example column, I have only one cell that contains the list of characters below:
Pole Employment Loire 133640037
Transfer In Your Favor
13 851 68056722 02012014
13364003778
What I want to do in VBA is the following program:
Excel looks at the value of the single cell in the "Example" column and compares it with the 5 cells in the "Operations" column. If one of the 5 values from this last column is contained in the single cell of the "Example" column, then Excel writes in the "Assignment" column, at the same height as the unique cell of column J, the string contained.
In other words, if in the following string of characters:
Pole Employment Loire 133640037
Transfer In Your Favor
13 851 68056722 02012014
13364003778
the string "Transfer in your favor" is present, then in the "Assignment" column it will say "Transfer in your favor"
Here is the code I am using but which does not work, hence my approach ;-):
Dim q As Integer
Dim AdressText As String
Dim AdresseTexteChercher As String
q = 0
Cells.Find("Exemple").Select
ActiveCell.Range("a1").Offset(1, 0).Activate
ActiveCell.Select
AdressText = ActiveCell.Value
For i = 1 To 5
q = q + 1
Cells.Find("Opérations").Select
ActiveCell.Range("a1").Offset(q, 0).Activate
AdresseTexteChercher = ActiveCell.Value
If AdressText Like AdresseTexteChercher Then
Cells.Find ("Affectation")
ActiveCell.Range("a1").Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = AdresseTexteChercher
End If
Next i
End Sub
Symptoms:
Excel does not indicate any programming errors, but the code that seemed correct to me does not allow me to achieve what I am trying to do.
It is starting from "If" and "Like" that it messes up ^^!
I leave you here my file on which I am working.
Thank you immensely, if you have questions do not hesitate ;-)
http://cjoint.com/?DAokMQEoqnj
5 respuestas
-
Hola,
un poco más simple:
Sub BuscarCadenaCaracteres()
Dim p As Integer
Dim q As Integer
Dim TextoDireccion As String
Dim TextoDireccionBuscar As String
With Worksheets("Fuentes")
'pasar a mayúsculas la frase
TextoDireccion = UCase(.Range("J3"))
For i = 3 To 7
'pasar a mayúsculas el texto a buscar
TextoDireccionBuscar = UCase(.Range("I" & i))
'probar si está en la frase
If InStr(1, TextoDireccion, TextoDireccionBuscar) Then
'escritura
.Range("K3") = TextoDireccionBuscar
End If
Next i
End With
End Sub -
Ohhh ¡Vaya!
Por un lado, gracias por tu respuesta :D
Es más simple para ti, pero no entendí para nada el código; ¿hay manera de que expliques tu código o no?
¿Por qué pasas a mayúsculas en realidad? Eso es lo que más me preocupa ^^
Te lo agradecería. -
Re,
salvo que le den importancia, busca cabeceras de columnas no útiles
bloque With --- end with: si otra hoja está activa, no escribirás en el lugar correcto
pasar a mayúsculas para evitar diferencias de escritura (minúsculas, mayúsculas)
instr(): permite encontrar la posición de un carácter o cadena en una palabra o frase-
Hola a todos,
o si las 2 cadenas deben ser estrictamente iguales:If IAdressText = AdresseTexteChercher Then
Nunca se sabe, términos como "Prélèvement" pueden estar presentes en etiquetas más largas...
Agregar Ucase() en las 2 cadenas si puede haber una diferencia de mayúsculas y minúsculas.
eric -
Gracias Eriiic por tu respuesta,
Los términos siempre se escriben de la misma manera de hecho, así que no me molestará. Pero si es el caso, ¿debo usar Ucase de hecho?
¿He entendido todo bien? ^^
Y por cierto, tampoco entendí por qué se usaba la siguiente sintaxis:
.Range("K3") = AdresseTexteChercher
¿Por qué el "." delante del rango? -
-
-
-
Quería agradecerles enormemente porque logré hacer con éxito lo que quería con el siguiente código (para los interesados, sin el With):
Dim p As Integer
Dim q As Integer
Dim TexteReference As String
Dim TexteCherche As String
p = 0
q = 0
For j = 1 To 2
p = p + 1
Cells.Find("Ejemplo").Select
ActiveCell.Range("a1").Offset(p, 0).Activate
ActiveCell.Select
AdresseTexte1 = ActiveCell.Address(0, 0)
TexteReference = Range(AdresseTexte1)
For i = 1 To 5
'NúmeroDeFilasColumnaOperaciones
q = q + 1
Cells.Find("Operaciones").Select
ActiveCell.Range("a1").Offset(q, 0).Activate
AdresseTexte2 = ActiveCell.Address(0, 0)
TexteChercher = Range(AdresseTexte2)
Cells.Find("Asignación").Select
ActiveCell.Range("a1").Offset(p, 0).Activate
Temps = InStr(1, TexteReference, TexteChercher, vbTextCompare)
If Temps <> 0 Then
ActiveCell.Value = TexteChercher
End If
Next i
q = 0
Next j
End Sub
Una vez más, muchas gracias por su ayuda :D
Que tengan un buen final de día a todos
