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

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

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

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

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

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

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

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.


---
Regards,
Norman



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

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.

On Wed, 29 Sep 2004 05:03:49 +0100, "Norman Jones"
wrote:

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.


---
Regards,
Norman



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

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

On Tue, 28 Sep 2004 23:49:31 -0500, Myrna Larson
wrote:

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.

On Wed, 29 Sep 2004 05:03:49 +0100, "Norman Jones"
wrote:

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.


---
Regards,
Norman



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

Thank you all very much

Terry V
http://vanduzee.com

"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