VBA Mismatch IF Statement

Question

I am trying to compare 2 string values at the same time - Account # and Account Name. I've explicitly set all variables as Strings, however I am still getting a mismatch error in my IF comparison.

Does anyone know what is it I'm doing wrong?


Dim i As Integer
For i = 2 To 10

    Dim accountNumber As String
    accountNumber = CStr(Sheet1.Cells(i, 2).Value)
    Dim accountName As String
    accountName = CStr(Sheet1.Cells(i, 3).Value)
    Dim c As Integer
    c = 4

    Dim j As Integer
    For j = 2 To 14831
        Dim accountNumber2 As String
        accountNumber2 = CStr(Sheet2.Cells(j, 2).Value)
        Dim accountName2 As String
        accountName2 = CStr(Sheet2.Cells(j, 3).Value)

        If (accountNumber = accountNumber2 & accountName = accountName2) Then
            Dim platform As String
            platform = Sheet2.Cells(j, 11).Value
            Sheet1.Cells(i, c).Value = platform
            c = c + 1
        End If
    Next j

Next i
End Sub

& is not and in VBA. Try: If accountNumber = accountNumber2 and accountName = accountName2 Then — cybernetic.nomad
– cybernetic.nomad, Commented Nov 28, 2019 at 22:45
Hi Jerry. If my answer worked for you, please mark it as the accepted answer to let others know it worked. Thanks. — mgd
– mgd, Commented Nov 29, 2019 at 21:44

mgd · Accepted Answer · 2019-11-28 23:16:37Z

2

As @cybernetic.nomad writes in a comment below your question, you should be using the and operator which is logical and instead of & which for strings means concatenation.

When you write

accountNumber = accountNumber2 & accountName = accountName2

then & has higher precedence than = so effectively this is what it means:

accountNumber = (accountNumber2 & accountName) = accountName2

So what happens is:

You are first concatenating accountNumber2 and accountName resulting in a string.
You are then comparing accountNumber to the result of the concatenation resulting in a boolean value.
Finally, you are trying to compare that boolean to accountName2 which is a string and this is probably not what you want.

answered Nov 28, 2019 at 23:16

mgd

4,4124 gold badges25 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

VBA Mismatch IF Statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related