Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative Formula References NOT changing After Insert From VB
I'm creating an Excel Workbook from VB and inserting some formulas (from VB
into Excel). The formulas are column specific and are relative reference formulas. For example oWB.ActiveSheet.Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'Gross Profit .. After insertion Excel shows a value for the formula, but the value is for the first row inserted. All subsequent rows contain the same formula and EXCEL does NOT adjust the relative Cell Formula References so the correct value is calculated for that row. QUESTIONS 1) What am I doing wrong? =================== What's the best way to handle Formulas when creating a new Workbook from VB: 1) Same as above using Formula Property 2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel Workbook to another 3) Create a separate sheet, Insert formulas into that sheet and then copy to sheet of interest. 4) Other Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative Formula References NOT changing After Insert From VB
Hi David,
That's because you're telling Excel to use the same formula on every row. Perhaps the easiest way to use relative referencing in VBA is to use the R1C1 notation (ie '.FormulaR1C1 ='). And to see how the formula would be coded for that, paste it manually into a workbook in the appropriate cell, then use Tools|Options|General. If, for example, the first entry would be in H7 (R7C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(RC[-6]="B",((RC[-2]-RC[-4])*RC[-5]*50),((RC[-2]-RC[-4])*RC[-5]*50))" However, if the first entry would be in H10 (R10C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(R[-3]C[-6]="B",((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50),((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50))" I note that, as posted, your true & false results appear to use the same formula, and that your test for a 'B' has too many double quotes around it. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... I'm creating an Excel Workbook from VB and inserting some formulas (from VB into Excel). The formulas are column specific and are relative reference formulas. For example oWB.ActiveSheet.Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'Gross Profit . After insertion Excel shows a value for the formula, but the value is for the first row inserted. All subsequent rows contain the same formula and EXCEL does NOT adjust the relative Cell Formula References so the correct value is calculated for that row. QUESTIONS 1) What am I doing wrong? =================== What's the best way to handle Formulas when creating a new Workbook from VB: 1) Same as above using Formula Property 2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel Workbook to another 3) Create a separate sheet, Insert formulas into that sheet and then copy to sheet of interest. 4) Other Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative Formula References NOT changing After Insert From VB
Thanks for your response:
I'm creating an Excel Application from VB6. As I recall R1C1 is Lotus notation which is OK, but haven't used for years. Whether Excel or Lotus notation if I create an Excel workbook by hand (no VB or VBA) and enter a formula, then drag it down a column, Excel enters the correct relative references in the formula. Using .Formula or .FormulaR1C1, VB(5/6) does put the same formula into each cell. My ASSUMPTION that once entered Excel would correct automatically (like the manual drag) or there is some property / method in the Excel object that I could set / call which would generate the wanted result. NOT exactly sure of your post. 1) Are you're saying I need to enter (via code) a different formula for each cell in the column. I HOPE this is not correct, as why use Excel via code OR 2) That using a R1C1 reference will create the offsets needed for each row even if I enter that same formula (via VB) into each cell? ======================== your true & false results appear to use the same formula -- I'm aware of this Your test for a 'B' has too many double -- When entered from VB(5/6) the double quotes are needed around a string literal otherwise the formula line errors. "macropod" wrote in message ... Hi David, That's because you're telling Excel to use the same formula on every row. Perhaps the easiest way to use relative referencing in VBA is to use the R1C1 notation (ie '.FormulaR1C1 ='). And to see how the formula would be coded for that, paste it manually into a workbook in the appropriate cell, then use Tools|Options|General. If, for example, the first entry would be in H7 (R7C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(RC[-6]="B",((RC[-2]-RC[-4])*RC[-5]*50),((RC[-2]-RC[-4])*RC[-5]*50))" However, if the first entry would be in H10 (R10C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(R[-3]C[-6]="B",((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50),((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50))" I note that, as posted, your true & false results appear to use the same formula, and that your test for a 'B' has too many double quotes around it. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... I'm creating an Excel Workbook from VB and inserting some formulas (from VB into Excel). The formulas are column specific and are relative reference formulas. For example oWB.ActiveSheet.Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'Gross Profit . After insertion Excel shows a value for the formula, but the value is for the first row inserted. All subsequent rows contain the same formula and EXCEL does NOT adjust the relative Cell Formula References so the correct value is calculated for that row. QUESTIONS 1) What am I doing wrong? =================== What's the best way to handle Formulas when creating a new Workbook from VB: 1) Same as above using Formula Property 2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel Workbook to another 3) Create a separate sheet, Insert formulas into that sheet and then copy to sheet of interest. 4) Other Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative Formula References NOT changing After Insert From VB
Hi David,
AFAIK, R1C1 notation originated with Microsoft Multiplan. Lotus introduced A1 notation. The manual method of inserting a formula into one cell, then dragging it down a column, is equivalent to copying the cell's contents and pasting them into the subsequent cells. That wasn't what your code was doing, though - it was putting a specific formula into each cell. As for your question, the R1C1 reference will create the offsets needed for each row even if you enter that same formula (via VB) into each cell. R1C1 notation can also be used to create absolute and mixed relative/absolute addressing (eg =R6C[-1]., when entered into either C4 or C10 would equate to =B$6, because a specific row is designated). Another way of implementing the code, without resorting to R1C1 notation would be to use something like: With oWB.ActiveSheet .Range("H" & Row).Value = "=IF(B" & Row & "=""B""," & _ "((F" & Row & "-D" & Row & ")*C" & Row & "*50)," & _ "((F" & Row & "-D" & Row & ")*C" & Row & "*50))" 'Gross Profit End With though, as I noted in my previous post, you'll still need to address the fact that your code uses the same calculation for both the true and false results of the IF test. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... Thanks for your response: I'm creating an Excel Application from VB6. As I recall R1C1 is Lotus notation which is OK, but haven't used for years. Whether Excel or Lotus notation if I create an Excel workbook by hand (no VB or VBA) and enter a formula, then drag it down a column, Excel enters the correct relative references in the formula. Using .Formula or .FormulaR1C1, VB(5/6) does put the same formula into each cell. My ASSUMPTION that once entered Excel would correct automatically (like the manual drag) or there is some property / method in the Excel object that I could set / call which would generate the wanted result. NOT exactly sure of your post. 1) Are you're saying I need to enter (via code) a different formula for each cell in the column. I HOPE this is not correct, as why use Excel via code OR 2) That using a R1C1 reference will create the offsets needed for each row even if I enter that same formula (via VB) into each cell? ======================== your true & false results appear to use the same formula -- I'm aware of this Your test for a 'B' has too many double -- When entered from VB(5/6) the double quotes are needed around a string literal otherwise the formula line errors. "macropod" wrote in message ... Hi David, That's because you're telling Excel to use the same formula on every row. Perhaps the easiest way to use relative referencing in VBA is to use the R1C1 notation (ie '.FormulaR1C1 ='). And to see how the formula would be coded for that, paste it manually into a workbook in the appropriate cell, then use Tools|Options|General. If, for example, the first entry would be in H7 (R7C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(RC[-6]="B",((RC[-2]-RC[-4])*RC[-5]*50),((RC[-2]-RC[-4])*RC[-5]*50))" However, if the first entry would be in H10 (R10C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(R[-3]C[-6]="B",((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50),((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50))" I note that, as posted, your true & false results appear to use the same formula, and that your test for a 'B' has too many double quotes around it. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... I'm creating an Excel Workbook from VB and inserting some formulas (from VB into Excel). The formulas are column specific and are relative reference formulas. For example oWB.ActiveSheet.Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'Gross Profit . After insertion Excel shows a value for the formula, but the value is for the first row inserted. All subsequent rows contain the same formula and EXCEL does NOT adjust the relative Cell Formula References so the correct value is calculated for that row. QUESTIONS 1) What am I doing wrong? =================== What's the best way to handle Formulas when creating a new Workbook from VB: 1) Same as above using Formula Property 2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel Workbook to another 3) Create a separate sheet, Insert formulas into that sheet and then copy to sheet of interest. 4) Other Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative Formula References NOT changing After Insert From VB
Thanks for response.
The solution I came up with is to build a dynamic formula (a string that reflects the correct row/column within the formula with each loop pass). This dynamic string is then assigned to the Excel Formula Property and inserted into Excel from VB at the correct Cell Formula location. This is the similar to your second solution except I used formula. strToken1 = "=IF(B" & CStr(row) & "=""B""," strToken2 = "((F" & CStr(row) & "-D" & CStr(row) & ")" strToken3 = " * C" & CStr(row) & " * 50)" strText = strToken1 strText = strText & strToken2 & strToken3 & ", " strText = strText & strToken2 & strToken3 & ")" .Cells(row, "H").Formula = strText Will again look at your first to see if using offsets are faster or easier to implement. =================== MultiPlan -- forgot about that. Have a nice day, and again thanks for your efforts on my behalf. David "macropod" wrote in message ... Hi David, AFAIK, R1C1 notation originated with Microsoft Multiplan. Lotus introduced A1 notation. The manual method of inserting a formula into one cell, then dragging it down a column, is equivalent to copying the cell's contents and pasting them into the subsequent cells. That wasn't what your code was doing, though - it was putting a specific formula into each cell. As for your question, the R1C1 reference will create the offsets needed for each row even if you enter that same formula (via VB) into each cell. R1C1 notation can also be used to create absolute and mixed relative/absolute addressing (eg =R6C[-1]., when entered into either C4 or C10 would equate to =B$6, because a specific row is designated). Another way of implementing the code, without resorting to R1C1 notation would be to use something like: With oWB.ActiveSheet .Range("H" & Row).Value = "=IF(B" & Row & "=""B""," & _ "((F" & Row & "-D" & Row & ")*C" & Row & "*50)," & _ "((F" & Row & "-D" & Row & ")*C" & Row & "*50))" 'Gross Profit End With though, as I noted in my previous post, you'll still need to address the fact that your code uses the same calculation for both the true and false results of the IF test. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... Thanks for your response: I'm creating an Excel Application from VB6. As I recall R1C1 is Lotus notation which is OK, but haven't used for years. Whether Excel or Lotus notation if I create an Excel workbook by hand (no VB or VBA) and enter a formula, then drag it down a column, Excel enters the correct relative references in the formula. Using .Formula or .FormulaR1C1, VB(5/6) does put the same formula into each cell. My ASSUMPTION that once entered Excel would correct automatically (like the manual drag) or there is some property / method in the Excel object that I could set / call which would generate the wanted result. NOT exactly sure of your post. 1) Are you're saying I need to enter (via code) a different formula for each cell in the column. I HOPE this is not correct, as why use Excel via code OR 2) That using a R1C1 reference will create the offsets needed for each row even if I enter that same formula (via VB) into each cell? ======================== your true & false results appear to use the same formula -- I'm aware of this Your test for a 'B' has too many double -- When entered from VB(5/6) the double quotes are needed around a string literal otherwise the formula line errors. "macropod" wrote in message ... Hi David, That's because you're telling Excel to use the same formula on every row. Perhaps the easiest way to use relative referencing in VBA is to use the R1C1 notation (ie '.FormulaR1C1 ='). And to see how the formula would be coded for that, paste it manually into a workbook in the appropriate cell, then use Tools|Options|General. If, for example, the first entry would be in H7 (R7C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(RC[-6]="B",((RC[-2]-RC[-4])*RC[-5]*50),((RC[-2]-RC[-4])*RC[-5]*50))" However, if the first entry would be in H10 (R10C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(R[-3]C[-6]="B",((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50),((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50))" I note that, as posted, your true & false results appear to use the same formula, and that your test for a 'B' has too many double quotes around it. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... I'm creating an Excel Workbook from VB and inserting some formulas (from VB into Excel). The formulas are column specific and are relative reference formulas. For example oWB.ActiveSheet.Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'Gross Profit . After insertion Excel shows a value for the formula, but the value is for the first row inserted. All subsequent rows contain the same formula and EXCEL does NOT adjust the relative Cell Formula References so the correct value is calculated for that row. QUESTIONS 1) What am I doing wrong? =================== What's the best way to handle Formulas when creating a new Workbook from VB: 1) Same as above using Formula Property 2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel Workbook to another 3) Create a separate sheet, Insert formulas into that sheet and then copy to sheet of interest. 4) Other Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Relative Formula References NOT changing After Insert From VB
Hi David,
I think you'll find this implementation: ..Cells(row, "H").Formula = "=(F" & row & "-D" & row & ")*C" & row & "*50" somwhat faster, since the IF test is pointless and this construction avoids the CStr conversion and string building/concatenation steps. Your workbook should also end up smaller and faster at recalculating too. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... Thanks for response. The solution I came up with is to build a dynamic formula (a string that reflects the correct row/column within the formula with each loop pass). This dynamic string is then assigned to the Excel Formula Property and inserted into Excel from VB at the correct Cell Formula location. This is the similar to your second solution except I used formula. strToken1 = "=IF(B" & CStr(row) & "=""B""," strToken2 = "((F" & CStr(row) & "-D" & CStr(row) & ")" strToken3 = " * C" & CStr(row) & " * 50)" strText = strToken1 strText = strText & strToken2 & strToken3 & ", " strText = strText & strToken2 & strToken3 & ")" .Cells(row, "H").Formula = strText Will again look at your first to see if using offsets are faster or easier to implement. =================== MultiPlan -- forgot about that. Have a nice day, and again thanks for your efforts on my behalf. David "macropod" wrote in message ... Hi David, AFAIK, R1C1 notation originated with Microsoft Multiplan. Lotus introduced A1 notation. The manual method of inserting a formula into one cell, then dragging it down a column, is equivalent to copying the cell's contents and pasting them into the subsequent cells. That wasn't what your code was doing, though - it was putting a specific formula into each cell. As for your question, the R1C1 reference will create the offsets needed for each row even if you enter that same formula (via VB) into each cell. R1C1 notation can also be used to create absolute and mixed relative/absolute addressing (eg =R6C[-1]., when entered into either C4 or C10 would equate to =B$6, because a specific row is designated). Another way of implementing the code, without resorting to R1C1 notation would be to use something like: With oWB.ActiveSheet .Range("H" & Row).Value = "=IF(B" & Row & "=""B""," & _ "((F" & Row & "-D" & Row & ")*C" & Row & "*50)," & _ "((F" & Row & "-D" & Row & ")*C" & Row & "*50))" 'Gross Profit End With though, as I noted in my previous post, you'll still need to address the fact that your code uses the same calculation for both the true and false results of the IF test. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... Thanks for your response: I'm creating an Excel Application from VB6. As I recall R1C1 is Lotus notation which is OK, but haven't used for years. Whether Excel or Lotus notation if I create an Excel workbook by hand (no VB or VBA) and enter a formula, then drag it down a column, Excel enters the correct relative references in the formula. Using .Formula or .FormulaR1C1, VB(5/6) does put the same formula into each cell. My ASSUMPTION that once entered Excel would correct automatically (like the manual drag) or there is some property / method in the Excel object that I could set / call which would generate the wanted result. NOT exactly sure of your post. 1) Are you're saying I need to enter (via code) a different formula for each cell in the column. I HOPE this is not correct, as why use Excel via code OR 2) That using a R1C1 reference will create the offsets needed for each row even if I enter that same formula (via VB) into each cell? ======================== your true & false results appear to use the same formula -- I'm aware of this Your test for a 'B' has too many double -- When entered from VB(5/6) the double quotes are needed around a string literal otherwise the formula line errors. "macropod" wrote in message ... Hi David, That's because you're telling Excel to use the same formula on every row. Perhaps the easiest way to use relative referencing in VBA is to use the R1C1 notation (ie '.FormulaR1C1 ='). And to see how the formula would be coded for that, paste it manually into a workbook in the appropriate cell, then use Tools|Options|General. If, for example, the first entry would be in H7 (R7C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(RC[-6]="B",((RC[-2]-RC[-4])*RC[-5]*50),((RC[-2]-RC[-4])*RC[-5]*50))" However, if the first entry would be in H10 (R10C8), the code would look like: oWB.ActiveSheet.Cells(row, "H").FormulaR1C1 = "=IF(R[-3]C[-6]="B",((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50),((R[-3]C[-2]-R[-3]C[-4])*R[-3]C[-5]*50))" I note that, as posted, your true & false results appear to use the same formula, and that your test for a 'B' has too many double quotes around it. -- Cheers macropod [MVP - Microsoft Word] "David" wrote in message ... I'm creating an Excel Workbook from VB and inserting some formulas (from VB into Excel). The formulas are column specific and are relative reference formulas. For example oWB.ActiveSheet.Cells(row, "H").Formula = "=IF(B7=""B"", ((F7-D7) * C7 * 50), ((F7-D7) * C7 * 50))" 'Gross Profit . After insertion Excel shows a value for the formula, but the value is for the first row inserted. All subsequent rows contain the same formula and EXCEL does NOT adjust the relative Cell Formula References so the correct value is calculated for that row. QUESTIONS 1) What am I doing wrong? =================== What's the best way to handle Formulas when creating a new Workbook from VB: 1) Same as above using Formula Property 2) Putting Formula into an Existing Excel Workbook/Sheet and copying the formula from one Excel Workbook to another 3) Create a separate sheet, Insert formulas into that sheet and then copy to sheet of interest. 4) Other Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing relative references | Excel Discussion (Misc queries) | |||
How to copy an array without changing relative cell references? | Excel Worksheet Functions | |||
How to copy an array without changing relative cell references? | Excel Programming | |||
How to copy an array without changing relative cell references? | Excel Worksheet Functions | |||
How to copy an array without changing relative cell references? | Excel Programming |