Type Mismatch Error VBA - why?

Question

I have a basic macro that is looking at a Cell in column B and then placing "NA" adjacent to the cell in column C based on the criteria I'm looking for. I have a type mismatch error and I don't understand why.

Sub badURLs()
    Dim lr As Long ' Declare the variable
    lr = Range("B2:B23068").End(xlUp).Row ' Set the variable
    ' lr now contains the last used row in column A

    Application.ScreenUpdating = False

    For a = lr To 1 Step -1
        If InStr(1, a, "bloomberg" Or "wiki" Or "hoovers", vbTextCompare) > 0 Then
        'Compares for bloomberg, wiki, or hoovers. Enters loop if value is greater than 0
            With Cells(a, 3)
                .NumberFormat = "General"
                .Value = "NA"
            End With
        End If
    Next a

    Application.ScreenUpdating = True
End Sub

The mismatch error occurs here:

With Cells(a, 3)

Is it possible that the lr is ever be 0? Does it occur the first time the For loop runs? Also, add Option Explicit to the very top of your sub, and add Dim a as Long (or a quickhand way is Dim a&). — BruceWayne
What is the value of "a" when the program hits the error? Hover the mouse over the variable "a" to see what it is. I also note that your lr (lastrow) variable is being taken from column B, not column A as your comment suggests. Check which columns you're interested in. — Chris Melville

tigeravatar tigeravatar · Accepted Answer · 2016-06-10T17:12:29

Are you sure you are getting the error on the With Cells(a, 3) line?? I imagine you're getting the error on the If InStr line because that line is completely invalid syntax. It should be:

    If InStr(1, Cells(a, 3), "bloomberg", vbTextCompare) > 0 _
    Or InStr(1, Cells(a, 3), "wiki", vbTextCompare) > 0 _
    Or InStr(1, Cells(a, 3), "hoovers", vbTextCompare) > 0 Then

Type Mismatch Error VBA - why?

2 Answers