Intermittent Object Required Error with Target.Value

vbabasic

New Member
Joined
Sep 22, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
Building out a couple of processes for a spreadsheet which are dependent on the selection made in the dropdown list, but running into an intermittent Object Required error.
It's weird because it was working for a minute, then nope.

1. If cell value in column AP on 'Data' sheet changes to "Remove", then copy the information to the new row on the 'Former' sheet and delete data row entirely from the 'Data' sheet.

2. If cell value in column AP on 'Data' sheet changes to "Reopen", then copy the information to the new row on the 'Former' sheet, then clear/change specific cells on the 'Data' sheet in the target row.

3. If cell value in column AP on the 'Former' sheet changes to "Return", then copy the information to the last row on the 'Data' sheet and delete data row entirely from the 'Former' sheet. (undoing 1, essentially).

Error is happening on "If Target.Value = "Reopen" Then" BUT only when you do a "Remove".
Reopening works as expected. Return also works as expected.

1&2 (in 'Data')
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AP:AP")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Former").Cells(Rows.Count, "AP").End(xlUp).Row + 1

If Target.Value = "Remove" Then
Rows(Target.Row).Copy Destination:=Sheets("Former").Rows(Lastrow)
Rows(Target.Row).Delete

End If
End If

If Target.Value = "Reopen" Then
Rows(Target.Row).Copy Destination:=Sheets("Former").Rows(Lastrow)

Cells(Target.Row, 6).Value = "VACANT"
'F
Cells(Target.Row, 11).ClearContents
Cells(Target.Row, 12).ClearContents
Cells(Target.Row, 13).ClearContents
'K-M
Cells(Target.Row, 18).ClearContents
Cells(Target.Row, 19).ClearContents
Cells(Target.Row, 20).ClearContents
Cells(Target.Row, 21).ClearContents
'R-U
Cells(Target.Row, 23).ClearContents
Cells(Target.Row, 24).ClearContents
'W-X
Cells(Target.Row, 27).ClearContents
Cells(Target.Row, 28).ClearContents
'AA-AB
Cells(Target.Row, 30).ClearContents
Cells(Target.Row, 31).ClearContents
Cells(Target.Row, 32).ClearContents
Cells(Target.Row, 33).ClearContents
Cells(Target.Row, 34).ClearContents
'AD-AH
Cells(Target.Row, 42).ClearContents
'AP

End If
End Sub


3 (in 'Former')
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AP:AP")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Dim Lastrow As Long
Lastrow = Sheets("Data").Cells(Rows.Count, "AP").End(xlUp).Row + 1

If Target.Value = "Return" Then
Cells(Target.Row, 6).Value = "Pending"
'F
Cells(Target.Row, 42).ClearContents
'AR
Rows(Target.Row).Copy Destination:=Sheets("Data").Rows(Lastrow)
Rows(Target.Row).Delete
End If
End If

End Sub
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,460
Office Version
  1. 2013
Platform
  1. Windows
In your code you're deleting Target's row at some point, so from then on Target refers to a destroyed range object.
If you intend to delete a particular worksheet range in its entirety which is part of the Target argument, you must do so at the end of the event handler.
Btw, I would recommend that you use Application.EnableEvents to prevent unwanted recursion. Set this property to False at the beginning of your code and to True at the end of your code.
 
Solution

vbabasic

New Member
Joined
Sep 22, 2021
Messages
4
Office Version
  1. 365
  2. 2019
  3. 2010
Platform
  1. Windows
This was it! I moved the "Remove" section below the "Clear" section and it worked like a charm. I also added in the recommended code.

Nothing was working at first, so I rebooted my computer and tried again with a fresh copy of the real workbook. I assume my Excel ran out of memory or something, but then it was working exactly as expected.

Appreciate the help!
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,460
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know.
 

Forum statistics

Threads
1,148,259
Messages
5,745,715
Members
423,969
Latest member
seanguerrero

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
Top