Code to add Range Value produces nothing

[ssGuru]

I want to add a value in col 26 in the current row IF a change occurs
in Column 3 of that row.
I have a cell with a named range "RptCreator", in the worksheet
"InstructionPrice"
Any ideas why this code DOES NOT add any value to Col 26? or anywhere
else.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Pn As String

If Target.Column = 3 Then
r = Target.Row
c = Target.Column
Pn = Sheets("InstructionPrice").Range("RptCreator").Value
If Cells(r, c) = Empty Then
Cells(r, 26) = Pn
End If
End If
End Sub

Thanks, Dennis

 

[George Nicholson]

1) As written, Cells(r,c) = Target (the cell just changed). You know that,
right? (Then why not just use Target?)

2)

If Cells(r, c) = Empty Then

Column 26 should have RptCreator's value placed in it ONLY if you have just
*cleared* Target so that it is now Empty. It will not react to any other
change. Is that not what you are seeing?

HTH,

 

[Trevor Shuttleworth]

Dennis

If Cells(r, c) = Empty Then

Cells(r,c) is the target cell ... you pick up the values from the target row
and column. So the only way the above statement will be true is if you
delete the contents of the target cell.

You should also disable events before changing the cell as you'll loop
through the Change event again:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
Dim c As Long
Dim Pn As String

If Target.Column = 3 Then
r = Target.Row
c = Target.Column
Pn = Sheets("InstructionPrice").Range("RptCreator").Value
If Cells(r, c) = Empty Then
Application.EnableEvents = False
Cells(r, 26) = Pn
Application.EnableEvents = True
End If
End If
End Sub

Try deleting something in column C ... you'll get a value put in column Z

Regards

Trevor

 

[Guest]

Is the Target cell empty?