How can I fix the error "Method 'range of object'_Worksheet' failed

Question

I have the following code. When I change a cell, it begins to work, but I get an error "Method 'range' of object'_Worksheet' failed," after it fills in the first cell

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim FeedType As Range
    'Dim SlotOrHole As Range
    Dim Redist As Range
    Dim RDesignHead As Range
    Dim DesignHead As Range
    Dim RTDHead As Range
    Dim TDHead As Range
    Dim RTUHead As Range
    Dim TUHead As Range
    Dim TwoTier As Range


    Set FeedType = Sheets("Input").Range("H12")
    'Set SlotOrHole = Sheets("Input").Range("H19")
    Set Redist = Sheets("Input").Range("H18")
    Set RDesignHead = Sheets("Spouts2").Range("P3")
    Set RTDHead = Sheets("Spouts2").Range("R3")
    Set RTUHead = Sheets("Spouts2").Range("Q3")
    Set DesignHead = Sheets("Spouts").Range("P3")
    Set TDHead = Sheets("Spouts").Range("R3")
    Set TUHead = Sheets("Spouts").Range("Q3")
    Set TwoTier = Sheets("Input").Range("H16")


    If TwoTier.Value = "Y" Then

        Sheets("Input").Range("L6") = "See Tab"
        Sheets("Input").Range("L7") = "See Tab"
        Sheets("Input").Range("L8") = "See Tab"

 'If Two Tier is set to yes; all head heights should report the value "See Tab"

    ElseIf FeedType <> "Vapor" And TwoTier = "N" And Redist = "Y" Then

        Sheets("Input").Range("L6") = RDesignHead
        Sheets("Input").Range("L7") = RTDHead
        Sheets("Input").Range("L8") = RTUHead

 'If Redistrubtion is set to yes; head heights are pulled from the spouts2 tab

    ElseIf FeedType <> "Vapor" And TwoTier = "N" And Redist = "N" Then

        Sheets("Input").Range("L6") = DesignHead
        Sheets("Input").Range("L7") = TDHead
        Sheets("Input").Range("L8") = TUHead

 'If Redistribution is set to no; head heights are pulled from the spouts tab

    ElseIf FeedType = "Vapor" Then

        Sheets("Input").Range("L6") = "NA"
        Sheets("Input").Range("L7") = "NA"
        Sheets("Input").Range("L8") = "NA"



    End If

    



'   Dim IsNo As Boolean
'        IsNo = UCase(Range("G_CapturedAbove").Value) = "N"
'        Sheets("Spouts").Visible = IsNo
'        Sheets("Spouts2").Visible = Not IsNo
'        Sheets("Redistribution Calculations").Visible = Not IsNo
'
' 'Shows Spouts if no redistribution, and spouts2 if there is redistribution
        



End Sub

Answer

Try something like this:

Option Explicit

Dim f As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

   If f Then Exit Sub
   f = True


   ... your current code ...


   f = False

End Sub

Answer

Hi,

The error, "Method 'range' of object '_Worksheet' failed," usually occurs by trying to assign a value directly to a Range object that is a part of a Worksheet or by a Range object that hasn't been fully qualified.

Here is something I found on the internet that can help you-

https://community.spiceworks.com/how_to/195135-how-to-resolve-excel-run-time-error-1004

https://stackoverflow.com/questions/27802286/vba-getting-run-time-1004-method-range-of-object-worksheet-failed-when-us

Best Regards.

Answer

I have one user getting this error where others are not and they are using the same excel template.

Share via

How can I fix the error "Method 'range of object'_Worksheet' failed

3 answers