Problems inserting a formula ina acell using VBA

Hi All,

I write this forum, trying to mfind a solution for a little problem I
have wiht my Excel spreadsheet.

I'm tryiing to insert a (not very) complex formula ina cell using the
".Formula" property of the cell, but it fails with a 1004 run-time
error.

When inserting a simple formula like the one in the example included
with the excel-help it works

Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"

but when trying ro  to insert my own formula, it fails...

Worksheets("Sheet1").Range("A1").Formula =
"=IF(AH2=1;(AH1&CHAR(10));AH3) & IF(AG2=1;(AG1&CHAR(10));AG3)"

or even

Worksheets("Sheet1").Range("A1").Formula =
"=IF(AH2=1;(AH1&CHAR(10));AH3)"

The fact is that the formula is well-constructed, since if I insert
just the text (without the "=") and then insert this character manually
editing the cell in the worksheet, the formula works completely OK.

Any idea on the reasons for that problem? Is there any limitation in
the length, complexity or the Excel functions that can be used when
creating formulas that way?

Many thanks

Xabier
0
9/9/2003 12:19:37 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
520 Views

Similar Articles

[PageSpeed] 1

Hi Xabier,

> Worksheets("Sheet1").Range("A1").Formula =
> "=IF(AH2=1;(AH1&CHAR(10));AH3)"

Excel VBA uses US-English formats for the formulas, so you'll need to 
use commas instead of semi-colons:

 Worksheets("Sheet1").Range("A1").Formula =
  "=IF(AH2=1,(AH1&CHAR(10)),AH3)"

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk

0
9/9/2003 1:27:59 PM
Reply:

Similar Artilces:

Creating formula for timeline
I am trying to find a formula in Excel that will help me manage the following report: I have 2 columns...the first gives a list of months...ex. Jan, Feb, March, etc. I want a formula that will automatically populate the second column with a month that is 3 months past the first column...ex. 1st column is Jan 2nd is April. 1st column is Feb 2nd is May... Is this possible. I don't want specific dates in the month...just the month itself. Lynne This should do the trick for you. 'Borrowed' from a post a month or so back Remember they are text so no date calculations can be...

Scrolling problem with Intellimouse #2
I am using a Microsoft IntelliMouse Explorer 2.0 on a Dell computer with Win XP with SP2. Excel is 2002 w/ SP3. Intellipoint is v. 5.20.413.0 (6/3/2004). I just updated driver with no improvement. Unlike in other MS programs on this computer (including Word and IE), I am unable to use the mouse wheel to scroll up and down in an Excel file (spreadsheet). When I use the wheel in Excel, the Zoom changes, even if I click on the spreadsheet itself first. I want to fix this so that when I use the wheel, I am moving up and down in the spreadsheet. (I also use a similar IntelliMouse with ...

Inserting email recipient into email message
I've tried looking only be be frustrated. So not to waste more time, direct me... I would like to insert either the receipients name/address into my email message. My example is this: "Dear {name/address}, " I know Word is used for mail merging, but is that the only way to compose an email with this same information rather than just using Outlook? I appreciate your help. Mail merges require Word. Always have. Always will. -- Russ Valentine "royalones" <mroyal@royalones.com> wrote in message news:D2CDF89E-D0AC-4297-8875-E33FA34058A4@micr...

[Money2007] Cleared or Reconcilied and other font problem
Hello, what does they mean? What's the difference? Another question: in my Working budget I see strange characters (like squaes in the values of column % of income. Does anybody has this problem? Thanks In microsoft.public.money, Pippo wrote: >Hello, >what does they mean? > >What's the difference? Cleared means that the bank has that transaction recognize, such as a check having cleared. Reconciled means that you have checked that the cleared transactions add up to what the bank thinks they have added up to. Those were not "official" but just my rough des...

Tab delimited text problems within Excel
When opening a tab delimited file within excel I am experiencing problems. The file's last column is blank, which is needed, but when I open it within excel it recognises that the column exists for the first 15 rows then it doesn't recognises the column when saving back as a text file.. Any ideas? A lot of programs don't need the tab if there is nothing in the values. But if your program does, how about putting something in that last column that you want. Just put a single apostrophe (') in that last column of each row and excel will understand what you want. Andrew...

apply the formula sign
Hi, I=B4ve imported data from other application and some cells have some formulas like 10*12 but without the =3D sign. How can I apply the =3D sign to all the cells quickly without going one by one so that it calculates the formula. Example, =3D10*12, should be 120. Thanks!! Copy this UDF to a general module in your workbook. Function EvalCell(RefCell As String) Application.Volatile EvalCell = Evaluate(RefCell) End Function In an adjacent cell enter =EvalCell(cellref) Gord Dibben MS Excel MVP On Thu, 28 Jan 2010 07:12:14 -0800 (PST), canvas <spyele123@g...

