Excel VBA works fine but not in two workbooks. Help

[Guest]

I have some code in Excel that works fine all the time but suddenly, it won't
work in two workbooks that are generated from the Office Link in Access. To
make matters worst, it work fine yesterday in the two workbooks. I have no
idea what is going on. Can anyone help? Thanks.

 

[JE McGimpsey]

Other than taking a wild guess, you don't provide nearly enough
information to diagnose a problem.

What does the macro do (post it in a reply if it's not too long). What
type of data does it operate on? Do you see the same problem if you
generate another workbook (other than the two problem ones)?

What changed in your Access, Office Link, or XL setup?

 

[Guest]

The macro copies the content of a cell down to the last used cell in the
column to the left.

Sub Filldown()
Range ("E1").Select
If IsEmpty(ActiveCell) Then Exit Sub
Range(ActiveCell, Cells(Rows.Count, _
ActiveCell.Column - 1).End(xlUp).Offset(0, 1)).FillDown
End Sub

In my case, I use this to copy a formula in E1 down. What is happening in
the two workbooks is that the actual value of cell E1 is getting copied
instead of the formula. The code works fine in all my other workbooks just
the two that get generated from Access.

Nothing has changed in my database or setup.

It is really baffeling to me. Any help would be appreciated. Thanks.

 

[Guest]

Let me clarify a little more. When you run the code, what you see in all
rows in Column E is what the value of the formula in cell E1 is. But if you
go into each individual cell in Column E, you will see that the formula did
get copied correctly but is now showing the proper results. If you manually
enter the formula in say E3, you get the right results.

 

[JE McGimpsey]

One way:

With Range("E1:E" & Range("D" & Rows.Count).End(xlUp).Row)
.Formula = .Cells(1).Formula
End With

 

[Guest]

Thanks very much. Worked like a charm. Any ideas though on why my code
wouldn't work in those two workbooks?

 

[JE McGimpsey]

Then you probably have calculation set to manual. Change it back to
automatic (Tools/Options/Calculation)