placing formula in cells

[Terry V]

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

 

[Norman Jones]

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

 

[Terry V]

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]

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.

 

[Jon Peltier]

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]

Hi Jon,

Thank you. An inadvertent typo on my part.

 

[Terry V]

Thank you Norman

This works like a charm.
Is there any simple way to know when to use R1C1 format?

Thanks again
Terry V

 

[Norman Jones]

Hi Terry,

Is there any simple way to know when to use R1C1 format?

Others may advance a more compelling rationale, but, in my case the choice
was dictated by predilection and force of habit.It is not, however,
mandatory and the formula could have been A1-notation based.

 

[Myrna Larson]

One rationale has to do with formulas that contain relative references. 1 cell
to the left is RC[-1], whereas in A1 notation, you have to know the address of
the cell containing the formula to know what to write, i.e. if it's C1, you
write B1. If it's K23, you write L23.

 

[Myrna Larson]

Oops... 1 cell to the left of K23 is J23, not L23.

One rationale has to do with formulas that contain relative references. 1 cell
to the left is RC[-1], whereas in A1 notation, you have to know the address of
the cell containing the formula to know what to write, i.e. if it's C1, you
write B1. If it's K23, you write L23.

Hi Terry,


Others may advance a more compelling rationale, but, in my case the choice
was dictated by predilection and force of habit.It is not, however,
mandatory and the formula could have been A1-notation based.

 

[Terry V]

Thank you all very much

Terry V
http://vanduzee.com