CODEWITHSUNDEEP
vbaexcel
Brayheart
Brayheart

Reputation: 167

Type Mismatch Error VBA - why?

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)

Upvotes: 0

Views: 1947

Answers (2)

J. felix
J. felix

Reputation: 31

Try This:

    With ActiveSheet.Cells(a, 3)

Upvotes: 0

tigeravatar
tigeravatar

Reputation: 26640

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

Upvotes: 4

Related Questions