Enter formula by code.

Hi All

can anyone help. I cant seem to get this to work.

I keep getting run time error 1004

application or object defined error.

Public Sub TLIdentify()

finalrow = Worksheets("INV Bookings").Range("B65536").End(xlUp).Row

Worksheets("INV Bookings").Range("AV26:AV" & finalrow).FormulaR1C1 = 
"=IF(OR(RC[-38]>0,RC[-32]>0,RC[-26]>0,RC[-20]>0,RC[-14]>0,RC[-8]>0,RC[-2]>0),VLOOKUP(RC4,'Team Summary'!R4C3:R16C4,2,0),"")"

End Sub
-- 
Regards and Thanks for any assistance.

Francis Brown.
0
Utf
2/21/2010 6:45:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
1170 Views

Similar Articles

[PageSpeed] 57

On Sun, 21 Feb 2010 10:45:01 -0800, Francis Brown
<FrancisBrown@discussions.microsoft.com> wrote:

>Hi All
>
>can anyone help. I cant seem to get this to work.
>
>I keep getting run time error 1004
>
>application or object defined error.
>
>Public Sub TLIdentify()
>
>finalrow = Worksheets("INV Bookings").Range("B65536").End(xlUp).Row
>
>Worksheets("INV Bookings").Range("AV26:AV" & finalrow).FormulaR1C1 = 
>"=IF(OR(RC[-38]>0,RC[-32]>0,RC[-26]>0,RC[-20]>0,RC[-14]>0,RC[-8]>0,RC[-2]>0),VLOOKUP(RC4,'Team Summary'!R4C3:R16C4,2,0),"")"
>
>End Sub

You have to double your "s in the end of the IF statement.
As the formula is inside a long string a single " will be interpreted
as the end of this string rather than a " that is to be part of the
formula.

Change you formula like this:

