Copy Paste Values with array formula

D

Dennis

Using XL 2003 Home & 2000 Work

Used VBA to populate a range with array formulas.

Then copy-paste-values.

All seemed predictable except that:
every cell in that range had " ' " (single quote) inserted at the beginning of each cell
or, " ' " plus the calculated looked-up value, if any.

I perceive that " ' " " may force a cell format to "text" (The range was/is formatted "General")

That said, why did either VBA and/or XL cause the " ' " entry into each cell?

Or did I answer my question two lines above?

Is there a way to stop the " ' " insertion? (because it may cause countif() miscalculations)


TIA Dennis
 
D

Dave Peterson

I don't use xl2003 (I use xl2002 and xl2k), but this doesn't sound like excel to
me. (I've never seen xl do this on its own.)

And I'd hate to think that xl2003 did it by itself.

Maybe there's something in your VBA code that's doing that for you.

That said, I've seen excel show the single quote when the formula evaluated to
"" and then the cell was converted to values.

You can see that single quote if you toggle a Lotus 123 setting:
tools|options|transition tab
Check transition navigation keys

But enabling that option actually shows the PrefixCharacter in all the text
value cells.
 
D

Dave Peterson

It would be pretty difficult to believe if xl2003 behaved this way.

Maybe the OP could post the code that does the work.
 
D

Dennis

David and Frank


In this case, XL 2003 (up-to date)


On the Tools | Options tab no items are selected (no transition etc.)

Here is the code:

Sub ArrayCopy()
'
Selection.FormulaArray = _
"=IF(ISERROR(INDEX(FRE!R1C2:R318C2,SMALL(IF(FRE!R1C1:R318C1=RC1, _
ROW(FRE!R1C1:R318C1)),COLUMN()-6))),"""",INDEX(FRE!R1C2:R318C2, _
SMALL(IF(FRE!R1C1:R318C1=RC1,ROW(FRE!R1C1:R318C1)),COLUMN()-6)))"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A304"), _
Type:=xlFillDefault
ActiveCell.Range("A1:A304").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:AJ304"), _
Type:=xlFillDefault
ActiveCell.Range("A1:AJ304").Select
With Selection
.Copy
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False

End Sub

After Copy | Paste Special | Values; Every cell in the "range" has either " ' "
or ' (value displayed)

TIA Dennis
 
D

Dave Peterson

The only way I could see that apostrophe was if I had that setting toggled in
tools|options|transition tab.

And the text values would look like:

'asfd
(no spaces surrounding the apostrophe)

And they don't show up with numeric entries.

When I do it, these don't affect any simple =countif() formulas. But they will
affect =counta() formulas.

If you want to really empty your cells you could use this technique:

with activesheet.Range("A1:AJ304")
.value = .value
End With

instead of copy|paste special values.
 
Top