View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default Eliminate formula Query

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

--
Regards,
Tom Ogilvy

"John" wrote in message
...
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