Link to home
Create AccountLog in
Avatar of Stephen Forero
Stephen ForeroFlag for United States of America

asked on

excel.intertop c# .formulaR1C1, remove absolute value

hi all,

Using c# microsoft.excel.interop, I am inputting a formula into excel.
My only question is... how do I get rid of the $$ in the formula... the absolute values?

Thanks in advance

                                                excelRange.FormulaR1C1 = "=IF(" + valueOnePointCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, true, Type.Missing) +
                                                                         "=0.9999, " +
                                                                             "(1 / " + tickSizeCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlA1, true, Type.Missing) + ")" +
                                                                             "*" + tickValueCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                             "*" + quantityCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                             "*" + currencyMultiplierCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                             "*" + deltaCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                             "," +
                                                                                 "(" + deltaCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                                 "*" + quantityCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                                 "*" + currencyMultiplierCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                                 "*" + valueOnePointCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +
                                                                                 "))";

Open in new window

Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

The first two parameters to get_Address are rowAbsolute and columnAbsolute.  You have omitted them in each of your calls - set one or both to false to get relative references.
Avatar of Stephen Forero

ASKER

for example.. this one section

 tickValueCell.get_Address(Type.Missing, Type.Missing, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +

Open in new window


returns ie "$A$1"... input into the formula.  I just want it input "A1"
Yes, exactly - change it to:
tickValueCell.get_Address(false, false, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +

Open in new window

okay, that part seemed to work, but not fix the issue.

I changed the formula to ...

excelRange.Formula = "=IF(" + valueOnePointCell.get_Address(false, false, Excel.XlReferenceStyle.xlA1, true, Type.Missing) +

there is more but not important...

when I look in the cell it looks like
=IF(excelworkbooknamehere!T3=0.9999


All the formulas work... but at the end when I sort by a column... the references its points to do not move.  When I sort the goal is for the formulas to move with the correct row.

I hope I am explaining this clearly
ASKER CERTIFIED SOLUTION
Avatar of andrewssd3
andrewssd3
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
GENIUS!!! thanks it worked.  I actually read the properties before I made this post.

Problem is, even reading it now I still don't understand what it means.  What is the definition a local reference?  

External
 Optional
 Variant
 True to return an external reference. False to return a local reference. The default value is False.
If you use external, it generates a reference that can be used from outside the sheet, or outside the workbook if it is saved, so it will include the sheet name, and the workbook name if it has one yet.  Otherwise you just get the cell reference that can be used just on this sheet.  A couple of examples from the Excel Immediate Window:
print activecell.address(false,false,xlA1,false)
A16
print activecell.address(false,false,xlA1,true)
'[Volatility Monitor.xlsb]MAIN'!A16

Open in new window

i understand now... thanks for the clarification