Target cell select

belilibom

New Member
Joined
Jul 4, 2023
Messages
9
Office Version
  1. 2010
Hi, i have code :
VBA 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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Could try it as below:
VBA 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 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
 
Upvote 1
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.

VBA 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 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
 
Upvote 1
Solution
Here is how I would do it:
VBA Code:
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
 
Upvote 1
Could try it as below:
VBA 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 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.
 
Upvote 0
Here is how I would do it:
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top