Target cell select

[belilibom]

Hi, i have code :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim DataRange As Range
Set DataRange = Range("D2:D50")
If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, DataRange).Value = "Daftar" Then
        Target.Offset(0, 1).Select
    End If
    If Intersect(Target, Range("D2:D50")).Value = "NA" Then
        Target.Offset(0, 2).Select
    End If
end Sub

It is working to select taget cell. But when the cell be edited, error message appears i.e : "Object variable or with block variable not set."

How to solve this problem ??
Thanks a lot.

 

[Georgiboy]

Could try it as below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DataRange As Range
    
    Set DataRange = Range("D2:D50")
    
    If Target.Cells.Count > 1 Then Exit Sub
    
    If Not Intersect(Target, DataRange) Is Nothing Then
        If Target = "Daftar" Then
            Target.Offset(0, 1).Select
        ElseIf Target = "NA" Then
            Target.Offset(0, 2).Select
        End If
    End If
End Sub

 

[Peter_SSs]

That is because this part of the code If Intersect(Target, DataRange).Valueis trying to determine the value where Target intersects DataRange. The problem is that when you edit that cell, Target does not intersect DataRange so the code is trying to get the value from a range that does not exist - hence the error.

Try this instead.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim DataRange As Range
 
  Set DataRange = Range("D2:D50")
  If Target.Cells.Count > 1 Then Exit Sub
  If Not Intersect(Target, DataRange) Is Nothing Then
    If Intersect(Target, DataRange).Value = "Daftar" Then
      Target.Offset(0, 1).Select
    End If
    If Intersect(Target, DataRange).Value = "NA" Then
      Target.Offset(0, 2).Select
    End If
  End If

 

[My Aswer Is This]

Here is how I would do it:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 And Target.Row > 1 And Target.Row < 51 Then
  
  If Target.Cells.Count > 1 Then Exit Sub
  
    Select Case Target.Value
        Case "Daftar": Target.Offset(0, 1).Select
        Case "NA": Target.Offset(0, 2).Select
    End Select
End If
End Sub

 

[belilibom]

Could try it as below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim DataRange As Range
   
    Set DataRange = Range("D2:D50")
   
    If Target.Cells.Count > 1 Then Exit Sub
   
    If Not Intersect(Target, DataRange) Is Nothing Then
        If Target = "Daftar" Then
            Target.Offset(0, 1).Select
        ElseIf Target = "NA" Then
            Target.Offset(0, 2).Select
        End If
    End If
End Sub

Thanks a lot for your fast respon. It works perfectly.

 

[belilibom]

Here is how I would do it:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 4 And Target.Row > 1 And Target.Row < 51 Then
 
  If Target.Cells.Count > 1 Then Exit Sub
 
    Select Case Target.Value
        Case "Daftar": Target.Offset(0, 1).Select
        Case "NA": Target.Offset(0, 2).Select
    End Select
End If
End Sub

Thanks so much for being so kind to share another way. makes me richer in viewing the path of a point.