Comparing Strings in VBA
Solved
Hello everyone,
Not being a whiz at VBA programming in Excel 2010 (I also have Windows 8 on my PC), I need your fruitful help.
What I'm trying to do is simple in words, but I'm running into some issues when programming it.
Let's consider 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 items such as:
- Payment by card
- Transfer in your favor
- Withdrawal at the ATM
- Direct debit
- Issued transfer
In the Example column, I only have a single cell that contains the list of characters below:
Pole Emploi Pays 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 unique cell in the "Example" column and compares it with the 5 cells in the "Operations" column. If one of the 5 values in this column is contained in the unique cell of the "Example" column, then Excel writes in the "Assignment" column, at the same height as the unique cell in column J, the string contained.
In other words, if the following string:
Pole Emploi Pays Loire 133640037
Transfer In Your Favor
13 851 68056722 02012014
13364003778
contains the string "Transfer in your favor," then in the "Assignment" column, "Transfer in your favor" is written.
Here’s the code I’m using that doesn’t work, hence my approach ;-):
Symptoms:
Excel does not show me any programming errors, but the code that seemed correct to me doesn't allow me to achieve what I'm trying to do.
It’s starting from the "If" and the "Like" that it’s not working ^^!
I’m attaching my working file below.
Thank you so much, if you have any questions, please don’t hesitate ;-)
http://cjoint.com/?DAokMQEoqnj
Not being a whiz at VBA programming in Excel 2010 (I also have Windows 8 on my PC), I need your fruitful help.
What I'm trying to do is simple in words, but I'm running into some issues when programming it.
Let's consider 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 items such as:
- Payment by card
- Transfer in your favor
- Withdrawal at the ATM
- Direct debit
- Issued transfer
In the Example column, I only have a single cell that contains the list of characters below:
Pole Emploi Pays 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 unique cell in the "Example" column and compares it with the 5 cells in the "Operations" column. If one of the 5 values in this column is contained in the unique cell of the "Example" column, then Excel writes in the "Assignment" column, at the same height as the unique cell in column J, the string contained.
In other words, if the following string:
Pole Emploi Pays Loire 133640037
Transfer In Your Favor
13 851 68056722 02012014
13364003778
contains the string "Transfer in your favor," then in the "Assignment" column, "Transfer in your favor" is written.
Here’s the code I’m using that doesn’t work, hence my approach ;-):
Dim q As Integer
Dim AdressText As String
Dim AdresseTexteChercher As String
q = 0
Cells.Find("Example").Select
ActiveCell.Range("a1").Offset(1, 0).Activate
ActiveCell.Select
AdressText = ActiveCell.Value
For i = 1 To 5
q = q + 1
Cells.Find("Operations").Select
ActiveCell.Range("a1").Offset(q, 0).Activate
AdresseTexteChercher = ActiveCell.Value
If AdressText Like AdresseTexteChercher Then
Cells.Find ("Assignment")
ActiveCell.Range("a1").Offset(1, 0).Activate
ActiveCell.FormulaR1C1 = AdresseTexteChercher
End If
Next i
End Sub
Symptoms:
Excel does not show me any programming errors, but the code that seemed correct to me doesn't allow me to achieve what I'm trying to do.
It’s starting from the "If" and the "Like" that it’s not working ^^!
I’m attaching my working file below.
Thank you so much, if you have any questions, please don’t hesitate ;-)
http://cjoint.com/?DAokMQEoqnj
5 answers
-
Hello,
a little simpler:
Sub SearchStringCharacters()
Dim p As Integer
Dim q As Integer
Dim AddressText As String
Dim TextToFind As String
With Worksheets("Sources")
'convert phrase to uppercase
AddressText = UCase(.Range("J3"))
For i = 3 To 7
'convert text to find to uppercase
TextToFind = UCase(.Range("I" & i))
'check if in phrase
If InStr(1, AddressText, TextToFind) Then
'write
.Range("K3") = TextToFind
End If
Next i
End With
End Sub -
Oh wow!!
First of all, thank you for your response :D
It's easier for you, but I didn't understand the code at all; is there a way you could explain your code or not?
Why do you convert to uppercase actually? That's mainly what's bothering me ^^
I would appreciate it -
Re,
unless you insist, look for unnecessary column headers
block With --- end with: if another sheet is active, you will not write in the right place
uppercase conversion to avoid differences in writing (lowercase, uppercase)
instr(): allows you to find the position of a character or string in a word or phrase-
-
Thank you Eriiic for your response,
The terms are always written the same way actually so it won't bother me. But if that's the case, do I have to use Ucase actually?
Did I get everything right ^^?
And otherwise, I also didn't understand why we used the following syntax:
.Range("K3") = AdresseTexteChercher
Why the "." in front of the range? -
-
-
I'm frustrated because the method of f894009 works wonderfully, but I can't adapt it to my project.
Not understanding why we use the With method, I do not wish to represent it in my program. That's why I want to continue with the code base I provided you.
I have made some modifications based on what you suggested, such as:
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
AdresseTexte1 = ActiveCell.Address(0, 0)
TexteReference = UCase(Range(AdresseTexte1))
For i = 1 To 5
q = q + 1
Cells.Find("Opérations").Select
ActiveCell.Range("a1").Offset(q, 0).Activate
AdresseTexte2 = ActiveCell.Address(0, 0)
TexteChercher = UCase(Range(AdresseTexte2))
If InStr(1, TexteReference, TexteChercher) Then
Cells.Find ("Affectation")
ActiveCell.Range("a1").Offset(1, 0).Activate
Range("K3") = TexteChercher
Next i
End Sub
But I'm still stuck; I don't understand why the value of the variable "TexteChercher" is not written in cell K3!
Can someone enlighten me and tell me why it doesn't work?
I feel like I'm close....
Thank you very much. -
I wanted to say a big thank you because I successfully did what I wanted with the following code (for those interested, without the 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("Exemple").Select
ActiveCell.Range("a1").Offset(p, 0).Activate
ActiveCell.Select
AdresseTexte1 = ActiveCell.Address(0, 0)
TexteReference = Range(AdresseTexte1)
For i = 1 To 5
'NombreDeLignesColonneOpérations
q = q + 1
Cells.Find("Opérations").Select
ActiveCell.Range("a1").Offset(q, 0).Activate
AdresseTexte2 = ActiveCell.Address(0, 0)
TexteChercher = Range(AdresseTexte2)
Cells.Find("Affectation").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
Again, thank you very much for your help :D
Have a great end of the day everyone
