Solved

excel.intertop c# .formulaR1C1, remove absolute value

Posted on 2013-05-23
8
1,406 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
Independent Software Vendors: 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 500 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel If Statement 3 29
Return array 3 20
Excel printing an invoice header over two sheets 3 30
Help to find the duplicates 8 19
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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.

726 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