use of integer in .FormulaR1C1

hello,
I used integer A and B in formula such as
..FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
and it works fine
How can I use the same principle for the following:
..FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
[" & A &"] does not seem to be recognised

PS: it is important that I use .FormulaR1C1 as my user needs to see the 
formula used.

Thanks a lot.
-- 
caroline
0
Utf
12/14/2009 6:23:01 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
1125 Views

Similar Articles

[PageSpeed] 54

you are combining a string and a number which is giving you the issue

try
 .FormulaR1C1 = "=INDEX(RangeX,[" & CSTR(A) &"],MATCH(YearEnd,Years,0))"
the CSTR(A) converts A to a string.
-- 
If this helps, please remember to click yes.


"caroline" wrote:

> hello,
> I used integer A and B in formula such as
> .FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
> and it works fine
> How can I use the same principle for the following:
> .FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
> [" & A &"] does not seem to be recognised
> 
> PS: it is important that I use .FormulaR1C1 as my user needs to see the 
> formula used.
> 
> Thanks a lot.
> -- 
> caroline
0
Utf
12/14/2009 6:35:01 PM
No it did not work. i must be doing something wrong
-- 
caroline


"Paul C" wrote:

> you are combining a string and a number which is giving you the issue
> 
> try
>  .FormulaR1C1 = "=INDEX(RangeX,[" & CSTR(A) &"],MATCH(YearEnd,Years,0))"
> the CSTR(A) converts A to a string.
> -- 
> If this helps, please remember to click yes.
> 
> 
> "caroline" wrote:
> 
> > hello,
> > I used integer A and B in formula such as
> > .FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
> > and it works fine
> > How can I use the same principle for the following:
> > .FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
> > [" & A &"] does not seem to be recognised
> > 
> > PS: it is important that I use .FormulaR1C1 as my user needs to see the 
> > formula used.
> > 
> > Thanks a lot.
> > -- 
> > caroline
0
Utf
12/14/2009 6:47:01 PM
Try dropping the []'s from your formular1c1 string.

If that doesn't help, create the formula manually in the worksheet cell.

