Run-time error 1004 11-16-09

The code that I am trying to use is very simple.
I would like to replicare automatically a formula along many cells, one yes 
and one no along a given range.
So I tried to build a macro:
sub a ()
for i 4 to 400 step 2
range(i).formula="=if(a2<b2;a2;b2)
next i
end sub
The macro doesn't work even without the for-next cicle.
On the contrary, the formula =if(a2<b2;a2;b2) works as expected when placed 
in a cell of the worksheet inside which I want to run the macro.
What am I doing wrong?
Thank you for your help,
Riccardo
0
Utf
11/16/2009 1:12:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1215 Views

Similar Articles

[PageSpeed] 26

Not sure that the following is really the answer you require.

Firstly my example uses the comma (,) as a delimiter in the formula so I am 
not sure if the semi colon (;) is causing you a problem or perhaps you use 
that in your country of origin.

When you want to use a formula with relative addressing then you need to 
code the formula in the first cell then the next line of code copies that 
cell to the remaining cells something like the following.

Range("A3").Formula = "=IF(A2<B2,A2,B2)"
Range("A3").Copy Destination:=Range("A3:AH3")

If this does not answer your question then perhaps you can supply a little 
mor explanation with an example of your data.

Regards,

OssieMac
0
Utf
11/16/2009 1:51:01 AM
VBA is USA centric.

Use commas instead of semicolons.

But I'm confused about what Range(i) is.



sub a ()
 for i = 4 to 400 step 2
   range(i).formula="=if(a2<b2,a2,b2)"
 next i
end sub

Riccardo Pucher Prencis wrote:
> 
> The code that I am trying to use is very simple.
> I would like to replicare automatically a formula along many cells, one yes
> and one no along a given range.
> So I tried to build a macro:
> sub a ()
> for i 4 to 400 step 2
> range(i).formula="=if(a2<b2;a2;b2)
> next i
> end sub
> The macro doesn't work even without the for-next cicle.
> On the contrary, the formula =if(a2<b2;a2;b2) works as expected when placed
> in a cell of the worksheet inside which I want to run the macro.
> What am I doing wrong?
> Thank you for your help,
> Riccardo

-- 

Dave Peterson
0
Dave
11/16/2009 2:03:41 AM
To take what Dave Peterson a step further: if you are using i as a row 
counter, then you probably need to change the line that sets up the formula 
from
Range(i).Formula = ...
to
Range("C" & i).Formula = ...
assuming C is the column where you want to put the formula.

"Riccardo Pucher Prencis" wrote:

> The code that I am trying to use is very simple.
> I would like to replicare automatically a formula along many cells, one yes 
> and one no along a given range.
> So I tried to build a macro:
> sub a ()
> for i 4 to 400 step 2
> range(i).formula="=if(a2<b2;a2;b2)
> next i
> end sub
> The macro doesn't work even without the for-next cicle.
> On the contrary, the formula =if(a2<b2;a2;b2) works as expected when placed 
> in a cell of the worksheet inside which I want to run the macro.
> What am I doing wrong?
> Thank you for your help,
> Riccardo
0
Utf
11/16/2009 3:28:05 AM
Reply:

Similar Artilces:

Sound "times out" on web cam after 5 minutes or so...
I have decent picture and sound quality on webcam but after about 5 minutes it seems to "time out" and the sound goes away. The person I'm talking to can still hear me but I can't hear them. I restart the video call and it works again... for about 5 minutes. Has anyone else had this problem or does anyone know how to fix it? It only started the last 2 times I have spoken with my dad on webcam. I have Windows Vista and the newest 2009 version of messenger. Please help! -- Thanks~ Ali Greetings Ali, Just out of curiosity, if you just do an audio call...

Excel 2007 error when opening program
"The INI file containing the temp chart file name could not be found" What does this mean and how can it be corrected. Recently upgraded from 2003 to 2007 ...

