1004-Applcation-defined or object-defined error

[Guest]

Have a function that copies what a user inputs in one cell to the adjacent
cell if certain conditions are met. Works fine when I type the value into
the cell, but when I select the value from a validation list I recieve the
error 1004. Simplified the condition on the code below. When I place a
break on .Value = Target.Value, Target.Value contains the value selected from
the validation list.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR_CleanUp
Dim i_row As Integer
Dim i_column As Integer

Application.DisplayAlerts = False
Application.EnableEvents = False

i_row = Target.Row
i_column = Target.Column
If Target.Address = "$P$5" Then
With Worksheets(1).Cells(i_row, i_column + 1)
.Value = Target.Value
End With
End If
CleanUp:
Application.DisplayAlerts = True
Application.EnableEvents = True
Exit Sub
ERR_CleanUp:

If Err.Number = 1004 Then
MsgBox "Excel has had a brain fart.", vbInformation
Else
MsgBox Err.Number & "- " & Err.Description
End If
Resume CleanUp
End Sub

 

[Guest]

Try changing $P$5 to Cells(5, 16). That will put your taget and your offset
in the same type cell reference.

 

[Guest]

Give this a whirl...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ERR_CleanUp

Application.EnableEvents = False
If Target.Address = "$P$5" Then Target.offset(0,1).Value = Target.Value
CleanUp:
Application.EnableEvents = True
Exit Sub
ERR_CleanUp:

If Err.Number = 1004 Then
MsgBox "Excel has had a brain fart.", vbInformation
Else
MsgBox Err.Number & "- " & Err.Description
End If
Resume CleanUp
End Sub

 

[Guest]

Still the same problem, works fine when I enter the value into the cell but
errors when I pick from the validation list.

 

[shashisharath.navada]

In case of situation like Worksheet("data").Range(variable,what could
be the format of value in variable???

thanks.