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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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
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
Best Regards.
I have one user getting this error where others are not and they are using the same excel template.