Time sheet webservices or API?
Hello! I am a .Net developer. Until about 6 months ago our IT shop had a custom time tracking application written in Silverlight. It was exceptionally smooth with auto completes, notes, and a wide assortment of other indicators that helped us identify critical projects and time allotment. Then we switched to MS Project Web Access. Don't get me wrong, I LOVE Project, and that I can use it on the web is awesome. But the Time Sheet interface over the web is horrendous. It's slow, cludgey, inconsistent, and a royal pain in the butt. So around the shop we were chatting...

Calculating diff between date/ time only between certain hours 03-01-10
I am trying to find the diff in hours only between 8:00 and 17:00 Monday to Friday only, any hours that fall outside of that time frame should not be counted. Each calculation will have different dates so if there is a way to do this without specifying the date that would be great! 2/19/2010 16:00 2/22/2010 9:00 ??? Thanks for any assistance you can offer! Hi there GMv1, Assuming "2/19/2010 16:00" is in cell A1 and "2/22/2010 9:00" is in cell B1. The formulae in cell C1 would be as follows : =INT((B1-A1)*24) Let us know if this helps yo...

VBA Error
I have a Excel Spreadsheet which runs without problems on both Windows 2000, and Windows XP, but when I run the spreadsheet on my laptop with Windows 2000 I get the following error code. Microsoft Visual Basic Run-time error '9' Subscript out of range code stops hear: With Workbooks("Time_Sheet").Worksheets("Data_Entry").Range("A1").SpecialCells(xlCellTypeLastCell) End With With Range("A1").SpecialCells(xlCellTypeLastCell) Row = .Row Workbooks("Time_Sheet").Worksheets("Data_Entry").Names.Add Name:="Database", ...

Compile error
I'm trying to set up a multiselect listbox for a query parameter. I found a reference to http://www.mvps.org/access/forms/frm0007.htm for code to accomplish this. However when I copied and pasted this code into my code window the last line - strSQL=left$(strSQL,len(strSQL)-12)) was highlighted in red. Debug shows it to be a syntax error. How do I correct this? -- Thanks for your help! Walter Correct me if I'm wrong, but do you not have 1 too many brackets??? Should it not be: strSQL = Left$(strSQL, Len(strSQL) - 12) -- Hope this helps, Daniel Pineault htt...

Windows error code 80070652
After updating manually, get error 80070652--unknown error encountered Started as 4 updates, 1 failed with error. rebooted same results. x86 Win 7 ultimate, 2 gig ram, Acer Aspire 6920 -- Art Artsr3 wrote: > After updating manually, get error 80070652--unknown error encountered > > Started as 4 updates, 1 failed with error. rebooted same results. > > x86 Win 7 ultimate, 2 gig ram, Acer Aspire 6920 80070652 ERROR_INSTALL_ALREADY_RUNNING Another installation is already in progress. Complete that installation before proceeding with this install. Sinc...

Some poor slob installed Ubuntu. Needs to run line commands to get that piece of crap to work
I installed ubuntu 9.10 and it was rebooting just before the login screen. After really long search it turned out it's the video driver, I guess the one coming with ubuntu didn't like my card. I have integrated Radeon HD3200. What worked for me: 1. Select recovery mode from grub menu (in your case for 9.10 too) 2. In the first following screen select "root with networking" to have internet 3. Install video driver package EnvyNG with: sudo apt-get install envyng-core envyng-qt <==================== line commands to get sucky Ubuntu to work. Oops. 4. Run i...

Cell Error Message
I am running Excel 2002 and keep getting a comment like deal in all cells that are not calculating to the end of the range. For example if I have data in Col. a.b.c.d.e.f but do not include Col. a, I get a comment type message that the formula did not calculate the entire range(which I do not want it to). I know that I can turn this off manually (in each and every cell effected)(about 50), however does anyone have an idea of how to turn it off completely, either my use of a VBA or Excel itself. Thanks Frank ...

Scheduling Error
I have a user that is receiving an error message when promoting an appointment into CRM. The error message only occurs if the user tries to track appointment that was created in the past. If the appointment is in the future, the user receives no error message. r/ Anthony ...

