Solved

excel.intertop c# .formulaR1C1, remove absolute value

Posted on 2013-05-23
8
1,389 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

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
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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