instead of copy/paste special, try:

Activecell.Offset(0,4).Value = Activecell.Text

but if this is for an array lookup, why not use the Text function in your
array lookup formula?

for example
Match("Tue", Text(A1:A100, "ddd"), 0)



"NoodNutt" wrote:

G'day Everyone

I need a work around for the following please:

Case Target.Value = DateSerial(2008, 9, 1) And Target.Value <
DateSerial(2008, 10, 1)

ActiveCell.Offset(0, 1).Select
Selection.Value = "SEP" ' Column G, [no problem here.]
ActiveCell.Offset(0, 1).Select
Selection.Value = Target.Value 'Column H, in this case the cell is
formatted (dddd) which = Tue. [no probs here either.]

ActiveCell.Copy 'I then need to copy Column H result [Tue] to Column L

ActiveCell.Offset(0, 4).Select
Selection.PasteSpecial xlPasteValues 'Convert result, which in this case is
39707 to display [Tue] as TEXT

The reasoning behind this is that I have an array lookup, it doesn't work
unless I use the text equivalent, as it reads Column H as a date/time and
comes up as #N/A, I can then change the MATCH reference accordingly.

Look forward to your thoughts & suggestions

TIA
Mark.