Populating IF Formula while looping - trouble with index interpret
 

I need to populate this IF into all cells in my spreadsheet (many others to
do as well). Somehow, my code is inserting the row index but it's the
variable name and not the value,,,resulting in this value
=IF(O(rIndex)=0,0,N(rIndex)/O(rIndex))

What am I missing? Is there an easier way to do this? Users may or may not
have 2007 loaded - will that be a consideration? Newbie to Excel coding!

Sub var_RE()
rIndex = 2 'set row to 2
For rIndex = 2 To 12
Cells(rIndex, 17).Formula = "=IF( [RE_Current] = 0, 0,
[Total_MTD] / [RE_Current])"
Next rIndex
End Sub

Populating IF Formula while looping - trouble with index interpret
 

Is RE_Current a range name that is being evaluated in this code? How does
it relate to O(rIndex), indeed what is O(rIndex)?

It might just be

Cells(rIndex, 17).Formula = "=IF(" & Range("RE_Current").Value
& _
"= 0,0," &
Range("Total_MTD").value & _
"/" &
Range("RE_Current").Value & ")"

--
__________________________________
HTH

Bob

"pardoxchic" wrote in message
...
I need to populate this IF into all cells in my spreadsheet (many others to
do as well). Somehow, my code is inserting the row index but it's the
variable name and not the value,,,resulting in this value
=IF(O(rIndex)=0,0,N(rIndex)/O(rIndex))

What am I missing? Is there an easier way to do this? Users may or may not
have 2007 loaded - will that be a consideration? Newbie to Excel coding!

Sub var_RE()
rIndex = 2 'set row to 2
For rIndex = 2 To 12
Cells(rIndex, 17).Formula = "=IF( [RE_Current] = 0, 0,
[Total_MTD] / [RE_Current])"
Next rIndex
End Sub

Populating IF Formula while looping - trouble with index inter
 

RE_Current and Total_MTD are headings for the columns. I found I could use
the heading labels instead of column references and I choose that method due
to self-documention. rIndex is the row variable I'm using to loop through all
the rows (well, only through 12 right now while I test).

I didn't initially define a range. Wouldn't I need 2 ranges since there are
2 difference references? An o range for column "O" and an n range for column
"N"? I'm attempting to test your code but having trouble with syntax. Thanks!!

Sub var_RE()
Dim oRange As Range
Set oRange = Worksheets("Sheet1").Range("O2:O2")
Dim myRange As Range
Set nRange = Worksheets("Sheet1").Range("N2:N2")
For rIndex = 2 To 12 Cells(rIndex,17).Formula = "=IF(" &
oRange("RE_Current").Value _
& "= 0,0," & nRange("Total_MTD").value & "/" & oRange("RE_Current").Value &
")""
Next rIndex
End Sub

"Bob Phillips" wrote:

Is RE_Current a range name that is being evaluated in this code? How does
it relate to O(rIndex), indeed what is O(rIndex)?

It might just be

Cells(rIndex, 17).Formula = "=IF(" & Range("RE_Current").Value
& _
"= 0,0," &
Range("Total_MTD").value & _
"/" &
Range("RE_Current").Value & ")"

--
__________________________________
HTH

Bob

"pardoxchic" wrote in message
...
I need to populate this IF into all cells in my spreadsheet (many others to
do as well). Somehow, my code is inserting the row index but it's the
variable name and not the value,,,resulting in this value
=IF(O(rIndex)=0,0,N(rIndex)/O(rIndex))

What am I missing? Is there an easier way to do this? Users may or may not
have 2007 loaded - will that be a consideration? Newbie to Excel coding!

Sub var_RE()
rIndex = 2 'set row to 2
For rIndex = 2 To 12
Cells(rIndex, 17).Formula = "=IF( [RE_Current] = 0, 0,
[Total_MTD] / [RE_Current])"
Next rIndex
End Sub

Populating IF Formula while looping - trouble with index inter
 

My code won't work if they aren't range names.

I see what you are doing, it is a 2007 table?

With tables, you only have to put the formula in the first row, the table
functionality auto-populates the rest. And it is FormulaR1C1 for some
reason.

Cells(2, 17).FormulaR1C1 =
"=IF([RE_Current]=0,[Total_MTD]/[RE_Current])"

is all you need.

--
__________________________________
HTH

Bob

"pardoxchic" wrote in message
...
RE_Current and Total_MTD are headings for the columns. I found I could use
the heading labels instead of column references and I choose that method
due
to self-documention. rIndex is the row variable I'm using to loop through
all
the rows (well, only through 12 right now while I test).

I didn't initially define a range. Wouldn't I need 2 ranges since there
are
2 difference references? An o range for column "O" and an n range for
column
"N"? I'm attempting to test your code but having trouble with syntax.
Thanks!!

Sub var_RE()
Dim oRange As Range
Set oRange = Worksheets("Sheet1").Range("O2:O2")
Dim myRange As Range
Set nRange = Worksheets("Sheet1").Range("N2:N2")
For rIndex = 2 To 12 Cells(rIndex,17).Formula = "=IF(" &
oRange("RE_Current").Value _
& "= 0,0," & nRange("Total_MTD").value & "/" & oRange("RE_Current").Value
&
")""
Next rIndex
End Sub

"Bob Phillips" wrote:

Is RE_Current a range name that is being evaluated in this code? How
does
it relate to O(rIndex), indeed what is O(rIndex)?

It might just be

Cells(rIndex, 17).Formula = "=IF(" &
Range("RE_Current").Value
& _
"= 0,0," &
Range("Total_MTD").value & _
"/" &
Range("RE_Current").Value & ")"

--
__________________________________
HTH

Bob

"pardoxchic" wrote in message
...
I need to populate this IF into all cells in my spreadsheet (many others
to
do as well). Somehow, my code is inserting the row index but it's the
variable name and not the value,,,resulting in this value
=IF(O(rIndex)=0,0,N(rIndex)/O(rIndex))

What am I missing? Is there an easier way to do this? Users may or may
not
have 2007 loaded - will that be a consideration? Newbie to Excel
coding!

Sub var_RE()
rIndex = 2 'set row to 2
For rIndex = 2 To 12
Cells(rIndex, 17).Formula = "=IF( [RE_Current] = 0, 0,
[Total_MTD] / [RE_Current])"
Next rIndex
End Sub