Problem with line of code / syntax
 

I am having problems with syntax.

I use the code below to enter formulas into cells . It is embedded in a sub
routine.

The code works finr for the first formula line (cell.offset), but the whole
thing breaks down for the second formula line.

The VBA code doesnt like the parentheses (thinks its the end of the
statement) and it doesnt seem to like the use of specific cell references
either (M$4)

Range("AK18:AK77").Select
Selection.ClearContents

For Each cell In Range("AO18:AO77")
Select Case cell.Value
Case "1"

cell.Offset(, -4).FormulaR1C1 = "=RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]"
cell.Offset(, -1).FormulaR1C1 = "=IF(ISERROR(RC[-1]/M$4),"Data?",RC[-1]/M$4)"
End Select
Next

Can anyone help with suggestions as I have alot of other similar formulas to
add which also contain cell references (some on other sheets) and other
statements within parentheses.

Thanks,

Roger

Problem with line of code / syntax
 

try extra quotes around the word data?.

""data?""
cell.Offset(, -1).FormulaR1C1 = "=IF(ISERROR(RC[-1]/M$4),""Data?"",RC[-1]/M$4)"
also, a couple other things i'd do:

1. use a sheet name in your references, to make sure it always uses the correct
sheet

set ws = worksheets("Sheet1")
For Each cell In ws.Range("AO18:AO77")

2. no need to select

ws.Range("AK18:AK77").ClearContents





--


Gary Keramidas
Excel 2003


"Roger on Excel" wrote in message
...
I am having problems with syntax.

I use the code below to enter formulas into cells . It is embedded in a sub
routine.

The code works finr for the first formula line (cell.offset), but the whole
thing breaks down for the second formula line.

The VBA code doesnt like the parentheses (thinks its the end of the
statement) and it doesnt seem to like the use of specific cell references
either (M$4)

Range("AK18:AK77").Select
Selection.ClearContents

For Each cell In Range("AO18:AO77")
Select Case cell.Value
Case "1"

cell.Offset(, -4).FormulaR1C1 = "=RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]"
cell.Offset(, -1).FormulaR1C1 = "=IF(ISERROR(RC[-1]/M$4),"Data?",RC[-1]/M$4)"
End Select
Next

Can anyone help with suggestions as I have alot of other similar formulas to
add which also contain cell references (some on other sheets) and other
statements within parentheses.

Thanks,

Roger

Problem with line of code / syntax
 

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Roger on Excel" wrote in message
...
I am having problems with syntax.

I use the code below to enter formulas into cells . It is embedded in a
sub
routine.

The code works finr for the first formula line (cell.offset), but the
whole
thing breaks down for the second formula line.

The VBA code doesnt like the parentheses (thinks its the end of the
statement) and it doesnt seem to like the use of specific cell references
either (M$4)

Range("AK18:AK77").Select
Selection.ClearContents

For Each cell In Range("AO18:AO77")
Select Case cell.Value
Case "1"

cell.Offset(, -4).FormulaR1C1 =
"=RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]"
cell.Offset(, -1).FormulaR1C1 =
"=IF(ISERROR(RC[-1]/M$4),"Data?",RC[-1]/M$4)"
End Select
Next

Can anyone help with suggestions as I have alot of other similar formulas
to
add which also contain cell references (some on other sheets) and other
statements within parentheses.

Thanks,

Roger

Problem with line of code / syntax
 

If you're going to use .formular1c1, then you have to use R1C1 reference style
for each cell in the formula--and M4 is not r1c1 reference style.

I'd use something like this:

Dim cell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.Range("AK18:AK77").ClearContents

For Each cell In .Range("AO18:AO77").Cells
Select Case cell.Value
Case is = "1"
cell.Offset(0, -4).FormulaR1C1 _
= "=RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]"
cell.Offset(0, -1).FormulaR1C1 _
= "=IF(ISERROR(RC[-1]/r4c13),""Data?"",RC[-1]/r4c13)"
End Select
Next cell
End With



Roger on Excel wrote:

I am having problems with syntax.

I use the code below to enter formulas into cells . It is embedded in a sub
routine.

The code works finr for the first formula line (cell.offset), but the whole
thing breaks down for the second formula line.

The VBA code doesnt like the parentheses (thinks its the end of the
statement) and it doesnt seem to like the use of specific cell references
either (M$4)

Range("AK18:AK77").Select
Selection.ClearContents

For Each cell In Range("AO18:AO77")
Select Case cell.Value
Case "1"

cell.Offset(, -4).FormulaR1C1 = "=RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]"
cell.Offset(, -1).FormulaR1C1 = "=IF(ISERROR(RC[-1]/M$4),"Data?",RC[-1]/M$4)"
End Select
Next

Can anyone help with suggestions as I have alot of other similar formulas to
add which also contain cell references (some on other sheets) and other
statements within parentheses.

Thanks,

Roger

--

Dave Peterson

Problem with line of code / syntax
 

Thanks Dave - this syntax does the trick for me

Roger

"Dave Peterson" wrote:

If you're going to use .formular1c1, then you have to use R1C1 reference style
for each cell in the formula--and M4 is not r1c1 reference style.

I'd use something like this:

Dim cell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks
.Range("AK18:AK77").ClearContents

For Each cell In .Range("AO18:AO77").Cells
Select Case cell.Value
Case is = "1"
cell.Offset(0, -4).FormulaR1C1 _
= "=RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]"
cell.Offset(0, -1).FormulaR1C1 _
= "=IF(ISERROR(RC[-1]/r4c13),""Data?"",RC[-1]/r4c13)"
End Select
Next cell
End With



Roger on Excel wrote:

I am having problems with syntax.

I use the code below to enter formulas into cells . It is embedded in a sub
routine.

The code works finr for the first formula line (cell.offset), but the whole
thing breaks down for the second formula line.

The VBA code doesnt like the parentheses (thinks its the end of the
statement) and it doesnt seem to like the use of specific cell references
either (M$4)

Range("AK18:AK77").Select
Selection.ClearContents

For Each cell In Range("AO18:AO77")
Select Case cell.Value
Case "1"

cell.Offset(, -4).FormulaR1C1 = "=RC[-9]&RC[-8]&RC[-7]&RC[-6]&RC[-5]&RC[-4]"
cell.Offset(, -1).FormulaR1C1 = "=IF(ISERROR(RC[-1]/M$4),"Data?",RC[-1]/M$4)"
End Select
Next

Can anyone help with suggestions as I have alot of other similar formulas to
add which also contain cell references (some on other sheets) and other
statements within parentheses.

Thanks,

Roger

--

Dave Peterson
.