5

I have a basic programming background and have been self sufficient for many years but this problem I can't seem to solve. I have a program in VBA and I need to compare two strings. I have tried using the following methods to compare my strings below but to no avail:

//Assume Cells(1, 1).Value = "Cat"

Dim A As String, B As String

A="Cat"

B=Cell(1, 1).Value

If A=B Then...

If A Like B Then...

If StrCmp(A=B, 1)=0 Then...

I've even tried inputting the Strings straight into the code to see if it would work:

If "Cat" = "Cat" Then...

If "Cat" Like "Cat" Then...

If StrCmp("Cat" = "Cat", 1) Then...

VBA for some reason does not recognize these strings as equals. When going through Debugger it shows that StrComp returns 1. Do my strings have different Char lengths? Thanks for any help.

Improve this question
8
  • What do you see if you do B = "@@@" + Cell(1, 1).Value + "@@@"? Any extra spaces? Tried wrapping B with Trim()? Commented Jun 23, 2014 at 16:22
  • Yes and VBA still still returns a 1 with StrComp() Commented Jun 23, 2014 at 16:48
  • I think you're using StrComp incorrectly. But the other functions should work (and indeed do work when I test them). Commented Jun 23, 2014 at 17:01
  • Am I using it wrong? And I dont know Keep trying If "D555177" Like "D555177" then... but im not getting anything. Is it because i have integers in the string? It shouldn't matter right? Commented Jun 23, 2014 at 17:08
  • see my answer below, you're certainly using it wrong, comparing a boolean to an integer. Commented Jun 23, 2014 at 17:11

1 Answer 1

Reset to default
17

Posting as answer because it doesn't fit in the comments:

I find it hard to believe that something like:

MsgBox "Cat" = "Cat"

Would not display True on your machine. Please verify.

However, I do observe that you are most certainly using StrComp function incorrectly.

The proper use is StrComp(string, anotherstring, [comparison type optional])

When you do StrComp(A=B, 1) you are essentially asking it to compare whether a boolean (A=B will either evaluate to True or False) is equivalent to the integer 1. It is not, nor will it ever be.

When I run the following code, all four message boxes confirm that each statement evaluates to True.

Sub CompareStrings()
Dim A As String, B As String
A = "Cat"
B = Cells(1, 1).Value


MsgBox A = B

MsgBox A Like B

MsgBox StrComp(A, B) = 0 

MsgBox "Cat" = "Cat" 


End Sub

Update from comments

I don't see anything odd happening if I use an array, just FYI. Example data used in the array:

enter image description here

Modified routine to use an array:

Sub CompareStrings()
Dim A As String, B() As Variant

A = "Cat"
B = Application.Transpose(Range("A1:A8").Value)

For i = 1 To 8

    MsgBox A = B(i)

    MsgBox A Like B(i)

    MsgBox StrComp(A, B(i)) = 0

    MsgBox "Cat" = B(i)

Next

End Sub

What I would check is how you're instantiating the array. Range arrays (as per my example) are base 1. If it assigned some other way, it is most likely base 0, so check to make sure that you're comparing the correct array index.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hmm you are right. I am getting true through the msg box. Maybe it is because something is wrong with my array that I am using. For i = 1 To 8 If A = B(i) Then... Next i
Yea StrComp is not supposed to compare like that. Thanks.
see some more info added, above. Maybe gets you closer to resolving issue (potentially) with your array.
Thanks a lot for all of the help David

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.