'Method 'Value' of object 'Range' failed

The following snippet of VBA code attempts a simple If statement over on an excel column of approx 80k rows long. The following error occurs.

Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range' failed

What is going wrong here?

Dim r As Long
Dim lastrow As Long
lastrow = Range("A" & Rows.Count).End(xlUp).Row
For r = 2 To lastrow
    If InStr(Range("I" & r).Text, "WH") Then
        Range("Z" & r).Value = "O"
    ElseIf InStr(Range("I" & r).Text, "MOD") Then
        Range("Z" & r).Value = "M"
    ElseIf InStr(Range("I" & r).Text, "VER") Then
        Range("Z" & r).Value = "V"
    ElseIf InStr(Range("I" & r).Text, "WT") Then
        Range("Z" & r).Value = "WT"
    ElseIf InStr(Range("E" & r).Text, "OIL") Then
        Range("Z" & r).Value = "OIL"
    Else: Range("Z" & r).Value = "N"
    End If
Next r

vba excel

Solution 1:^[1]

VBA environments are not immune to instability. If you have had multiple crashes, save your work and restart your machine (cold boot).

With that said, each of these routines runs in less than a half-second as opposed to the 27 seconds that your original took to get through 80K cells.

Sub oilWT2()
    Dim r As Long, vVALs As Variant

    Debug.Print Timer
    With Worksheets("Sheet5")
        vVALs = .Range(.Cells(2, "I"), .Cells(rows.Count, "A").End(xlUp).Offset(0, 8)).Value2
        For r = LBound(vVALs, 1) To UBound(vVALs, 1)
            Select Case UCase(vVALs(r, 1))
                Case "WH"
                    vVALs(r, 1) = "O"
                Case "MOD", "VER"
                    vVALs(r, 1) = Right(vVALs(r, 1), 1)
                Case "WT", "OIL"
                    'do nothing - value already correct
                Case Else
                    vVALs(r, 1) = "N"
            End Select
        Next r
        .Cells(2, "Z").Resize(UBound(vVALs, 1), 1) = vVALs
    End With
    Debug.Print Timer
End Sub

Sub oilWT3()
    Dim r As Long, vVALs As Variant

    Debug.Print Timer
    With Worksheets("Sheet5")
        vVALs = .Range(.Cells(2, "I"), .Cells(rows.Count, "A").End(xlUp).Offset(0, 8)).Value2
        For r = LBound(vVALs, 1) To UBound(vVALs, 1)
            Select Case True
                Case CBool(InStr(1, vVALs(r, 1), "WH", vbTextCompare))
                    vVALs(r, 1) = "O"
                Case CBool(InStr(1, vVALs(r, 1), "MOD", vbTextCompare)) Or _
                     CBool(InStr(1, vVALs(r, 1), "VER", vbTextCompare))
                    vVALs(r, 1) = Right(vVALs(r, 1), 1)
                Case CBool(InStr(1, vVALs(r, 1), "WT", vbTextCompare))
                    vVALs(r, 1) = "WT"
                Case CBool(InStr(1, vVALs(r, 1), "OIL", vbTextCompare))
                    vVALs(r, 1) = "OIL"
                Case Else
                    vVALs(r, 1) = "N"
            End Select
        Next r
        .Cells(2, "Z").Resize(UBound(vVALs, 1), 1) = vVALs
    End With
    Debug.Print Timer
End Sub

The former simply looks at the entire cell contents; the latter uses the InStr function to search for a string-within-a-string as your original does. By reusing a variant array to store the new values, it makes the conditional statements more efficient than a cell-by-cell read. The values are similarly returned en masse to column Z.

Solution 2:^[2]

When I received this error, it was because I had created an infinite loop, for example, a Worksheet_Change Sub set the value of a Range, creating another change. Double check what is triggering these Subs and ensure that the Sub will not infinitely trigger itself.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Janine White

'Method 'Value' of object 'Range' failed

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]