......R16C4,2,0),"""")"

and say good bye to the error 1004 :-)

Hope this helps / Lars-�ke
0
Lars
2/21/2010 7:21:13 PM
Reply:

Similar Artilces:

Why is Actual Work updated when entering time into Work Field?
We're using MS Project 2007 and seeing that sometimes when time is entered into the Work field for a task, that task's Actual Work is updated to the same value entered in the Work field. This happens to multiple resources, but only on some of their tasks and not on others. For example, for the weeks of 1/24 and 1/31, whatever hours I schedule (enter in the "work" cell in the Resource Usage View) for a given task also appears in the "actual" cell. The tasks are setup up differently. Some are Fixed Work, some are Fixed Duration. I believe all have the e...

Formula input error help
I enter complex mathmatical formulas in excel spreadsheets frequently. If something isn't quite right xl makes suggestions on how to correct the formula. I never choose yes and then another warning pops up that tells what it thinks is wrong. This is a waste of time and very irritating. Is there any way just to turn all that assistance stuff off and have it place the word error or something in the cell? I am perfectly capable of finding the problem without the program's help. Excel 2002, Windows 2000 Pro Click on Tools|Options|Error checking and untick <Enable background er...

Excel 2003 spreadsheet. I enter the any number and it is entered.
If I enter a number into a cell it is put in like a decimal. For example, if I enter 10 it is put in as .1 500 is entered as a 5 Thank you Mary Mary, Goto Tools>Options and on the Edit tab you should see The Fixed Decimal Places box checked. Uncheck it. -- HTH RP "maryu" <maryu@discussions.microsoft.com> wrote in message news:65C0E0FB-43F3-4F34-8A86-FBA3F02EC9B1@microsoft.com... > If I enter a number into a cell it is put in like a decimal. For example, if > I enter 10 it is put in as .1 500 is entered as a 5 > Thank you > Mary ...

Extracting unique records by formula
I want to extract the unique records from column A into column B by formula ie: NOT using the Advanced Filter. Any ideas? Thanks. -- Peter London, UK Stolen from J-Walk: Assuming data is in cells A2:A100. Enter this array formula in B2 - commit with ctrl+shift+enter. =INDEX($A$2:$A$100,SMALL(IF(MATCH($A$2:$A$100,$A$2:$A$100,0)=ROW($A$1:INDIRECT(CHAR(65)&COUNTA($A$2:$A$100))),MATCH($A$2:$A$100,$A$2:$A$100,0),""),ROW()-ROW($A$2:$A$100)+1)) Copy down. Note you will have to adjust the range to suit your data as any blank cells will result in a #NA error. Hope this helps Rowan ...

Filter records by date in subform by date entered in mainform.
Hello, I've spent 3 days trying to understand what I'm doing wrong.....Here is the summary of my problem. I have a form named 'frm_EfficiencyStats' and a subform within the form named 'qry_Total hours subform' (the subform is based on a query). The form contains a text box (for date) which is bound to a table 'tbl_EfficiencyStats'. I would like to be able to enter a date in the text box on my form, and display the records in the subform based on the date I entered in the text box. The subform will only pull records from my table based on the date. Here are th...

Excel 2003 - VBA
Hi Guys: I'm drawing a blank and need some help. I have a table with row 3 containing sheet names (currently pointing at 106 sheets). In rows 4-250 I want to put formulas that will look for something (located in Column B) in each of the sheets named in row 3. The first cell in the target sheet contains the last row with valid data. Example: Cell "D3" = P02-05 Cell "B5" = 123456789IBM In cell "D5" I want to generate a formula that will look in sheet P02-05, search for the contents of "B5" throughout the sheet and return the Value in colum...

Help with code
I have created and coded text-boxes ( using the design view and VB-code) in excel that enables me to jump from one text-box to anoter by using the Tab-button, but view of the worksheet does not follow the tab Then I got at tip to add code to Select or GoTo a nearby cell before the textbox I am a very new user so I am not so familiar with VB code yet Is it possible to get some help with the code please Thanks If you want to center a particular cell on your screen, and/or a TEXT box near that cell, then do F5 and type IV65000 in the reference box and then do F5 again and type a cell number...

Conditional Format Formula Help
Greetings, I have a column of names. Some of these names are in a separate list and some are not. I have a macro to change the color of the cell of the names that are not in the list to red. When I find these names that are not in the list and correct them so that they are now in the list, the cell color is changed back to white. The problem is in quickly identifying which workbooks still have uncorrected names in this column when I open it. For example: In the CF of D1, If any cell in column D is red, then change the color of D1 to brown, otherwise, change it to orange (defa...

how to enter simple list and sum
using excel 2002 with an xp sp2 system how would I enter a simple list of positive and negative numbers and then create a cell with the sum of these values? if the list would look like this 0.58 -1.22 1.79 -0.56 -0.92 the output would look something like 0.58 0.58 -1.22 -0.64 1.79 1.15 -0.56 0.59 -0.92 -0.33 John In B1 enter =A1 In B2 enter =B1+A2 Copy down to B5 Gord Dibben MS Excel MVP On Wed, 16 May 2007 06:34:13 +0300, "John" <johnj@multipl.es> wrote: >using excel 2002 with an xp sp2 system >how would I enter a simple list of positive and negative...

Formula Help! #3
Hi, I need to transform a date to week date , for example: 10/11/05 to Tuesday Is there any formula can do this transform? Thanks in advance -- Meme Hi! Try this: A1 = 10/11/2005 =TEXT(A1,"dddd") This returns a TEXT value = Tuesday OR, you could just format the cell, A1, as dddd. This retains it's numeric value but will display as Tuesday. Biff "Meme" <Meme@discussions.microsoft.com> wrote in message news:64B1FB51-4EC0-42DC-97B9-B788A9586811@microsoft.com... > Hi, > > I need to transform a date to week date , for example: > 10/11/05 ...

UPC Bar Codes
I am trying to print UPC codes in both my software and a client's RMS software and the UPC-A bar code is listed, but will not display when you select it nor will it print. Does anyone know what the problem might be? Thanks, Jim T. jim_tingley@yahoo.com Your look up code may not meet the UPC requirements - for instance does it include an Alpha character? -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can ...

Bracket {..} in formulas
I encountered a formula in a spreadsheet which starts and ends with {=...} these brackets. The formula refers sum (although in reality it is sumproduct) The brackets are visible in the formula bar, but not in the cell when I double click it. When I click in the formula bar the brackets disappear and the formula stops working. It is not possible to copy this back from the original workbook. When typing it back the formula become ordinary text. -- milet Hi, that is an array formula if you edit and press enter the { dissapear, when editing the formula what you have to do is to press CTRL+...

First attempt at VBA coding problem
I have a formula in a cell on a worksheet as follows: =TEXT(DAY(B5),"00")&"-"&TEXT(MONTH(B5),"00")&"-"&RIGHT(TEXT(YEAR(B5),"00"),2)&"," & CELL("contents",D5)&"" I am attempting to automate entering the formula in a cell with the following macro code. Obviously I have something wrong with the syntax. Can anyone identify the error in this code: ActiveCell.FormulaR1C1 = _ "=TEXT(DAY(RC[1]),""00"")&""-""&TEXT(MONTH(RC[1]),&...

Resource Units for Tasks entered in PWA differs than tasks entered in Project 2007
Hello, I have an ePM user asking why if he enters a project task in PWA does it not check the resource max units allocation the same way as when he enters a task in Project Professional. For example: You have a project where resource A is 50% allocated. When you create a task in Project Professional for this resource, the resource allocation is set to 50%. Create a task under the same project in PWA, submit and approve the task. Open project professional and view the task information for the task entered in PWA, why is the resource allocation set to 100% instead of 50% set for th...

Decimals in Formula
how do I turn This: ="This totals need to consider "&(ROUND(SUM(G28:G32),2)) Into This: This totals need to consider 171,150.00 And Not This totals need to consider 171150.00 ="This totals need to consider "&TEXT((ROUND(SUM (G28:G32),2)),"#,###.00") HTH Jason Atlanta, GA >-----Original Message----- >how do I turn This: >="This totals need to consider "&(ROUND(SUM(G28:G32),2)) > >Into This: >This totals need to consider 171,150.00 > >And Not >This totals need to consider 171150.00 > >. > Trella, He...

Enter formula by code.
Hi All can anyone help. I cant seem to get this to work. I keep getting run time error 1004 application or object defined error. Public Sub TLIdentify() finalrow = Worksheets("INV Bookings").Range("B65536").End(xlUp).Row Worksheets("INV Bookings").Range("AV26:AV" & finalrow).FormulaR1C1 = "=IF(OR(RC[-38]>0,RC[-32]>0,RC[-26]>0,RC[-20]>0,RC[-14]>0,RC[-8]>0,RC[-2]>0),VLOOKUP(RC4,'Team Summary'!R4C3:R16C4,2,0),"")" End Sub -- Regards and Thanks for any assistance. Francis Brown...

Formula Bar
How do I replace the function button "fx" with the equal sign "=" on my formula bar? Please help! Yours truly, New Excel 2002 User Sean The closest you can get is to go to Tools>Customize>Commands>Insert and drag the "=" sign to your Toolbar. Note: it does not function as it did prior to Excel 2002 and 2003. Gord Dibben Excel MVP On Tue, 10 Aug 2004 11:24:06 -0700, "Sean Lamb" <seanlamb@gmail.com> wrote: >How do I replace the function button "fx" with the equal >sign "=" on my formula bar? > >Plea...

Docking Formula Builder
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) I accidentally hid the formula bar that appears at the top of every training video. I can customize the toolbar to show a formula button, but every time that I try to dock the formula bar to the excel window (as shown in every video), it will not stay. How do I re-dock the formula builder to the excel window? Do I have to re-install the product. Please say no. <br> Thanks I see no call for reinstalling Office -- there rarely is -- but I'm not sure I'm clear on your issue. The Formula *Bar* & the Formula *Builder*...

Non-positional cell formulas?
Ok, I have a spreadsheet with multiple columns, for example: Date | spanning header1 | spanner header2 | spanning header3 | Average header | | subhead1a | subhead1b | subhead2a | subhead2b | subhead3a | subhead3b | | date | s1adata | s1bdata | s2adata | s2bdata | s3adata | s3bdata | average s1a,s2a,s3a | Now for every row, I need to calculate the subtotal data. No problem with the formula (like in =AVERAGE(B3,D3,F3)). The next line's formula would be =AVERAGE(B4,D4,F4). The problem ...

Entering a dollar amount w/out having to enter the decimal
What is the formula to have Excel automatically enter the decimal so all I have to enter is the numbers? It's one of the options you can specify. In Excel 2007, it's: Excel Options...>Advanced>Automatically insert a decimal point Regards, Fred "Julieanne" <Julieanne@discussions.microsoft.com> wrote in message news:3AABE398-3B77-4ACD-8938-05ED7F5CC58E@microsoft.com... > What is the formula to have Excel automatically enter the decimal so all I > have to enter is the numbers? ...

Formula showing in cell as text
This is a new one on me. After entering a formula, instead of seeing blank cell or the result of the formula in the cell, I'm seeing th text of the formula as I normally would see it displayed in the formul bar. I can't seem to get it to calculate or stop showing the formula a text in the cell. One additional odd thing, going to TOOLS>FORMULA AUDITING>EVALUAT FORMULA, everything looks normal, with two exceptions; one a statemen "The cell currently being evaluated contains a constant", is displaye in the dialog box and all but the CLOSE button are grayed out. What I ha...

formula through inputbox
Instead of entering a formula like =IF(ISERROR(C1/D1),"",C1/D1), I would like the user the enter the formula like c1/d1 through an inputbox. The code below tries to do this but is not working - the results looks like =IF(ISERROR(y),"",y) Sub Macro1() y = InputBox("enter formula") ActiveCell.Formula = "=IF(ISERROR(y),"""",y)" End Sub Two ways, uncomment the second commented line to try the second way Sub test() Dim sFmla As String Dim y As Variant y = InputBox("enter formula") If Var...

