.Formula = .Formula breaks End method

[Greg Lovern]

If I run Range.Formula = Range.Formula on a given range of blank cells
formatted as text, the following problems occur:

-- Range.End treats those cells as occupied, not as blank.
-- Worksheetfunction.CountA treats those cells as occupied, not as blank.
-- Saving out as a tab-delimited text file saves out a tab character between
each affected cell, as though the cells were occupied.

In our project, users paste long text strings that look like numbers to
Excel. Even if I protect the formatting, Excel allows pasting in formatting,
including number formatting. And these users often copy from other rich-text
sources such as Outlook, then paste into Excel, so what looked like
123456789012 in Outlook looks like 1.234567E+11 when they paste it in. I've
discussed paste special | values, and copying to Notepad,and I even provided
an import feature. But of course they're still pasting from Outlook. Oh, and
there are tens of thousands of users, and growing.

I've been asked to make it automatically convert back to text format, so on
the worksheet's Change event I'm changing the target range's number
formatting back to "@" (which is how we send the workbook out to users), and
since that alone isn't enough, I then do a range.Formula = range.Formula on
the target range, equivalent to pressing F2 and then Enter. Yes, I realize
any leading zeros are lost forever, along with any digits after the 15th, and
I've covered that with them, but this is what they want.


Steps to reproduce the problem:

-- In a new sheet, select row 1 (the whole row).
-- Set the number formatting of that row to Text (right-click anywhere in
the selected row | Number tab | Text).
-- enter any data in cells A1:C1.
-- In the VBA Immediate pane, paste in this command and press enter:
activesheet.range("A1").end(xltoright).select
-- it should select cell C1. So far so good.
-- In the VBA Immediate pane, paste in this command and press enter:
activesheet.rows(1).formula = activesheet.rows(1).formula
-- Then run the first command again:
activesheet.range("A1").end(xltoright).select
-- This time, it does NOT select cell C1. Instead, it selects cell XFD1 if
you are in Excel 2007, or cell IV1 if you are in Excel 2003 or earlier. This
is the same result you would get if you had entered data in ALL cells in row
1.


Why does this happen?


Thanks,

Greg Lovern
http://PrecisionCalc.com
More Power In Excel

 

[Bob Phillips]

If you look at the Value property of any of the cells beyond C, then you
will see that have the value "". If you look at the equivalent cell on row
2, you will see that Value is Empty. Setting a range to Value or Formula
create a value, it is empty no longer; even though that value is blank, it
is not empty.

Why not calculate the row end and just set that range, or are you doing it
on all rows in one hit?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Greg Lovern]

In that case, I would expect the same behavior if the cells were formatted as
General. But I only get this behavior if the cells were formatted as Text.

So is the behavior wrong for cells formatted as General, or is the behavior
wrong for cells formatted as Text?

Or is there some reason your explanation doesn't apply to cells formatted as
General?

Why not calculate the row end and just set that range, or are you doing it
on all rows in one hit?

I greatly simplified for the sake of simple repro steps. In our
spreadsheets, the problem applies to finding rows too, not just columns, and
the users are always adding and deleting data, changing which cell would be
the last, etc.

Greg

 

[Greg Lovern]

I forgot to add --

From your (Bob Phillips) explanation, I would expect the same behavior
if I did it interactively instead of programmatically. The interactive
equivalent of .Formula = .Formula is F2, then Enter (or, mouse-click
in the formula bar, then Enter). But the problem behavior does not
occur if I F2/Enter; the problem behavior only occurs if I .Formula
= .Formula, and then only if the cells had been formatted as text.

Why would it be correct for the problem behavior to only occur
with .Formula = .Formula on cells that had been formatted as text, and
not occur with F2/Enter or with cells not formatted as text?

Greg

 

[Charles Williams]

Hi Greg,

Not sure why the problem exists, I suspect something to do with implicit
coercion of datatypes and the fact that an empty string is not Null.

If you are still looking for a bypass try something like this

Sub GregFix()
Dim v1 As Variant
Dim v2 As Variant
Dim j As Long
Dim k As Long
Dim oCell As Range

v1 = Range("a1:a20").Value2
v2 = Range("a1:a20").NumberFormat

If IsNull(v2) Then
'
' v2 is null if the range contains more than one numberformat
'
For j = 1 To UBound(v1, 1)
For k = 1 To UBound(v1, 2)
If Not IsEmpty(v1(j, k)) Then
Set oCell = Range("a1").Offset(j - 1, k - 1)
If oCell.NumberFormat <> "@" Then
v1(j, k) = Format(v1(j, k), "0")
oCell.NumberFormat = "@"
oCell.Value2 = v1(j, k)
End If
End If
Next k
Next j
End If

End Sub


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Greg Lovern]

HiGreg,

Not sure why the problem exists, I suspect something to do with implicit
coercion of datatypes and the fact that an empty string is not Null.

That wouldn't seem to explain why the problem doesn't happen on F2/
Enter. Recording F2/Enter shows that it sets the cell's value to an
empty string.

If you are still looking for a bypass try something like this

I'm okay re workarounds. As for the example you give here, on our real
sheets it would have to be run on the whole sheet, and very
frequently. It would be way too slow.

I posted here because it was recommended elsewhere as a good way to
get a bug noticed by MS (without paying $259 for a support incident).
But I hadn't noticed at first that this is just a root of the same
newsgroup I'd originally posted to.

When I was on a contract at Microsoft for a year recently, I posted my
bugs to Offbug, and internal tool there for reporting Office bugs. I
got good responses from that. But when contracting at other clients, I
don't seem to have a good way to report bugs, at least not that I've
been able to find.


GregLovern
http://PrecisionCalc.com
More Power In Excel

 

[Charles Williams]

Greg

That wouldn't seem to explain why the problem doesn't happen on F2/
Enter. Recording F2/Enter shows that it sets the cell's value to an
empty string.

If you F2/enter you are not going via VBA.
As soon as you go via VBA you get potential implicit type conversion, by
design.
Empty string is not the same thing as either Null or Empty

.... Not sure that this is a bug but good luck with reporting it!!!

I'm okay re workarounds. As for the example you give here, on our real
sheets it would have to be run on the whole sheet, and very
frequently. It would be way too slow.

I was just outlining an approach: not giving you the final solution.
I think it would be fairly easy to optimise for fast enough speed,
faster than your current .Formula=.Formula approach

Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com