Then turn on R1C1 reference style (if it's not turned on).

If you can't get your code to create this formula, then copy the formula and
paste into your followup message.

In this case (because you're using named ranges), there won't be a difference
between R1C1 reference formular1c1 and the A1 reference formula.  (Well, unless
I guessed wrong at what you were doing.)

caroline wrote:
> 
> hello,
> I used integer A and B in formula such as
> .FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
> and it works fine
> How can I use the same principle for the following:
> .FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
> [" & A &"] does not seem to be recognised
> 
> PS: it is important that I use .FormulaR1C1 as my user needs to see the
> formula used.
> 
> Thanks a lot.
> --
> caroline

-- 

Dave Peterson
0
Dave
12/14/2009 7:47:21 PM
Dropping the []'s from the formular1c1 string was the answer.
Thanks a million

-- 
caroline


"Dave Peterson" wrote:

> Try dropping the []'s from your formular1c1 string.
> 
> If that doesn't help, create the formula manually in the worksheet cell.
> 
> Then turn on R1C1 reference style (if it's not turned on).
> 
> If you can't get your code to create this formula, then copy the formula and
> paste into your followup message.
> 
> In this case (because you're using named ranges), there won't be a difference
> between R1C1 reference formular1c1 and the A1 reference formula.  (Well, unless
> I guessed wrong at what you were doing.)
> 
> caroline wrote:
> > 
> > hello,
> > I used integer A and B in formula such as
> > .FormulaR1C1 = "=R[" & -A & "]C*R[" & -B & "]C"
> > and it works fine
> > How can I use the same principle for the following:
> > .FormulaR1C1 = "=INDEX(RangeX,[" & A &"],MATCH(YearEnd,Years,0))"
> > [" & A &"] does not seem to be recognised
> > 
> > PS: it is important that I use .FormulaR1C1 as my user needs to see the
> > formula used.
> > 
> > Thanks a lot.
> > --
> > caroline
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
12/15/2009 8:40:01 AM
Reply:

Similar Artilces:

Opening File using default viewer
Hello, how would I go about opening a file using the default viewer, so for example, if the file is a txt file, then notepad would open it. Or if it is an XML file, then maybe firefox woulld open it. I was hoping to use a CButton and if the user clicks on it, then it would grab the path specified and open the file with the corresponding viewer. thanks. Jon wrote: > Hello, how would I go about opening a file using the default viewer, so > for example, if the file is a txt file, then notepad would open it. Or > if it is an XML file, then maybe firefox woulld open it. > > ...

Use of Pop3 accounts for external email
We've been using a pop3/smtp account to recieve our external email. I've heard that using those is a bad idea for a buisness. I don't understand it that much and wondering what are the issues with pop3 and what is an alternative to recieve external mail if not using a pop3 account. We have Exchange 2003. The issue is the company can't archive all user's mail. You know, company's email is representing the company, so the company should keep it as archives. With POP3 email will be moved from server to the client. Instead, you can use IMAP, Exchange Server, or ...

Trouble with hyhens within text when using LOOKUP
I have two columns, each containing a list of part numbers. Some of the part numbers contain hyphens. I am using LOOKUP and/or VLOOKUP to determine if the value in one column exists in the other. This works great on non-hyphenated part numbers. However, it will not find or return the hyphenated part numbers from the specified arrays. As a test, I did a quick if statement to compare the instances of identical hyphenated values that exist in both columns. Those statements did not have a problem with the hyphens. Can anyone offer any help? If hyphens cannot be used in conjunction with the ...

Using formulas to filter
Is there a way to filter a list by formulas the same way the Data->Filter option works? I'd like to take the matrix: Col A Col B Col C Col D Col E ===== ===== ===== ===== ===== Smith 30 $104.2 Yes 52 Jones 31 $155.3 No 51 Jones 31 $422.2 Yes 49 Freer 31 $424.3 Yes 42 Waylan 30 $322.5 No 50 Smith 31 $288.3 Yes 49 etc. And, using a formula, filter on Col B = 31 to produce: Col AA Col AB ...

RE: Use that security update
--dedtgpho Content-Type: multipart/related; boundary="yzkfzsfqtv"; type="multipart/alternative" --yzkfzsfqtv Content-Type: multipart/alternative; boundary="qeviqcxczwaquy" --qeviqcxczwaquy Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to hel...

Name Range with using Data Form
I first created a variable with A1:D1, four columns with named variable Data. How to expand this Data range to A1:D2 after using the DataForm to add one row data in the worksheet?? The same, the Data range should be A1:D3 when adding one more data row... million thanks Hi use the following formula in the name definition dialog =OFFSET($A$1:$D$1,0,0,COUNTA($A:$A)) >-----Original Message----- >I first created a variable with A1:D1, four columns with named variable >Data. How to expand this Data range to A1:D2 after using the DataForm to add >one row data in the worksheet?? The...

Has anyone used the Remove MO Utility?
I have a closed Quick MO that did not process properly somehow. The MO is flagged as closed, but sime items were not issued and are stuck in WIP. I ran the Tools > Utilities > Manufacturing > Remove MO process in a test database. It removed the MO and the items re no longer hung up in WIP. Has anyone else ever executed this process? Are there any gotcha's I need to be aware of? Many Thanks. Jim Hi Jim, I too have used this in a development/test environment and have not encountered any problems. -Mike -- Micheal LoPatriello Coordinator, Luna Development mlopat@lu...

How to use CxImage?
Hi I want to display a jpeg picture by using CxImage but I am new to visual c++ so I couldn't understand how to use this class in my project.Is there someone who have used this class and can you help me about using it? Thanks On Jul 3, 7:12 am, ibrahim <ibra...@discussions.microsoft.com> wrote: > Hi > I want to display a jpeg picture by using CxImage but I am new to visual c++ > so I couldn't understand how to use this class in my project.Is there someone > who have used this class and can you help me about using it? > Thanks Its all here: http://www.codeproj...

buy from this E-hermesol company is fast only used my 5days
I would like to introduce you a very good company which I knew. Their company homepage is www.ehermesol.com. They can offer you all kinds of electronical products which you need, such as motorcycles, laptops, mobile phones, digial cameras, TV LCD ,x box, ps3, gps, MP3/4, etc. Please take some time to have a look at it, there must be something you 'd like to purchase. Their contact email: ehermesol@188.com MSN: ehermesol@hotmail.com Hope you have a good mood in shopping from their company! Regards ...

How to use MSExcel to plot Column A against Col B?
I want to plot col A (x axis) against col B ( y axis). I cant seem to do it. Can anyone here please give me step by step primer. Thanks in advance. All I get is the graph of (1,2,............n) (x axis) against the n values of either col A or B. i.e 2 graphs instead of one. (In the old Lotus this was so simple: select the column for the X axis and then select the col for the Y and press enter, and you'd get the chart) Why is it so diff in Excel? Select the entire range you want to graph such as a2:b44>insert>chart>>>> -- Don Guillett SalesAid Software dguillett1@au...

Using Outlook to access and compile information in calendar
I want to create an application that will compile schedule information from Excel worksheets or an Access database and display them in a calendar type format. I know this type of application can be built using PivotTables in Excel, or forms in Access, but I was wondering if I could use the calendar in Outlook to display the information rather than creating PivotTables or custom forms which would wind up looking something like a calendar anyway. Before I start the job I'd like to know if I can work Outlook into the equation or if I really have to build it from scratch. Thanks, JEM ...

Using cell text in a formula
I am trying to use derived cell references in a VLOOKUP formula to matc data in several tables. For example, A1 contains the cell reference fo the top left of my array (A3) whilst cell A2 contains the cel reference for the bottom right of my array (D14). The array I' checking against starts in column E3. However, when I use the formula =VLOOKUP(E3,A1:A2,4,FALSE) I get a #N/ error. I need to use the cell references in each VLOOKUP as the arra sizes may vary in each case. (PS, I've used =INDIRECT(ADDRESS(A1,A2) to derive the cell references. Ji -- Message posted from http://www.Excel...

Using two conditions in a formula
How do I write the following formula: I am in cell I6 If g6 AND h6 is blank, then blank, else I5 minus g6 plus h -- Richard Pit ----------------------------------------------------------------------- Richard Pitt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1533 View this thread: http://www.excelforum.com/showthread.php?threadid=27042 Richard, In cell I6, enter the formula: =IF(AND(G6="",H6=""),"",I5-G6-H6) Though you don't check for case when G6 is blank and H6 isn't, etc. HTH, Bernie MS Excel MVP "Richard ...

Using CMFCToolBar and CMFCMenuBar?
After initial frustrations indicated by recent messages, I am now getting on with these classes relatively cordially. The user of my program can add or remove buttons to/from the CMFCToolBar(s), and menu items to/from the CMFCMenuBar - all beautifully customisable. The program knows about the available commands and they can be added to any toolbar or menu. And new custom tool bars can be added by the user. It's all fiendishly clever. However: my questions (today) are as follows: 1. The MFC Customise dialogue, from which one can drag commands to toolbars and menus,...

How did you add text into publisher, without using boxes?
how do you add text to publisher without using text boxes I suppose you could create your text as an image and insert the image into your publisher file. -- Don Vancouver, USA "Calvin Scott" <Calvin Scott@discussions.microsoft.com> wrote in message news:64D23D52-138D-47B4-B265-4A41BF14BF55@microsoft.com... > how do you add text to publisher without using text boxes Calvin Scott <Calvin Scott@discussions.microsoft.com> was very recently heard to utter: > how do you add text to publisher without using text boxes You don't. Text in Publisher has to e...

I want to design both side of a post card using publisher 03
I have go side one completed. How do I do the other side??? Insert Page -- Don Vancouver, USA "bettboop" <bettboop@discussions.microsoft.com> wrote in message news:68D78C55-BB03-44EC-97AF-D8FBA74FB95C@microsoft.com... >I have go side one completed. How do I do the other side??? ...

Using Outlook over a Network
Hi, I would like to know what software I need to allow multiple users to access one MS Outlook Calendar. The computers currently have Outlook 2000 and I would like the users to be able to edit one central calendar simultaneously, directly in the calendar (ie. not requiring users to send emails etc.) What sofware beyond Outlook 2000 do I need and is there any info on how this is done? Thanks Adrian Check out http://www.slipstick.com/outlook/share.htm -- PATRICK REED [Outlook - MVP]~~~~~~ -Microsoft Certified Professional (MCP) -Have you checked http://www.slipstick.com? -Plea...

how to use 2007 styles in Word 2003
hi everyone, we have Word 2003 but we really like the Styles that come with Word 2007. Is it possible to export these Styles and use it in Word 2003? And if yes, how can i do that. In Word 2007, click the Home tab of the ribbon. In the Styles group, click Change Styles | Style Set | Word 2003. To make this choice the default, click Change Styles again, and then click Set as Default. If/when you are prompted to save the changes to your template, choose to do so. -- Stefan Blom Microsoft Word MVP "skoub" <skoub@discussions.microsoft.com> wrote in messa...

How is the ease of use for salespeople compared to other tools?
With the goal of keeping it simple, it seems like there are a lot of steps, drop downs, etc. that slow up the inputting of information. Is there customization that is needed to speed this up would you think? is this a crm 1.2 or 3.0 question. Microsoft made a big effort to reduce the time to navigate around and input data in crm 3.0. you always have the option of building custom forms to allow you to enter data quickly then have these populate crm ========================= John O'Donnell Microsoft CRM MVP http://www.microsoft.com/businesssolutions/crm/community/microsoft_crm_co...

how can i use countif function inside a filtered range
How can I use countif function inside a filtered range Hi, Not much detail here, so this is the general idea: =SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18="Red")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jayin" wrote: > How can I use countif function inside a filtered range Shane Devenshire wrote on 02/24/2009 02:05 ET : > Hi, > > Not much detail here, so this is the general idea: > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18=&quot;Red&quot;)) > > ...

How do I use the "Correlation" dialog box with multiple ranges?
I'm using data in an Excel 2003 in a workbook, trying to see if there is any correlation between specific offices in which people work and the length of time they have been employed, their age the amount of sickleave taken and/or the amount of sickleave balance they have. I'm attempting to use Data Analysis and the Correlation dialog box. Although the dialog description tells me that I can use multiple comparison ranges, I don't see how to do it. Thanks for any help. Paulf6 - Arrange your data in list (database) format, i.e., names in the top row (Time Employed, Age, ...)...

Using Outlook from a different computer?
Hi, I have just started using Outlook on my home computer. Can I access my emails from a different computer??? if so, how do I do it. Thanks Amanda It depends on what different computer you mean. If you're trying to access your work email from home, contact your systems administrator at work to find out what their protocols are and if you're even allowed to do it. If you're asking how to access your Outlook email from a different computer than what you have Outlook installed on, it depends on what your intentions are. It might be easier to just check your ISP...

how to capture the button caption using an object
How to capture the button caption using an object in Dialog based MFC ..Suggest me which method to use. iam in urgent need of it. please give reply as soon as possible Thank you. What do you mean "capture the button caption"? Do you mean "Read the caption on a button"? GetWindowText works fine for this. Create a control variable and use it as CString s; c_MyButton.GetWindowText(s); and you've got it. joe On 7 Aug 2006 22:08:50 -0700, "lakshmi" <lakshmi.bollam@gmail.com> wrote: >How to captu...

anyone use pc charge with telecheck to process checks?
I got it all set up but all checks are denied. I call telecheck and they say they don't support software check processing and I should talk to PC Charge , I talk to PC-Charge and they say talk to Telecheck since all the settings seem correct. I have my account #, the phone numbers correct from telecheck and MICR as service. It dials out and processes but everything is denied with a retry message. thanks ...

using domain account for Exch2003 services
In Chapter 3 of the MS Press Training Kit "Implementing and Managing Microsoft Exchange Server 2003", there are instructions for changing the Exchange Services to run under a domain account (with appropriate group memberships and rights) rather than the default Local System account for security & auditing purposes. However, I could not start the services after making the change and reviewed several articles stating that only the Local System account will work - KB Articles 271920, 239762, etc. Is the training kit wrong, or is there a way to do this? thanks in advance, Art ...