Odd Formula copying Behavior

[PatK]

I have a very odd situation going on with excel 2007, and thought I would try
a post here, before I try a total Office re-install:

I have an excel spreadsheet with a variety of formulas in the rows.
Starting today, when I copy/paste a formula from one row, to a row
immediately below it (using drag copy), rather than the formula being copied
as it has always done, instead (and here is the odd part), it seems to paste
the result of the formula I am copying into that cell (no more formula).
Additionally, if instead of dragging to copy just one cell, I drag to copy to
several cell rows, it works as normal. Very strange.

Same applies if I use Ctrl-C, Ctrl-V copy, etc.

Hope that makes sense. About ready to do an uninstall, reinstall, before a
total system rebuild, if that doesn't work. But if there is an easy fix,
would appreciate any insights.

Thanks!

PatK

 

[Shane Devenshire]

Hi,

First thing I would check is whether Manual recalc is on - choose Formulas,
Calculation Options, Automatic.

 

[PatK]

Hey, Shane! thanks for the reply!

Yes, Calc is set to automatic, vs manual.

This is the strangest behavior I have ever seen. Drag copy down one
cell...you get the calculated result (in this case, vlookups pulling content
from another page), and the formula completely goes away, and in it's place
is the "value" from the vlookup.

But, if I drag/copy down 2 rows, or more, it works just fine. (by drag
copy, I mean holding the little black box in the lower corner of the selected
cell, and dragging downward).

I am open to trying anything!

Thanks again!
pat

 

[PatK]

Ok...I figured it out. A few weeks back, I had found a neat set of code out
here that someone wrote to track rows that had changed (code pasted below).
That code was tied to the worksheet itself.

It appears that, with that code implemented, it caused the problem that I
saw. not sure exactly what, in the code caused this . Here is code. I might
post this in that same thread in the programming group, should someone else
experience this.

The row: IF Target.Cells.Count > 1 Then Exit Sub

probably explains why it did not occur when I copied/pasted (changed) more
than one cell. Would like to figure this out, tho, as the code really does
work nice in keeping a history.

Appreciate all who may have stared at this....

PatK

CODE:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
Dim myVal As Variant
If Target.Cells.Count > 1 Then Exit Sub
With Application
.EnableEvents = False
.ScreenUpdating = False
myVal = Target.Value
.Undo
myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).row
Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _
Sheets("History Sheet").Cells(myRow, 3)
Sheets("History Sheet").Cells(myRow, 2).Value = Now
Sheets("History Sheet").Cells(myRow, 1).Value = .UserName
Target.Value = myVal
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub