Announcement

Collapse
No announcement yet.

Worksheet_Change(ByVal Target As Range) throws error

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Worksheet_Change(ByVal Target As Range) throws error



    trying to have cell value change based on another cell value change

    code below throws this error

    Click image for larger version

Name:	glob.png
Views:	5
Size:	4.8 KB
ID:	1221701

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim intersection As Range
        Set intersection = Intersect(Target, Worksheets("Sheet1").Range("G1"))
        If Not intersection Is Nothing Then
            If Worksheets("Sheet1").Range("G1").Value = Worksheets("Sheet20").Range("K2").Value Then
            Worksheets("Sheet1").Range("F2").Value = Worksheets("Sheet20").Range("I2").Value
            End If
        End If
    End Sub
    Attached Files

  • #2
    Give this a try:

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target = Range("G1") Then
            If Target.Value2 = Sheets("Sheet20").Range("K2") Then
                Sheets("Sheet1").Range("F2") = Sheets("Sheet20").Range("I2")
            End If
        End If
    End Sub
    Alan

    Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
    FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8

    If someone has helped you, say "thank you" by clicking on the Like Button.

    Comment


    • #3
      on line

      Code:
      If Target.Value2 = Sheets("Sheet20").Range("K2") Then
      Click image for larger version

Name:	9.png
Views:	5
Size:	4.4 KB
ID:	1221707

      Comment


      • #4


        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
          Dim r As Range
          
          Set r = Intersect(Target, Range("G1"))
          If r Is Nothing Then Exit Sub
          
          Application.EnableEvents = False
          If r.Value = Sheets("Sheet20").Range("K2") Then _
            Range("F2") = Sheets("Sheet20").Range("I2")
          Application.EnableEvents = True
        End Sub

        Comment

        Working...
        X