systematic wrote:
Hi there,

I'm trying to write a macro, so that if a cell in a range has a value
placed in it - Now() is written to a corresponding cell in another
column, then the value copied and pasted (so that it does not update).

I have this -

private sub worksheet_change(byval target as range)

if target.address(false, false) = \"f6:f45\" then
application.enableevents = false

if range(\"f6:f45\") = \"1\" then
range(\"d6:d45\").value = now()

else
range(\"d6:d45\") = \"no call taken\"

end if

application.enableevents = true

end if

range(\"d6:d45\").select
selection.copy
range(\"d6:d45\").select
selection.pastespecial paste:=xlpastevalues, operation:=xlnone,
skipblanks _
:=false, transpose:=false

end sub

For some reason it does not want to work for me. I have EnableEvents
set to True on workbook open and am running Office XP.

Would appreciate any advice to steer me in the right direction as I am
still quite new to VBA!

Thanks

Rob

Hi

try


Private Sub Worksheet_Change(ByVal Target As Range)


If Union(Target, Range("$F$6:$F$45")).Address = "$F$6:$F$45" Then
Application.EnableEvents = False
If Target.Value = 1 Then
Target.Offset(0, -2).Value = Now
Else
Target.Offset(0, -2).Value = "no call taken"
End If
Application.EnableEvents = True
End If

End Sub

Note the following changes:

1) VBA has its own version of Now() - no need to bother with paste
special

2) \ is not an escape character in VBA \" throws a syntax error

3) unless you have a reason to use relative addresses - just accept the
default behavior of range.address and insert $ where needed. To get a
feel of how things work, play around with the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Target.Address
End Sub

4) If you change *one* cell in F6:F45, then the address of the target
will be that of the changed cell and not all of F6:F45. If you want to
monitor a range for any change, you need to use the union operator as
above

5) the offset from the target has the effect of only changing the
corresponding cell in the D column, and not the entire range of cells

6) I always use the slightly verbose range.value rather than just range
(which defaults to value) - but this is a matter of taste. I find
explicit code easier to debug

7) I assume you didn't really want "1" for the value (as opposed to 1)
- if you really wanted the string you can revert to "1"

If you are new to VBA then Walkenbach's VBA books are great learning
tools.

Hope that helps

-John Coleman