Solved

excel.intertop c# .formulaR1C1, remove absolute value

Posted on 2013-05-23
8
1,379 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
  • 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now