Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index formula trouble | Excel Discussion (Misc queries) | |||
Trouble populating ComboBox from a range | Excel Programming | |||
looping trouble | Excel Discussion (Misc queries) | |||
Trouble with looping | Excel Programming | |||
Looping trouble | Excel Programming |