How do I stop dates from being entered everytime I enter a number
Everytime I enter data in a box (ex: ages 5-9) I then press enter to go to the next cell and it automatically puts in may 9 . I have tried putting in general in formatting cells and it still does it. can someone help before I shoot this pc out the window? I am making a population pyramid with males and females and their ages. Thanks, Kicking and Screaming You can precede your entry with a single quote... '5-9 ....or format your cells as 'Text'. Hope this helps! In article <64AE5219-035D-46E3-9488-5D01F6B012FA@microsoft.com>, Kicking and Screaming <Kicking and...

entering time #2
hello all.. wondering if there is any way to do this... I have a column formatted for time as in this format 12:00 when entering a time it is cumbersome to have to enter the hour, then a colon, then the minutes. Is it possilbe to simply enter a 4 digit number such as 1245 and have it show up as 12:45 ?? thanks so much... I've found this group to be irreplaceable... glen in orlando Enter your times without colons in column A and put this formula in column B =(A1-RIGHT(A1,2))/2400+RIGHT(A1,2)/(24*60) and format column B for "Time" in the form you want it. Tod "Glen i...

Formula for multiplying and adding percentage please help.
A pretty simple question I guess. I am trying to make a template to do material take offs for a construction company. I am trying to add 10-15% to various cells. An example of what I am after. A | B | C 1 100 =A1*(2+10%) 2 3 4 What I am getting is 200.1 when what I am expecting is 220 ... 100 * 2 = 200 +10% = 220. What in my formula do I have wrong ? =E1*(100%+10%) or e1*1.10 -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Johnnychadrice" <Johnnychadrice@discussions.microsoft.com> wrote in message news:3...