excel - VBA is saying Type Mismatch when I try and check the value of a range - Stack Overflow

admin管理员组

文章数量:1122832

I am completely new to VBA and have literally never done this before so this might be a stupid mistake. But unfortunately it seems so basic that no one has ever felt the need to explain it. I am trying to let the a variable, "row", equal the row value of a specific number being searched for in a given range of cells. I kept receiving the error "Run-time error '91': Object variable or With Block variable not set" and some people said it may be because the value I'm looking for isn't in that range.

So I wanted to check what values were in the range incase I had misreferenced or something, and so I wrote in the immediate window ?Range("B2:B30") and it gives me the "Run-time error '13': Type Mismatch" error. I don't understand how this is possible because surely that's not even dependent on my code?

This is part of my code:

Sub CreateContract()

    Dim wdApp As Word.Application
    
    Set wdApp = CreateObject("Word.Application")
    
    Dim ContractNumber As Double
    
    Dim row As Double

            Worksheets("Contract Creator").Select
            
                ContractNumber = Range("O1").Value
                
                row = Range("B2", Range("B2").End(xlDown)).Find(what:=ContractNumber, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row

...

End Sub

I am completely new to VBA and have literally never done this before so this might be a stupid mistake. But unfortunately it seems so basic that no one has ever felt the need to explain it. I am trying to let the a variable, "row", equal the row value of a specific number being searched for in a given range of cells. I kept receiving the error "Run-time error '91': Object variable or With Block variable not set" and some people said it may be because the value I'm looking for isn't in that range.

So I wanted to check what values were in the range incase I had misreferenced or something, and so I wrote in the immediate window ?Range("B2:B30") and it gives me the "Run-time error '13': Type Mismatch" error. I don't understand how this is possible because surely that's not even dependent on my code?

This is part of my code:

Sub CreateContract()

    Dim wdApp As Word.Application
    
    Set wdApp = CreateObject("Word.Application")
    
    Dim ContractNumber As Double
    
    Dim row As Double

            Worksheets("Contract Creator").Select
            
                ContractNumber = Range("O1").Value
                
                row = Range("B2", Range("B2").End(xlDown)).Find(what:=ContractNumber, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row

...

End Sub

• excel
• vba

Share Improve this question Follow edited Nov 22, 2024 at 10:48 Darren Bartrup-Cook 19.6k22 gold badges2626 silver badges4848 bronze badges asked Nov 22, 2024 at 10:47 saskiaclrsaskiaclr 111 silver badge22 bronze badges 1

• Avoid using VBA keywords as Variable names. "Row" is a property and when you assign it as variable, it can make your code confusing. Instead, use RW. – Frank Ball Commented Nov 22, 2024 at 21:10

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 1

(1) You get a Type mismatch error for ? Range("B2:B30") because you can only write one value at a time and Range("B2:B30") returns (a 2-dimenional) array with 1x29 value. So nothing to worry about. Write for example ? Range("B2").

(2) Dont use Select. Don't rely on the ActiveSheet. Write something like this:

With ThisWorkbook.Worksheets("Contract Creator")
     row = .Range("B2", .Range("B2").End(xlDown)).Find(what:=ContractNumber, SearchOrder:=xlRows, SearchDirection:=xlPrevious).row
End With

Note the Dots before .Range - that qualifies the Range - it tells VBA that you want to work with the object specified in the With-statement (in your case the sheet "Contract Creator").

Obgligarory link: How to avoid using Select in Excel VBA

(3) Just to let you know: There are better ways to find the last cell of a range than to use End(xlDown). See Find last used cell in Excel VBA

(4) You get an "Object not Set" error when the Find-command cannot find anything. Find returns a Range if something is found, and using Range.Row will give you the row number. However, if nothing is found, instead of a Range object you get Nothing, and Nothing has (of course) no Row property. Best is split such complex commands into pieces:

With ThisWorkbook.Worksheets("Contract Creator")
    Dim ContractNumber As Variant 
    ContractNumber = .Range("O1").Value
    Dim lastCell As Range, searchRange As Range, foundCell As Range
    Set lastCell = .Range("B2").End(xlDown)
    Set searchRange = .Range("B2", lastCell)
    Set foundCell = searchRange.Find(what:=ContractNumber, SearchOrder:=xlRows, SearchDirection:=xlPrevious)
    If foundCell Is Nothing Then
        MsgBox "Contract Number not found"
        Exit Sub
    End If
    row = foundCell.row
    (...)
End With

(5) Don't use the data type Double for a row number. Double is a floating point value that should be used for calculations that don't result in integer values. Use Long instead (forget about data type Integer in VBA as it is obsolete). Same is true probably for the contract number.

?Range("B2:B30") returns an array.
To Debug.Print an array is not possible. You have to define which item of the range you want to get.

?Range("B2:B30")(1) returns B2
?Range("B2:B30")(1,2) returns C2 (!!!)
?Range("B2:B30")(29) returns B30
?Range("B2:B30")(30) returns B31

The same can be used with Cells property

?Range("B2:B30").Cells(1)
?Range("B2:B30").Cells(1,2)

?Range("B2:B30") refers to a range of cells, the immediate window won't return each value - it's not set up to know you want an array of values returned. ?Range("B2") would work as it's only looking at a single cell.

Your posted code would work if the ContractNumber is in the range Range("B2", Range("B2").End(xlDown)).
If it doesn't find the value it will return the error message you're getting - "Run-time error '91': Object variable or With Block variable not set"

I'd have a read of How to avoid using Select in Excel VBA and also With...End With Statement.

This is the verbose code I've explicitly stated the workbook and worksheet for each reference -

Public Sub CreateContract()

    Dim ContractNumber As Double
    ContractNumber = ThisWorkbook.Worksheets("Contract Creator").Range("O1").Value
    
    'Note:  Rather than `xlDown` the code starts at the bottom and uses `xlUp`.  
    'If your range only contained a value in cell B2 then `xlDown` would return the whole column from your start cell down.
    Dim SearchRange As Range
    Set SearchRange = ThisWorkbook.Worksheets("Contract Creator").Range("B2", ThisWorkbook.Worksheets("Contract Creator").Cells(Rows.Count, 2).End(xlUp))
    
    Dim FoundRow As Range
    Set FoundRow = SearchRange.Find(What:=ContractNumber, After:=SearchRange.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    If Not FoundRow Is Nothing Then lRow = FoundRow.row

End Sub  

Using With...End With it can be shortened to:

Public Sub CreateContract()

    With ThisWorkbook.Worksheets("Contract Creator")

        Dim ContractNumber As Double
        ContractNumber = .Range("O1").Value
        
        Dim SearchRange As Range
        Set SearchRange = .Range("B2", .Cells(Rows.Count, 2).End(xlUp))
    
    End With
    
    Dim FoundRow As Range
    Set FoundRow = SearchRange.Find(What:=ContractNumber, After:=SearchRange.Cells(1, 1), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    Dim lRow As Long
    If Not FoundRow Is Nothing Then lRow = FoundRow.row
    
    If lRow = 0 Then
        MsgBox "Value not found"
    Else
        MsgBox ContractNumber & " found on row " & lRow
    End If

End Sub

Also worth having a read of:
Range.Find method and Worksheet.Cells property

本文标签： excelVBA is saying Type Mismatch when I try and check the value of a rangeStack Overflow