CODEWITHSUNDEEP
vbaexcelfor-looprange
Thomas the plane
Thomas the plane

Reputation: 35

Search a table based on the value of more than one cell per row

I am trying to find a previous value of a unit ( the data for the unit being an entire row) entered into a table. Over time the value in one of the columns in the unit grows and I am attempting to use a loop to analyze each row based on two values in each row and return the previous value in that specific unit when a new value is entered for that specific unit. 

'My Variables

    Dim current_size As Integer
    Dim last_size As String
    Dim joint_number As Integer

    Dim tool_sizes As Range: Set tool_sizes = drill_log.Range("Drill_Log[Tool Size]")
    Dim joints As Range: Set joints = drill_log.Range("Drill_Log[Joint '#]")
    Dim pass_types As Range: Set pass_types = drill_log.Range("Drill_Log[Pass Type]")
    Dim row As Range

    'Finds the current pass size and last pass size and assigns it to the current pass size variables
        If pass_type = ream Then
            current_size = drill_log.Range("W" & last_ream_range.row)
            joint_number = drill_log.Range("C" & last_ream_range.row)

        ElseIf pass_type = intersect_ream Then
            current_size = drill_log.Range("W" & last_intersect_ream_range.row)
            joint_number = drill_log.Range("C" & last_intersect_ream_range.row)
        Else
            Exit Sub
        End If

    'Finds the previous pass size for the last joint entered

        For Each row In drill_log.Range("Drill_Log")

'Line that Errors
          If pass_types.Value = pass_type And joints.Value = joint_number Then
          If tool_sizes < current_size Then
                    last_size = tool_sizes
                Else
                    last_size = "NONE"
                End If
            Else
            End If

        Next row

My goal is to have it examine the specified columns in each row and if it is a match then assign it to the variable if the cell in the column containing the information is less that the current.

Right now it is telling me that I have a type mismatch and I cant solve it. Thanks for the help.

Upvotes: 0

Views: 33

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71167

A type mismatch error on an instruction that compares Range.Value against a string or number or anything, is almost certainly due to Range.Value containing an error value.

Either fix the data and ensure there can never be an error in these cells:

=IFERROR([whatever formula you have here], "")

Or change the code so that it no longer assumes there can't be errors in the cells:

If Not IsError(pass_types.Value) And Not IsError(joints.Value) Then
    If pass_types.Value = pass_type And joints.Value = joint_number Then
        '...
    End If
End If

...or play safe, and do both wherever possible.

The reason for the type mismatch is that when IsError(Range.Value) is True, then the type of Range.Value is Variant/Error, and VBA doesn't know how to compare an Error value to anything.

Repro:

Debug.Print TypeName(CVErr(xlErrNA))       'prints "Error"
Debug.Print IsError(CVErr(xlErrNA))        'prints "True"
Debug.Print CVErr(xlErrNA)) = vbNullString 'type mismatch
Debug.Print CVErr(xlErrNA)) = 0            'type mismatch

Upvotes: 2

Related Questions