You're still using .Value = .Value, which doesn't cause the problem.

I'm using .Formula = .Formula, and need to because that's what makes
the cell contents obey the text formatting after the user entered them
with other formatting.

Also, see Charles' note below that the problem only occurs with text
formatting; I hadn't noticed that text formatting was a requirement
for the problem to occur; I hadn't tested to see if the problem occurs
with other formatting, since I need the text formatting.

Greg


On Jun 11, 11:00 pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:
But when I started doing the range.Formula = range.Formula
above, I found that Worksheetfunction.CountA thinks every affected
cell is occupied, even if the cells are blank.

What's the issue then? This works around the problem you describe. If I type
in a value, then clear it using the delete key, CountA on A1:A10 works
correctly.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Me.Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
With Target
.NumberFormat = "@"
.Value = .Value
End With
Application.EnableEvents = True
End If
' MsgBox Application.WorksheetFunction.CountA(Me.Range("A1: A10"))
End Sub

or even

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Me.Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
Dim rngArea As Range
For Each rngArea In Target.Areas
With rngArea
.NumberFormat = "@"
.Value = .Value
End With
Next
Application.EnableEvents = True
End If
' MsgBox Application.WorksheetFunction.CountA(Me.Range("A1: A10"))
End Sub

--
Tim Zychwww.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Greg Lovern" wrote in message

...
No, I tried that before trying .Formula = .Formula.

On Jun 11, 8:11 pm, "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote:

Will .Value = .Value work for you instead? CountA doesn't seem to mind
that.

--
Tim Zychwww.higherdata.com
Compare data in worksheets and find differences with Workbook Compare
A free, powerful, flexible Excel utility

"Greg Lovern" wrote in message

...

If I run Range.Formula = Range.Formula on a given range of blank
cells, WorksheetfunctionCountA returns 1 for each cell in the range,
indicating occupied cells.

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.

But today I discovered another problem. The code I inherited when I
was brought in uses Worksheetfunction.CountA to determine the next
blank row. But when I started doing the range.Formula = range.Formula
above, I found that Worksheetfunction.CountA thinks every affected
cell is occupied, even if the cells are blank.

Any thoughts on why that would be? Here's what I plan to try tomorrow
as a workaround -- any other ideas?

Dim vIsRangeEmpty As Variant
Dim iIsRangeEmptyCol As Long
Dim iIsRangeEmptyRow As Long
Dim iUboundIsRangeEmptyCol As Long
Dim iUboundIsRangeEmptyRow As Long

Function IsRangeEmpty(target_range As Range) As Boolean

On Error GoTo GenErr

vIsRangeEmpty = target_range

If Not IsArray(vIsRangeEmpty) Then
'single cell:
If vIsRangeEmpty = "" Then
IsRangeEmpty = True
Else
IsRangeEmpty = False
End If
Exit Function
End If

'multiple cells:
iUboundIsRangeEmptyRow = UBound(vIsRangeEmpty, 1)
iUboundIsRangeEmptyCol = UBound(vIsRangeEmpty, 2)

For iIsRangeEmptyCol = 1 To iUboundIsRangeEmptyCol
For iIsRangeEmptyRow = 1 To iUboundIsRangeEmptyRow
If vIsRangeEmpty(iIsRangeEmptyRow, iIsRangeEmptyCol) < ""
Then
IsRangeEmpty = False
Exit Function
End If
Next iIsRangeEmptyRow
Next iIsRangeEmptyCol

IsRangeEmpty = True

Exit Function
GenErr:
IsRangeEmpty = False
End Function- Hide quoted text -

- Show quoted text -