Method of Range object failed Target.Address

[BKrukowski]

Normally I can figure these errors out but I am stumped. I am getting the dreaded runtime 1004 Method 'Range' of object failed. The issue I am having appears to be that I cannot set a range to the Target when using Worksheet_Change event handler

Here is the VBA:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range
Dim thisRange As Range
Dim unitCost As Long
Dim contractYr As Integer
contractYr = Range("C2").Value

Set thisRange = Range(Target.Address) //HERE IS THE ISSUE HOW TO SET thisRange so I can go back to it later

MsgBox (thisRange) // this msgbox does not show a range it shows the Target.Value


Set MyRange = Range("A10:A15")
If Intersect(Target, MyRange) Is Nothing Then
Exit Sub
End If
Dim lookUpVal As String
lookUpVal = Target.Value
Worksheets("Sheet2").Activate
Sheets("Sheet2").Range("A4:A21").Select
Selection.Find(lookUpVal).Activate
If contractYr = 2017 Then
    unitCost = ActiveCell.Offset(0, 1).Value
End If
If contractYr = 2018 Then
    
     unitCost = ActiveCell.Offset(0, 2).Value
End If

Worksheets("Sheet1").Activate
Range(thisRange).Activate            // HERE IS THE DEBUG POINTER 
ActiveCell.Offset(0, 4).Value = unitCost
 
End Sub

 

[V_Malkoti]

Try using thisRange.Activate instead of Range(thisRange).Activate

 

[Bill Rockenbach]

Try

Dim thisRange As Range
 Set thisRange = Target
MsgBox (thisRange.Address)

 

[BKrukowski]

Thank you - problem solved

 

[Bill Rockenbach]

Your Welcome