Hi Jon,

Thank you. An inadvertent typo on my part.

---
Regards,
Norman



"Jon Peltier" wrote in message
...
Use DOT-range:

With Sheets("Sheet1")
.Range("C7:E26").FormulaR1C1 = _
"=IF(RC2=""off"",""off"","""")"
End With


- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______


Norman Jones wrote:

Hi Terry,

I think that you can simplify your code to:

With Sheets("Sheet1") '<<=== CHANGE
Range("C7:E26").FormulaR1C1 = _
"=IF(RC2=""off"",""off"","""")"
End With

Amend the sheet name to accord with your requirements.

---
Regards,
Norman



"Terry V" wrote in message
...

Yes I see what you mean about the circular reference.

The range(r).formula = "=IF(" & Range(r) & "="Off","Off","")" should
actually be column(r).offset(0,-1)
In essence, if, on the row that the formula is being inserted, the B
column
=Off.

What Im trying to do, is simple in the worksheet to do. However, Im
preventing from having errors come up in cells. So I placed this formula
in
(which gets overwritten by "data validation/ list"). This formula
actually
makes it easier for the user because they do not have to enter the word
"Off" for each column that requires time entry (scheduled time start,
Scheduled time end, Actual time start and Actual time end). The word Off
could be placed in each of these columns when the first column (B) has
the
word Off selected from the data validation drop down.

This is very difficult to explain, but I hope you get an idea.
Columns B,C,D,E contain the Scheduled and Actual start and end times.
All
have data validation applied that allows the user to select a time from a
list (drop down). When the user Selects "Off" from the drop down (column
B), the values on that row for Columns C, D, and E all automatically say
"Off"; other wise they are left blank until the user selects the
appropriate
time for each cell.

Thank you So much
Terry V



"Norman Jones" wrote in message
...

Hi Terry,

In your fomula change each instance of "off" to ""off"", i.e. double up

the

quotes.

As written, your formula will return a circular reference.

If you explain the formula that you need, I am sure that your code can
be
simplified


---
Regards,
Norman



"Terry V" wrote in message
...

Hello
I know this probably sounds like a really silly question, but Ive been
racking my brains trying to figure it out.

I have a Workbook_Open() , so that when the workbook opens, it controls
scrolling.

I also need to place a formula in several cells.
My problems are these:
I have a range - C7:E26 that I need to add a formula to.

In the for loop, Im trying to go to each cell in the range:
Dim rng as range
Dim cl
Dim r as range
Set rng = range("C7:E26")
For each cl in rng
set r = range(cl.address)
range(r).formula = "=IF(" & Range(r) & "="Off","Off","")"
Next cl

Now, I know where the problem lies, and it is in the "". This is
something
that has plaqued me for quite a while.
The other problem is that "set r = range(cl.address)" returns a value

not

a
range object.

How can I fix this?

Thank you
Terry