Eliminate formula Query

[John]

I received this code a few months back that eliminates formulas in certain
rows (Sales!A3:A55) where ever a certain date, which is is in C.O.S!H4, then
just copy/paste values, those formulas that are in the cells. Code is as
below

Problem is that a highlighted "rCell" displays compile error shows up -
can't find project or libary.

I have tested the code on just on a blank file with basic data over 2
worksheets and it works fine - where have I gone wrong??

Public Sub SetupDatabase()
Dim wsSheet As Worksheet
Dim dTest As Double
dTest = Worksheets("C.O.S").Range("H4").Value
For Each wsSheet In Sheets(Array("Sales"))
With wsSheet
For Each rCell In .Range("A3:A55")
If rCell.Value = dTest Then
With rCell.Offset(0, 1).Resize(, 26)
.Value = .Value

End With
End If
Next rCell
End With
Next wsSheet

 

[Tom Ogilvy]

I don't see anything that would produce that error unless you go to
tools=>References in the VBE with your workbook as the active project and
you see a reference marked as MISSING - that can cause that error in unusual
situations.

if that isn't the case, try declaring the rcell variable

Public Sub SetupDatabase()
Dim wsSheet As Worksheet
Dim dTest As Double
Dim rCell as Range

 

[John]

Yes there is a Reference checked titled "Autosave.xla". Does that mean I
have to enable Autosave on my PC in order for the macro to execute? The PC
which the file I'm working on is located does have Autosave turned on and
the small test file which I created on my PC and worked with the code has it
turned off

Thanks

 

[Tom Ogilvy]

Having a reference checked isn't a problem unless it is marked as MISSING.
If it is, then that can cause problems or strange behavior.