Excel 2000 formula recalc problem
Hi everybody, I have a problem making sure all the formulas in the spreadsheet recalculate, when recalc is called from within a macro. I have a complicated spreadsheet model that takes a second or two to recalculate. I run a macro that: 1. Sets up parameters 2. Forces recalc 3. Grabs the output 4. Cycles back to step 1 with different parameters, etc. My code looks something like this: For j = pageFirst To pageLast Step pageStep <set up parameters of the j-th scenario> Application.CalculateFull <save the output, which is just a rect...

SetForegroundWindow problem in 2000/XP
Hi I have to bring my application foreground during some events. SetForegroundWindow() function works fine in NT but had some problem in XP. I read in MSDN about SetForegroundWindow() restriction in 2000/XP. But when i tried the following code it seems to work fine in NT and XP . i have not tested it in Win 2000 void BringMyWindowForeground() { // get a pointer to our main window CWnd *pMainWnd = AfxGetMainWnd(); HWND forground_hwnd = ::GetForegroundWindow(); HWND PrevHndl = pMainWnd->m_hWnd; if(PrevHndl == NULL) return; DWORD lForeThreadID = 0; DWORD lThis...

Double value subtraction problem....
Hi When I tried to subtract two double values its giving unexpected results!!!! Eg:- 11.846000000000 - 11.846000000000 Its giving some values like -1.02383489238E12 !!!! instead of ZERO !!!! In an MSDN article (Q59407) it says that " subtracting double values greater than or equal to 1.0E+025 may return inaccurate results". But didn't say anything on how to rectify it!!! How to solve this prob? Please help..... rgds, RENAK RENJITH A K wrote: > Hi > When I tried to subtract two double values its giving unexpected > results!!!! > ...

Partition Problem
Got a wierd one for ya. Everything was going along as normal and then all of a sudden, while out at lunch, our exchange server stopped working. 12 hours later I got it running after several chkdsk, defrag, repeats based on the error messages. The information store is safe and sound and so is the log store. But the drive with the OS on is freaking me out. Under disk management, the graphic display shows 21.77GB of HD space in the C: drive partition. Up top in the list view it shows 12.00GB. dbl-click my computer and C: drive is 12.00GB. What has happened to my partition? ...

Bitmap Printing problem
I am trying to print a bitmap using CDC::StretchBlt function,The application is printing well on some systems but a blank page is getting printed on the others, where all the systems are in LAN and using the same OS(Win XP) & ServicePack 1A(version) and printer drivers(Versions Note:The same is being displayed on the window and there exists no problem. Check out the bitmap printing example in the MSDN. Printing a bitmap isn't straightforward, and in addition, not all printer drivers support bitmap printing. joe On Fri, 30 Apr 2004 08:16:07 -0700, "Bitmap Printing problem&quo...

Problems moving my money file to another computer
I have recently bought a new computer and am trying to move my existing Money file to the new machine. I've copied the file across without any problem, but when I try and open it I get the message 'money cannot open the file or cannot open it, possibly because it is a read-only file or you do not have permission to change it, or your disk drive is write-protected.' I've checked and the file is not read-only. Does anyone have any ideas how to solve this? Thanks Assuming you are using M2005 there was a big download patch that won't be on the original M2005 CD. Ch...

VBA to insert currency
Has anyone created a VBA solution to inserting 12.34 into a field using VBA._ _ _ _ _ _ _ _ _ _ _I have a touchscreen application that is used to complete various forms. One of the forms consists of a few number and currency input boxes that are used elseware in the application (they can’t be text).For the numbers it is simple to add digits to a field formatted as general number.Me.FieldName = Me.FieldName & 1Will add the digit 1 to whatever is in the box so 1234 will become 12341 etcThis of course does not work for currency as you can’t use & sign to add to a currency field.I have ...

Journal
I am using Outlook 2002/XP. I use journaling to keep log hours I have spent on different projects over the weeks. I work on different projects and every day I log entries in the journal (with an Entry Type of Task, Meeting or whatever; and I use the Company field for the Project name; and I key in the number of hours spent in the Duration field). What I would like is a sort of report or view where by given a start and end date the outlook would provide me with consolidated figures (for example, to get total number of hours spent on each project in a given month) Any ideas about...

Why can't I use ::GetAncestor()
Hi, In my MFC App, I got a compile error with the following sentence: ::GetAncestor(hwnd,GA_ROOTOWNER); The Error message is : error C2039: ��GetAncestor�� : not member of ��operator``global namespace''�� But when I use ::GetParent(hwnd), there is no error. There's no GetAncestor() in my version of the SDK (VC6 Maybe you're looking at documentation for some newer API, but have older one installed... >In my MFC App, I got a compile error with the following sentence: > >::GetAncestor(hwnd,GA_ROOTOWNER); > >The Error message is : error C2039: ��GetAncestor...

Wordwrap problem
I'm using Publisher 2002 SP3, as well as Publisher 2007. No matter what I do, I can't get text to wrap around a picture placed inside a text box. The text simply flows through the picture. I have tried to use the inclluded help files, but to no avail. Anyone have some suggestions? Jeff I'm a 2000 user which the fix is, Click on the word text box, Format Text frame properties Options Wrap text around objects. -- Don Vancouver, USA "Jeff Stanton" <jstanton@hughes.net> wrote in message news:CryHj.1014$3N1.932@newsfe17.lga... > I'm using Publish...

formula switches to value ?!?
what toggle, got switched where ? if A7 contains the value: "Christopher" and in B9 I enter: "=A7", it automatically switches the formula to the resulting value (both in the cell, and in the formula bar; the value of B9 is now not "=A7", but: "Christopher") thanks in advance, -mark mark kubicki wrote: > what toggle, got switched where ? > > if A7 contains the value: "Christopher" > and in B9 I enter: "=A7", it automatically switches the formula to the > resulting value (both in the cell, and in the formula bar; ...

inserting multiple columns from combo list
Hey everybody, I am a newbie to access. I trying to pull employee firstName, lastName, and middleInitial from a table and insert them into one employee field in a timecard table. I get the drop down list to show complete names in the 3 columns but when I choose a name I only get the first name inserted into the employee field. Evidently the query is finding everything but I can't get them to cancatenate into the field. Any help is greatly appreciated. Thanks, Andre Use the Column property of the combo box. The column reference is 0 based, so assuming your columns are First, Mi...

Apostrophe Problem
I use a control on a form which I have concatenated from the users First and last names. All works well if I select, Harry Smith, but when I select a name with an apostrophe in it, Kelly O'Malley, I get a run time error 3077. The coding is: Private Sub Combo80_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[Name] = '" & Me![Combo80] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Any suggestions? Thanks Add this to a standard ...

Does Publisher have a template for the CD/DVD slim case insert?
All I found in the templates was the older-style thick CD case insert (Avery 8931). There must be a slimline case template right? There's room in a slim case for a label? -- Don Vancouver, USA "luketech" <luketech@discussions.microsoft.com> wrote in message news:EE08A564-3789-4581-AD53-ABD85F286C40@microsoft.com... > All I found in the templates was the older-style thick CD case insert (Avery > 8931). There must be a slimline case template right? http://www.hdc.uk.com/templates.shtml http://www.amtechdisc.com/printspecs/art.htm -- Mary Sauer MS MVP http://...

plotting "empty" cells that contain a formula that has returned a blank
Cells that appear empty, but contain a formula that has returned a blank, when plotted on a line chart, plot as ZEROS. This is in spite of setting tools, options, charts to "zero values not plotted". Does anyone know a way round this, please. thanks Bob Farey Instead of return a blank (" " or "") result, return a NA(). If you need a null string ("") for subsequent calculations or for aesthetic purposes, create a 2nd dummy range that has NA() instead of "". Plot the range with the NA()s -- Regards, Tushar Mehta www.tushar-mehta.com ...

Problem with Hook
You can view my source at the following: http://www.qtextender.com/code/DllMain.cpp http://www.qtextender.com/code/DllMain.h I call the InstallShellHook() function within my DLL and it seems that the only shell hook notifications I receive are for the actual application that I am debugging. I start Notepad, or any other application and I don't receive a HSHELL_WINDOWCREATED, HSHELL_WINDOWACTIVATED, or any other message for that matter. Any ideas as to why the hook callback would only be triggered for the application that installed the hook? TIA. [CppNewB] > the only shell...

BeforeUpdate problem
I have a form bound to a client list that shows client status and the assigned salesperson ([assignment] field). I have placed code in the BeforeUpdate event of the [status] field that, when the client status is changed from "active" to "inactive" prompts the user for confirmation, and with an affirmative response calls a module procedure that updates records in two other tables, as well as removing the salesperson [assignment] in the underlying table for the bound form. The (2115) error occurs, I assume, when the form's BeforeUpdate event attempts to fire. S...

Problem with calender creation in Outlook
Hi NG, I installed CRM these days and got a bigger problem. (Outlook 2007) When I am creating a calender item in outlook (not crm client plugin, as usually in outlook) and then create a connection to a crm lead or anything like that, I get the error, that maybe the CRM server isn´t availible. If I click ok, I get one more error telling me there is an error with the item and if I wanna ignore it or not. (no matter what I click the result is the same). In my outlook calender, the appointment/item is there like it should be, but not at the crm part. Anyone got an idea how to solve th...

using option buttons #2
sorry im a bit of a noob, what do you mean by frame? :confused -- 4nd ----------------------------------------------------------------------- 4ndy's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=26576 When you're designing the userform inside the VBE, you'll see an icon on the toolbox toolbar that looks like an outline of a frame with xyz in the top border. If you let your cursor linger over the icon, you'll see Frame as the tooltip. If you put your optionbuttons within that...