?
Solved

excel.intertop c# .formulaR1C1, remove absolute value

Posted on 2013-05-23
8
Medium Priority
?
1,429 Views
Last Modified: 2013-05-24
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

0
Comment
Question by:solarissf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39192498
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.
0
 

Author Comment

by:solarissf
ID: 39192526
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"
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39192537
Yes, exactly - change it to:
tickValueCell.get_Address(false, false, Excel.XlReferenceStyle.xlR1C1, true, Type.Missing) +

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:solarissf
ID: 39192607
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
0
 
LVL 17

Accepted Solution

by:
andrewssd3 earned 2000 total points
ID: 39193321
That's because you have set the 4th parameter, 'External' to true.  This generates an external ref.  If you change this to false it will do what you want.

It's always a good idea to be familiar with the Excel methods and properties before using interop - the Address documentation is here: http://msdn.microsoft.com/en-us/library/office/ff837625(v=office.15).aspx amongst many other places on the web. I often find it helps to experiment with the code in a test routine or the immediate window in Excel VBA - it's usually quicker than trying to do it though Visual Studio and the help is generally better.
0
 

Author Comment

by:solarissf
ID: 39194070
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.
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 39194221
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

0
 

Author Comment

by:solarissf
ID: 39194434
i understand now... thanks for the clarification
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question