For Each Next help

Here is my code.

Dim C As Range
        For Each C In Range("TotalsCells").Resize(1, 0)  -----IT FAILS
AT THIS LINE
            Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)"
        Next C

I have a couple of questions.

Is my variable declaration correct? I want to inspect each cell in the
range.

Is my Resize syntax correct for inspecting each cell?

Is my Sum syntax correct?

Thanks in advance for your help.

0
troy_lee (13)
10/8/2008 5:06:18 PM
excel 39879 articles. 2 followers. Follow

7 Replies
404 Views

Similar Articles

[PageSpeed] 12

On Wed, 8 Oct 2008 10:06:18 -0700 (PDT), troy_lee@comcast.net wrote:

>Here is my code.
>
>Dim C As Range
>        For Each C In Range("TotalsCells").Resize(1, 0)  -----IT FAILS
>AT THIS LINE
>            Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)"
>        Next C
>
>I have a couple of questions.
>
>Is my variable declaration correct? I want to inspect each cell in the
>range.
>
>Is my Resize syntax correct for inspecting each cell?
>
>Is my Sum syntax correct?
>
>Thanks in advance for your help.

I assume "TotalsCells" is a range name defined in your worksheet.

Try omitting the Resize

	for each c in range("TotalsCells")
		...

--ron
0
ronrosenfeld (3122)
10/8/2008 5:15:46 PM
Yes, it is a declared range.

I tried this code without the resize. It locks up the Excel and forces
me to close it through the Task Manager dialog box. I'm guessing
because the loop is faulty.

With the resize code I get an application or object defined error
(rather ambiguous, at best).

0
troy_lee (13)
10/8/2008 5:23:45 PM
If you want a running total of b2 to the last cell, try this
Sub ddd()
  Range("TotalsCells").Formula = "=$b$2:b2"
End Sub

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<troy_lee@comcast.net> wrote in message 
news:a1c823f9-0787-4c45-99df-58b075e86e05@a19g2000pra.googlegroups.com...
> Here is my code.
>
> Dim C As Range
>        For Each C In Range("TotalsCells").Resize(1, 0)  -----IT FAILS
> AT THIS LINE
>            Cells.FormulaR1C1 = "SUM(R2C:R[-1]C)"
>        Next C
>
> I have a couple of questions.
>
> Is my variable declaration correct? I want to inspect each cell in the
> range.
>
> Is my Resize syntax correct for inspecting each cell?
>
> Is my Sum syntax correct?
>
> Thanks in advance for your help.
> 

0
dguillett1 (2487)
10/8/2008 5:24:21 PM
This is the answer for anyone interested.

Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
0
troy_lee (13)
10/8/2008 5:42:04 PM
If ?? you like r1c1 style
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
<troy_lee@comcast.net> wrote in message 
news:719655e1-24c3-465c-b1b1-f31d000be3c8@w1g2000prk.googlegroups.com...
> This is the answer for anyone interested.
>
> Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)" 

0
dguillett1 (2487)
10/8/2008 5:51:23 PM
"Don Guillett" <dguille...@austin.rr.com> wrote...
>If ?? you like r1c1 style

Who wouldn't? R1C1 makes this a simple 1-liner. A1 referencing would
require much greater complexity when not working with hardcoded
references.

>dguille...@austin.rr.com<troy_...@comcast.net> wrote in message
>>This is the answer for anyone interested.
>>
>>Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)"

You don't need to know where TotalCells is, but the formula will
always sum from row 2 to the row just above TotalCells in the same
column as TotalCells. If TotalCells were D5, the formula would become
=SUM(D$2:D4). If TotalCells were X99, the formula would become =SUM(X
$2:X98). R1C1 reduces errors once you get used to it.
0
hrlngrv1 (375)
10/8/2008 6:47:19 PM
Harlan, I didn't say there was anything wrong with it, if that is what is 
preferred. I just don't like Jack Daniels when there is Wild Turkey 
available.

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com
"Harlan Grove" <hrlngrv@gmail.com> wrote in message 
news:2e934fdc-f8bc-414b-8c34-6fdfda68ce81@40g2000prx.googlegroups.com...
> "Don Guillett" <dguille...@austin.rr.com> wrote...
>>If ?? you like r1c1 style
>
> Who wouldn't? R1C1 makes this a simple 1-liner. A1 referencing would
> require much greater complexity when not working with hardcoded
> references.
>
>>dguille...@austin.rr.com<troy_...@comcast.net> wrote in message
>>>This is the answer for anyone interested.
>>>
>>>Range("TotalsCells").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
>
> You don't need to know where TotalCells is, but the formula will
> always sum from row 2 to the row just above TotalCells in the same
> column as TotalCells. If TotalCells were D5, the formula would become
> =SUM(D$2:D4). If TotalCells were X99, the formula would become =SUM(X
> $2:X98). R1C1 reduces errors once you get used to it. 