Blocked email from sending by error
In MS Exchange 2003 Server, Is it possible to stop email from deliver to recipient immedialy after sent by mistake. Or recall an email completely after the email is delivered to recipient inbox (recipient no chance to read the email). (Other than using the "Recall" feature in MS Outlook) "2Sweet" <chongcmw@hotmail.com> wrote in message news:ue5T4cCmKHA.2592@TK2MSFTNGP04.phx.gbl... > In MS Exchange 2003 Server, > > Is it possible to stop email from deliver to recipient immedialy after > sent by mistake. > Or recall an email comple...

Rookie-building DB-want to get right the first time! Help?
Rookie user: Access 2007, Using “Picture Yourself Learning: Microsoft Access 2007” as reference guide. Need suggestions for how to structure what seems to me to be a very complicated DB. I want to build it the best way the first time, so I don’t do a lot of work and not have it do what I need. Here’s what I need to do: Track GIS datasets for about 50 natural and environmental hazards. The data itself does not need to be tracked. I do need to track its source (National, State, County or City data) and know its date of creation, and frequency of updates for example. I don’t k...

Error Backing up System State (2010 RTM)
Hi, I have just upraded to 2010 RTM (eval) from 2010 RC and now the system state is failing for one of my protected member servers. DPM server = BACKUP1 Protected Member = APPV01 Error: The Backup to tape job failed for the following reason (ID 3311) More Information: Protected Member: COMPUTER\System Protection (APPV01.DOMAIN.LOCAL) Error Details: DPM encountered an error while performing an operation for C:\WindowsImageBackup\ on backup1.domain.local (ID 2033 Details: the system cannot find the path specified (0x80070003) Please Help? Can you please mail m...

Cannot find file error
HI, I have a machine that comes up with an error when "double-clicking" to open a file. When I double click any excel file I get this error. Cannot find file "..Filename..." (or one of its components). Make sure the path and filename are correct and that all required libraries are available. The file can be on a file server or on local disk and I still get this error. I tried installing Office 2k SP3 in hopes that would fix something but, that hasn't worked. any hints? thanx, In Excel Go to Tools Options General and uncheck Ignore Other Applications ...

error :unhandled exception:0xE06D7363:
unhandled exception in mfcclient.exe(Kernel32.dll):0xE06D7363:Microsoft C++ exception Ankit Aneja Software Developer There's a bug in your program. Seriously, you need to give a whole lot more information, such as the stack backtrace to your source code, what you were trying to do, etc. joe On Sat, 1 Oct 2005 19:03:54 +0530, "Ankit Aneja" <efextra@newsgroups.nospam> wrote: >unhandled exception in mfcclient.exe(Kernel32.dll):0xE06D7363:Microsoft C++ >exception > >Ankit Aneja Software Developer > Joseph M. Newcomer [MVP] email: newcomer@flounder.com...

Runtime errors with Release Build
Hello I have a relatively simple dialog app that polls a network device over SNMP (about 1hz) and sends a UDP status message. I am getting the dreaded release-build-only runtime errors -- all access violations. I have read the excellent articles by Dr. Newcomer and Bruce Dawson, but still can't track down the problem. Briefly, I have enabled debugging info and symbol map with release builds and disabled optimization. I am linking statically. I can run the application and let it sit; it will not crash until I interact with it somehow to generate a GUI message (e.g. move the mouse...

CRM e-mail router config error
Hi Whenever i run the email router config i get the following error (translated from dutch) "Client has discovered that the content of the answer text/html;charset=utf-8 was, but expected text/xml. I have the CRM4 running on another server, all the clients can reach it perfectly, upgraded to update rollup 1. There are multiple websites on the crm server, i am using the same url we use for web access: http://crm/companyname I have the feeling the installer is reaching the wrong site. Can someone help me out on this? thx! Hi I also have this when i want to configure the crm cli...

error C2065: "TTM_SETTITLE" undeclared identifier
I want to add a icon to ToolTip,then I use following code: SendMessage( (HWND) pToolTipCtrl->m_hWnd, (UINT) TTM_SETTITLE, // adds a standard icon and title string to a ToolTip (WPARAM) tti_ICON, // TTI_NONE = 0 - no icon // TTI_INFO = 1 - information icon // TTI_WARNING = 2 - warning icon // TTI_ERROR = 3 - error icon (LPARAM) (LPCTSTR) title ); But,compile failed error C2065: "TTM_SETTITLE" undeclared identifier Smallfrogs "Smallfrogs" <super_smallfrogs@hotmail.com> wrote in...

Forms used to run query
I created a form from where the information is selected to run the query. The problem is that I want to pull information from two different columns inthe query. In other words, I have a query that pulls information from 5 differente tables. I need to query the info by building/department and by full name column. I created a form from where the user can select the building to run the query, but I also want to get the employees full name, only employees located in that building so that I can run a report based on that employee. I hope I make sense! :) Any help would be greatly ...

Scattered chart combined with columns and time-x-axis in XL2007
Dear Group, until XL2003 I could create a column chart, configure the x-axis to as a time-axis (for x-scaling purposes) and I could place scattered charts (as carrier for text and symbols) at any (non-integer) x-position. Using XL2007, scattered charts can only be positioned on x-integer-values. So I am forced to put a second transparent scattered-chart over the column-chart? Or is there any better way to do this in ONE chart? Thanks in advance: Holger. Ironically, the XY series can plot anywhere along a text axis in 2007. The first category is at X=1, the second at X=2, etc. I hadn...

Using counter "i" in for loop gives error
Fellow excel programmers, I have the following little program which gives me an error: Sub code() ' ' code Macro ' ' Keyboard Shortcut: Ctrl+o ' For i = 1 To 5 ActiveCell.FormulaR1C1 = &quot;=CODE(MID(R[-1]C1,LEN(R[-1]C1)-(LEN(R[-1]C1)- i),1))&quot; ActiveCell.Offset(0, 1).Range(&quot;A1&quot;).Select Next i End Sub The problem is that when I run this code it gives me a &quot;VALUE&quot; error. The computer claims that the &quot;i&quot; in this part of the code (LEN(R[-1]C1)- i),1)) is what causes the...

Background Banking error crashes IE
I have Money Plus DLX adn have enabled background banking. When Im navigating in IE it will crash and the log says " The instructions at "0x7c910e03" referenced memory at "0x00000000". The memory could not be written". This appears in a dialog box titled "MICROSOFT MONEY BACKGROUND BANKING: MNYBB.EXE-APPLICATION ERROR". I beleive its caused by some sort of security software or autofill software but cannot narrow it down. Any Ideas would be greatly appreciated. In microsoft.public.money, Jixa wrote: >I have Money Plus DLX adn have enabled back...

error C2664.
Hi, i have an error in my application(vs2005). First i list .h and .cpp files and then i provide error description. If anybody know the solution please help me out. *************modifymediapage.h************** #include <vector> class Cmodifymediapage { ....; private: vector<CString> & m_vFieldVals; }; ****************modifymediapage.cpp**************** //this gives index of combo box selection int iPageSelect = m_PageNumber.GetCurSel(); if(m_vFieldVals.size()>=(iPageSelect+1)) { m_vFieldVals.erase(&m_vFieldVals[iPageSelect]);//error pointing here...

cannot set up outlook ....services 32 error...shuts down??
every time I put in my pop3 and smtp info a box pops up telling me services 32 error and shuts down outlook. Can not get past this?? What version of Outlook? Is that all there is to that error message? -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** "greg" wrote: > every time I put in my pop3 and smtp info a box pops up telling me services > 32 error and shuts down outlook. > Can not get past this?? ...

comparitive time period reporting
I am interested in the best value way to get comparative reporting which for example would compare April 2006 with April 2007 sales for all my stores in an easy to read way, perhaps by department/catagory/suuppier. I've asked my reseller about this, but they have quoted me �4000+ to develop this sort of reporting pack. Any advise? -- Philip Gass Donaghadee Garden Centre Philip Gass - Creative Gardens - wrote: > I am interested in the best value way to get comparative reporting which for > example would compare April 2006 with April 2007 sales for all my stores in > an ...