Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with syntax in line of code | Excel Programming | |||
Syntax problem with code - chip.gorma | Excel Programming | |||
Syntax problem with code | Excel Programming | |||
Syntax problem with code | Excel Discussion (Misc queries) | |||
Syntax Problem with formula code | Excel Programming |