0
dguillett1 (2487)
10/8/2008 7:08:20 PM
Reply:

Similar Artilces:

Formula Help #6
difficult formula How to explain this. In a range of cells, row J4:AB4 I will be entering numbers weekly, eventually filling up to AB4. When I enter a number into, lets say, O4, I want a calculation of the average of ONLY L4, M4, N4 Next week, when I enter a number into P4, I want a calculation of the average of ONLY M4, N4, O4 So the result, in the cell that the formula is in, is changing weekly. I hope this is clear. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com Please do not multi-post Peo has given a suggestion to your post in: microsoft.public....

Help! Can I store my files on a disk for future use?
Is it possible to store my messages on a disk and bring them up at a future time using the disk? If so, how can I store them. We are changing servers and I have to reduce the size of my files. Please respond ASAP. Thanks, Liz File > Import and Export > Export to a file File Type: Personal Folder File (.pst) Note, save this file to your hard drive and note the location. Once the file is completed, copy it to your other disk (probably a CD-R). -- Charles Kenyon Word New User FAQ & Web Directory: http://addbalance.com/word Intermediate User's Guide to Microsoft Word (sup...

formula help #26
Is this possible? Find all cells in a column which match an item from a pull down list. Perform a sum of those rows in a different column and divide by a count of the matches to obtain an average. for one cell it might be like this. If X1 = C3 then R1=(Running SUM of C column matches)/ Count of C Column matches Where X1 is the pulldown value selected. R1 is the result -- Rikk Flohr gallery: www.fleetingglimpse.com consulting: www.fleetingglimpse.net blog: http://spaces.msn.com/members/fleetingglimpse/ =AVERAGE(IF(C1:C100=X1,C1:C1000)) which is an array formula, it should be com...

SQL help please
I've got a query that I just can't seem to get right. I have a table (orders) with itemName and itemQty. Every order makes a new row so I can have many rows with the same itemName plus the itemQty that was ordered. What i'm trying to do is get how the total number of each item ordered. First try: SELECT itemName, count(*) as popitemCount FROM Orders Group By itemName ORDER BY Count(*) DESC Looks good except it only tells me how many times each item was ordered regardless of itemQty, not how many total of each item. I need something like.. SELECT itemName...

help with pivot tables
I have created a spreadsheet which has multiple pivot tables the only problem is that my source data can contain anything from one item to fifty items therefore the pivot tables can overlap if two adjoining pivot table both contain alot of information, how do I solve this problem I want the pivot table when refreshed to insert and move everthing else either down or across plese help ...

Urgent please help
Hello, everytime I open a bank document or an existing document (open anything or even when close visio) I got the error message "Visio.exe has generated errors will be closed by windows. You will need to restart the program.". Any suggestions are greatly appreciated! M Sounds like a corrupt installation. I would uninstall and reinstall. Beforehand though, an quicker thing to try is to see if the bin file might be corrupt. The location of the bin file varies based on the version of visio but you can do a general search on your computer and delete any files named visi...

Help reg integrating VC ++ with Flash
hi, I am doing a dialog based app USING MFC. I need to interact with the fron end with Flash. Can anybody help me in this regard? The flow of app is ..... Create a Dialog based app Insert a Flash based control. Insert a Flash based list box and insert the values. When I click or select the values from the Flash controls,the values selected from List box should be caught by MY Dialog. Regards ...

