Eliminate formula Query
 

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

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

Eliminate formula Query
 

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

Eliminate formula Query
 

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.

--
Regards,
Tom Ogilvy

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