Hi all just wondering how this is done.
for example
If i want to get the last 5 letters from PineApPle and match it with 'apple' or 'APPLE' OR 'aPPlE'.
Is there a way?
cheers
Hi all just wondering how this is done.
for example
If i want to get the last 5 letters from PineApPle and match it with 'apple' or 'APPLE' OR 'aPPlE'.
Is there a way?
cheers
Hi
you can use the strcomp function with vbBinaryCompare to see if the letters are the same regardless of case or vbTextCompare for a case match.
Example
[VBA]Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "APPLE"
str2 = "aPPlE"
If StrComp(str1, str2, vbBinaryCompare) Then
MsgBox ("match found")
End If
End Sub[/VBA]
regards
qff
That's the wrong way round - vbTextCompare ignores case, vbBinaryCompare is case sensitive. Additionally, StrComp returns 0 if there is a match, so the code should be:
[vba]Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "APPLE"
str2 = "appLe"
If StrComp(str1, str2, vbTextCompare) = 0 Then
MsgBox ("match found")
End If
End Sub
[/vba]
Regards,
Rory
Microsoft MVP - Excel
Rory
thanks for pointing out my error.
I should know better than to post with a hangover
regards
qff
Regards,
Rory
Microsoft MVP - Excel
Thanks guys
That looks wierd , but works, I would have thought if a string comparison equals zero (False) it would not return a match.?
cheers
I would use
[vba]
Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If str1 Like "*" & str Then
MsgBox ("match found")
End If
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
It makes a bit more sense when you think that StrComp can tell you whether string 1 is less than, equal to, or greater than string 2 (returning -1, 0, or 1)
I was only commenting on qff's code though - for what you want, xld's is more specific.
Regards,
Rory
Microsoft MVP - Excel
to cater for all the case variations you've given, use Option Compare Text, e.g.Originally Posted by daniel_d_n_r
[vba]
Option Explicit
Option Compare Text
Sub mymatch()
If ActiveCell.Value Like "*apple*" Then MsgBox "match found"
End Sub
[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
It strange that VBA doesn't have a function like UCASE or LCASE to ignore case sensitivity.
thanks all for the helpful assistance
cheers
It does if you want that
[vba]
Sub mymatch()
Dim str1 As String
Dim str2 As String
str1 = "Pineapple"
str2 = "Apple"
If LCase(str1) Like "*" & LCase(str) Then
MsgBox ("match found")
End If
[/vba]
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Exactly, but that is not ignoring case sensitivity, it's forcing changes to case to enable a comparison to be made. Option Compare Text is really the only way to do what you want i.e. to ignore case altogether...Originally Posted by xld
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Is the result different?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
No Bob, but the thread title is... Read string variable while ignoring case sensitiveity
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
But then he said ... It strange that VBA doesn't have a function like UCASE or LCASE to ignore case sensitivity... so he clearly wasn't being as literal as you have interpreted it.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Bob, my post #12 wasn't in any way some sort of criticism of your reply, it was targeted at the OP's stated misconception (Post #10) that UCASE or LCASE somehow ignores case...
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.