weekly cash flow template setup
hi, basically i am designing a weekly cash flow manager and am in need of some help. i want to make one sheet where you input all the data ( eg weeks' takings, expenditure, wages, WEEKNUMBER, PREVIOUS BALANCE) and then when you click a button it will copy that sheet and rename it to the week number and then it will clear the template sheet. however the week number starts from june. would i have to type these in manually or can i somehow get it to calculate the week number based on the last sheet added. also in the template i need a cell where it displays the previous week's b...

Pivot tables / source data
Is there a way to take a pivot table and copy and paste it into a new document so that the source data cannot be pulled up by the person looking at it? The reason I need to do this is because I have created a pivot table and included in the source data is very confidential information. I want to be able to send out JUST the actual pivot table, but I don't know how to do this without releasing all of the other information along with it. Is this even possible?? Have you tried doing <copy> (on the table), then moving the cursor to another sheet and Edit | Paste Special | Values (ch...

Exchange 2003 Mail Wizard is not working -- Please Help!!
When I go through the wizard I get an error "To send Internet email, the selected Server cannot be a bridgeheade Server then if I deselect send internet email I get "The SMTP virtual server 'Default SMTP virtual server' on the selected server does not have the binding of all unassigned/port 25 What does this mean Will You cant run Internet Mail Wizard twice. If you do that you olways get erorr "To send Internet email, the selected Server cannot be a bridgeheade Server" You must delete Internet Mail Connector and ReRun Internet Mail Wizard I think this help y...

TRANSACTION PROBLEM, Pls Help me!
I have fallen into TRANSACTION problem in MSSQL2005. I created small easy to understand sample which throws same error as in my problem. So sample is : ============================================== CREATE PROCEDURE dbo.spTempProc2 AS BEGIN TRANSACTION CREATE TABLE #aa (col INT) ROLLBACK RAISERROR ('error test', 16, 1) GO =========================================== CREATE PROCEDURE dbo.spTempProc1 AS BEGIN TRANSACTION CREATE TABLE #aa (col INT) INSERT INTO #aa SELECT 1 EXEC dbo.spTempProc2 IF @@ERROR <> 0 RETURN COMMIT TRANSACTION GO ==============...

Help Using a Combination Function
BACKGROUND: I'm trying to create a spreadsheet that would help me do the monthly schedule for our nurses more efficiently. Right now, the spreadsheet is just fully manual, add all the dates (to six sheets), review the schedules cell by cell to make sure we have enough people scheduled and then take the daily schedules and apply to yet another spreadsheet that shows the daily schedule (versus the 28 day schedule). CURRENTLY: I have created a new spreadsheet that has already automated some of these functions. I have created a cell so that I only have to enter one date and ...

HELP please
Hi, I have 3D data that I would like to plot in Excel. This should be straightforward using the surface chart template in Excel. However, the chart that I produce when I follow the Excel wizard looks nothing like a 3D surface plot (it looks more like 2D). The data I am plotting most definitely has a surface, as there is a well known series of mathematical models underlying the observed data. I have seen several publications that have surface plot diagrams which they claim was created using Excel - what am I doing wrong ? (Pushing my luck here ...) Once I have managed to get a 3D surface plo...

Help function in Office 2007
I have lost the Help function in Office 2007 Home and Student edition. When i press the help icon, I get the dialog but it doesn't do anything. If i search on a word I get no response. If i click on a topic in the table of contents, I get no response. I am running Vista. Have you tried repairing your Office installation to see if that helps? -- Susan Ramlet -- please reply to the newsgroup so all may benefit. "rjirak" <rjirak@discussions.microsoft.com> wrote in message news:BB5D4CC0-4FC6-4FF8-80AC-5B1F7AA805C4@microsoft.com... > I have lost the He...

Unable to open Excel Help and Visual Basic Help
Despite deleting and then reloading the whole of MS Office including Help files, am unable to open Help files - window opens okay, but not populated. I'm probably doing something dumb, but what? Any help gratefully received - thanks. ...

Help with file types
I have a pc going bad and I'm trying to save Publisher files. I copied them using DOS to the a: drive. The problem I'm haveing is that the file typs are .BDR I copied them to another PC using Publisher 2000. but PUB 2000 does not recognize the . BDR extension. Any ideas how to save these files so they are usable? Thanks in advance. WWV I have Pub 2000 and 2002 but I've never heard of a .bdr file. Are you sure they were somehow created with Publisher? "WWV" <venterp@yahoo.com> wrote in message news:%23J4wWQrlDHA.2068@TK2MSFTNGP09.phx.gbl... > I have a pc ...

countif help
How do I use countif to select between numbers. If I have a column of numbers and I want to find numbers greater than, lets say, 100 and lower than 200. Would I have to use AND? Is there an ANDCOUNTIF formula or can I add more to this formula? =COUNTIF(A1:A30,">100") Here is where I get lost. Thanks, Ken try =sumproduct((daterng>a1)*(daterng<=a2)) -- Don Guillett SalesAid Software donaldb@281.com "Ken" <kwill1050@mindspring.com> wrote in message news:qb7id.17172$5i5.13587@newsread2.news.atl.earthlink.net... > How do I use countif to select between num...

help on customizing/adding reports to MS-CRM 1.0
Hi, I have MS-CRM 1.0 installed.This installed Crystal Enterprise 8.5 with some standard reports. In order to customize/add custom reports to the enterprise, MS-CRM implementation guide asks for Crystal Reports 8.5. Some queries....Thanks in advance for all ur help/directions.... 1>I wanted to know if I can use the Crystal Reports for .NET that comes with Visual Studio.NET 2003 to edit/add reports to Crystal Enterprise 8.5 that comes with MS-CRM 1.0.If so, do I face any limitations(in features etc)in using Crystal reports for .NET compared to using independent Crystal Reports 8....

Help unable to reinstall Money 2006- 4 years of data lost
Hi everybody, I bought Money 2006 on november 11, 2005. I have my data uptodate and up to this monday... I had a complete crash on tuesday Reinstalled everything perfectly up to now.... When trying (and following all the Microsoft FAQ and stuff) to reinstall my 2006, it does an update and on rebooting and trying to use Money with my more than 4 years of DATA.... it says the following: "Your trial version of Microsoft Money has expired. To purchase Microsoft Money 2005 Deluxe, take advantage of this special ,,,,,,,page not working......, or contact your local reseller." I am l...

Stopping help for circular references
When I create a circular reference, Excel spends out 2 minutes trying to display help and the circular reference tool bar. I don't mind the toolbar, but how do I stop the help from being displayed. Hi, This is one way to prevent the error and the help window from opening: Tools -> Options -> Calculation, check iteration. Now Excel only calculates the circular references only so many times, that one of the specified conditions (maximum iterations OR maximum change) come true. - Asser ...

HELP! Clean upgrade to WinXP
Ugh... a bit of a mess - kind of an emergancy thou Story: Having problems with PC using Windows98se, many little things... both hardware and software related. Reloaded Windows - resolved hardware issues - but Win98 was apparently the software problem. Did a Clean install of WinXP Pro on computer, proceeded to move data files onto the the main hard drive. Installed software (Office2000, etc) Problem: The original outlook PST files are NOT working correctly with Outlook2000 under XP. The data is good (I have 3 backups of it). When needed to replace a damage or transfer the outloo.pst file,...

Help me Please!! Need hyperlinks to show actual email address!
I copy a list of names from one page and put them in a excel spreadsheet. What happens is just the name show like DUSTIN MCALISTER but when you hold the mouse over the name the email address shows up in a little yellow box showing what the hyperlink is. Is there a way to make it show the email address instead of the hyperlink?? That way I can click on a row of names and all the emaill addresses will be there so I can copy and paste them into my Email and mass email my clients? Thanks! Dustin, You could add this little UDF Function eMail(rng As Range) Dim tmp As String On...

Word and Publisher file exchange
I know there is a word file import function in publisher; however, when you do this there are two problems which I have been unable to figure out. First, if you import Word file text and then attempt to save the file you've created, it will only save as a Publisher file. So, if you wanted to send this file using the nice formats of Publisher as a Word file, you cannot? Most people use Word, but not Publisher. Second, if you use the Letterhead creation function within Publisher, and you like to format you've created and save it, I have not been able to figure out how to export...

Help with this needed!!
I have a workbook with two worksheets. Sheet 1 is an itemisation for December 2003 for customers who have cancelled a subscription to a DVD/magazine service (reason for cancelling, number of cancellations for each reason). Sheet 2 is a complete listing of all customers who have ever cancelled along with their cancellation date and reason for cancelling. This sheet contains data going back three years. What I would like to do is use a lookup to populate the number of cancellations for each reason in sheet 1 from the data held in sheet 2. I tried messing around with =sumif but I'm strug...

| | | | |HELP | | | | Server went down, anyone have an email RECOVERY program, I have and offline .ost file
| | | | |HELP | | | | Server went down, anyone have an email RECOVERY program, I have and offline .ost file Lost all contacts and emails, but have an upto date offline file on my laptop... TIA ExchangeRecovery http://officerecovery.com/exchange/?204800 ExchangeRecovery is data recovery program for Microsoft Exchange Offline Storage Files. In case of server crash or accidental deleting of a Microsoft Exchange Server mailbox, Offline Storage Files (.ost) remain on client computer, containing emails, contacts, tasks and other items from the original mail account. ExchangeRecovery conve...