using for-next index in range variable

MSDN seems to say that range values must be written in the A1 format. I
need to compare a value in column D (sample IDs) of worksheet import
and compare it to column A (location) of worksheet data. If the values
are equal I need to set a cell in worksheet data equal to the sum of
columns I:M in the same row that the sample ID = the location.

Will something like this work:

If worksheet(import).cell[D"i"] = worksheet(data).cell[A"j"]
then worksheet(data).cell[k,j] = sum(worksheet(import).[I"i":M"i"]
Is there a different, perhaps easier way to do this?

0
cporter (9)
12/16/2004 9:43:43 PM
excel 39879 articles. 2 followers. Follow

3 Replies
739 Views

Similar Articles

[PageSpeed] 32

>MSDN seems to say that range values must be written in the A1 format.

Well, yes, sort of, but...

Here is your example, restated in VBA using the Cells property.
(4, 1, 9 & 13 are your fixed columns D, A, I and M restated as numbers).
Not sure if you wanted to actually put a formula in j,k or just the result 
of a formula.  I chose the latter.
**********
i = 9
j = 10
k = 11
If Worksheet("import").Cells(i,4) = Worksheet("data").Cells(j,1) Then
    Worksheet("data").Cells(j,k) = 
WorksheetFunction.Sum(Worksheet("import").Range(Cells(i,9),Cells(i,13))
End if
**********
Note that Cells arguments follows RC notation (Row, Column) not A1 (Column, 
Row).
You might want to look at the Offset property as well.

HTH,
-- 
George Nicholson

Remove 'Junk' from return address.


<cporter@dallas.photronics.com> wrote in message 
news:1103233423.128561.124740@c13g2000cwb.googlegroups.com...
> MSDN seems to say that range values must be written in the A1 format. I
> need to compare a value in column D (sample IDs) of worksheet import
> and compare it to column A (location) of worksheet data. If the values
> are equal I need to set a cell in worksheet data equal to the sum of
> columns I:M in the same row that the sample ID = the location.
>
> Will something like this work:
>
> If worksheet(import).cell[D"i"] = worksheet(data).cell[A"j"]
> then worksheet(data).cell[k,j] = sum(worksheet(import).[I"i":M"i"]
> Is there a different, perhaps easier way to do this?
> 


0
JunkGeorgeN (154)
12/16/2004 10:43:28 PM
I was meaning to do something like this:

Sub particlecertconv()
'
' particlecertconv Macro
' Macro recorded 12/14/2004 by Carl Porter
For i = 1 To 352
For j = 1 To 1079
For k = 2 To 9
If Worksheets("import").Cells(i, 4) = Worksheets("data").Cells(j, 1)
Then
Worksheets("data").Cells(j, k) =
WorksheetFunction.Sum(Worksheets("import").Range(Cells(i, 9), Cells(i,
13)))
End If
Next k
Next j

Next i
End Sub


It steps through well but  I'm getting a run time error '1004'
application defined or object defined error  when running it through
the actual spreadsheet. Thanks for your help.

Carl

0
cporter (9)
12/20/2004 3:44:14 PM
I found the problem, thank you for your help.

0
cporter (9)
12/20/2004 4:51:43 PM
Reply:

Similar Artilces:

select a range in a column
I have two columns in a sheet say column A and B. In Column A I have invoice numbers and in column B a formula which identifes duplicate invoice numbers. As the data varies in length in column A when opening the sheet I want to ensure that a formula in column B returns a value for all the entries in Column A. As part of a macro instruction how can I write in VBA a sequence which will cover all the entires in Column A, which will then allow me to copy the formula in Column B thus ensuring the variable number number of entires in Column A are covered. -- hopalong What do...

colour cells using VBA
Hi New to VBA so apologies in advance if a dumb question! I have managed to get to the following which is working well but I now want corresponding cells in the "k" column to change to specific colours based on the data in "I" and "J" columns and seem to have put myself in a corner - what os the best way to write such a request please? 'Convert to FMECA format, and set matrix row If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or LCase(consequence) = "a" Or LCase(consequence) = "...

Runtime error while using excel help
I get the below error message "A Runtime Error has occured. Do you wish to debug? Line: 0 Error: Object doesn't support this property or method" This message comes up when I'm using Excel 2002 help. However, the message only comes up when I click on the See Also link. To see this link, type in "LEFT worksheet function" under type keywords in help and click search. Any help would be appreciated in trying to solve this. ...

Interrupting long calculations in Excel? Hi all, I am having a very long calculation in Excel using VBA. Of course I know I can hit ESC or CTRL+BREAK to interrupt the VBA program. But I found du
Interrupting long calculations in Excel? Hi all, I am having a very long calculation in Excel using VBA. Of course I know I can hit ESC or CTRL+BREAK to interrupt the VBA program. But I found due to too long running process, the Excel window stop to respond for very long time, besically it's like "not responding" situation with "not responding" on the windows title bar... It doesn't seem to accept any more key strokes, not to say ESC or CTRL +BREAK. What can I do to rein/stop my program? Thanks Try this to speed up execution Application.ScreenUpdating = FA...

Using a text box to enter in a password.
Hello everyone, I have created a login form for our users to enter their user ID and password into two text boxes. The first text box, the users enter in their employee ID, which is alpha-numeric, and the second text box is where the user enters in their password, using the input mask of astericks (*). I am seeing odd behavior in the password section of my form. If a user has a passowrd called "MYpassword", any leading capitals will be converted to lowercase and will read "Mypassword". We have had our users choose another password without any leading capital letters after...

creating and using cell references
I create 2 cell references using ADDRESS() in a column. How do I use these created cell references to sum the numbers? Appreciate your help. joeZy Here's an example: =SUM(INDIRECT(ADDRESS(1,1)),INDIRECT(ADDRESS(1,2))) Excel Support Technician www.canhelpyou.com ----------------------------------------------------------- ----------- Every member of the CanHelpYou team holds at least a Microsoft Level 1 Excel certification. In addition, we have experts in PowerPoint, Word and other products such as IIS, SQL Server 2000, network connectivity. >-----Original Message----- >I c...

Which Icon should I use?
I want to know what is the dimension and the number of colors of the following icons. 1. Icon shown in the task bar 2. Icon shown in the title bar 3. Icon shown in the Explorer Thanks.~~ -- My C++ and C# ( Traditional Chinese ) Web Site : www.franzwong.com/Home.php These icons are created for you (IDR_MAINFRAME) by the app wizard. You will find two sizes of these in resource editor(32x32 and 16x16). I have never investigated other sizes but I gather that would be OK but MFC may not like it. As far as colors are concerened, its number is limited by resource editor capability. You can use...

Using value from 2 tables to fill a cell
Any help would be appreciated, Thanks I need to know the formula (without using VB) to have the Denta Premium and Extended Health Premium taking from two charts/tables ( can put these on the same worksheet or different worksheet within th same workbook, I have no preference) For example below: I enter the name (Test in example), Plan (AA in example), and statu (Family in example). I would like a formula that would fill in th Dental Premium and Extended Health Premium from the following tw charts/tables. Name Test Plan AA Status Family Dental Premium $47.00 Extended Health Premi...

Quantity Requisitioned: where used?
Hi, In "Cards > Inventory > Vendor" I see there is a field called "Requisitioned". I also see a field called "Requisitioned" in "Cards > Inventory > Quantities/Sites". If in "Cards > Inventory > Vendor" I enter a quantity of 25 Requisitioned for an Assigned Vendor ID and close that window. Then I then go to "Cards > Inventory > Quantities/Sites", select an Assigned Site, I notice that I can also enter a different quantity for Requisitioned (like 75). In this case, I see that 75 will be displayed in &q...

Select range of monthly data
I am planning a database that tracks certain data that comes in as a monthly total. So I have a figure for Jan-07, Feb-07, etc. I plan a field for the total and a field for the month/year. In queries I will want to be able to select data that might include a range, such as "between Jan-07 and May-07". My question is, for the field which stores the month and year that the data covers, do I need to make it a date field and enter a complete date, i.e. Jan 30, 2007 in order to select such a range (e.g. select between 1/30/07 and 5/31/07) or does Access have some function that a...

UML use case text not wrapping
Has anybody find an answer to the problem of the text not wrapping ? I saw several posts, but no answers, is the answer to obvious to post? The word "connection" becomes connectio n. Not pretty. I cant show this to my team of programmers...Changing the protection to allow "width and height" changes, does nothing. There seems to be noway to resize these animals! You can unprotect the shape and chane the size of the shape, text box or both. Protection is on the Format menu, you may have to open the shapesheet for other protection settings. John... Visio MVP Ne...

Using XML to serialize a SQL Query
Let me state up front that I know very little about XML. My experience is pretty much limited using the XML Serializer to serialize a user preferences class to a file and back again. I'm writing a rules engine for an application that basically involves allowing the user to enter the the WHERE clause for a SQL Server query that becomes the rule. The rules will be stored in a SQL Server database and the user will be able to make changes to the rules. The rules are entered through a non-freeform interface, so I don't have to try and parse the rule after the user enters it. However...

Using A Find Result in a Range
Hello, I'm trying to search for a word on a spreadsheet, in this case the word "Label." I then want to copy everything from A1 to the row above that word "Label" and paste it to a new spreadsheet. I've got everything worked out except how to use the Find Result address in a new range. I'm sure it's simple, so simple I can't find it in any tutorial. Currently I have to look in each spreadsheet for the last row of the range I want to copy and type the copy range manually into the code for each of the multiple spec sheet spreadsheets I'm att...

SaveAs using two cells for filename
I'm trying to save a file with two cells of data to create the filename. Here is the code: Dim fname With ActiveWorkbook fname = .Worksheets(1).Range("B4").Value & .Range("E6") & ".xls" ..SaveAs fname End With I'm getting stopped on the fname line. I don't think I have the combination right. Can you help? Dim fname as String With ActiveWorkbook.worksheets(1) fname = .Range("B4").Value & .Range("E6") & ".xls" .SaveAs fname End With You missed the .worksheets(1) in front of E6. Any chance that one...

After using MAPI function in Outlook 2003, Can No Longer Retrieve Mail using Outlook
Hi, I am using Outlook 2003 with the Outlook Connector 12.0.6407.1000 to retrieve my Windows Live Hotmail mail. It has worked fine. However, I lost my ability to retrieve my WL Hotmail with Outlook ever since, about five days ago, I used Word 2007 and the MAPI function to try to send a document to someone. That MAPI-sent document got stuck in Outlook's outbox (I just found out). I have since removed it from Outlook's outbox. Since that time, I cannot retrieve mail from WL Hotmail using Outlook. I get this error message: ********* Errors : Time: 11/19/2008 10:45:38 PM Erro...

technik farmaceutyczny http://medyk.zgora.pl/index.php?option=com_content&task=view&id=83&Itemid=38
technik farmaceutyczny http://medyk.zgora.pl/index.php?option=com_content&task=view&id=83&Itemid=38 ...

linking to a range changes
hi, ive made a sheet where i'm linking a cell to another using =$a$ formula. But when i cut and paste the value in a1, my link goes to referenc fault. I thought if i used the $ signs the link wouldn't change. I there any way to fix this? /jock -- Message posted from http://www.ExcelForum.com Hi try =INDIRECT("A1") -- Regards Frank Kabel Frankfurt, Germany > hi, ive made a sheet where i'm linking a cell to another using =$a$1 > formula. > But when i cut and paste the value in a1, my link goes to reference > fault. I thought if i used the $ signs the lin...

Memory use for int, long, etc
I have to load some 400,000 (perhaps upto 1,200,000) numbers in memory, none of them larger than 100. I am looking for ways to reduce the memory load. If I store each as an int, would i use 400,000 bytes of physical mem? If I store four numbers per long, would i use 100,000 bytes of physical mem? In other words, what is the most efficient of storing the lot? Appreciated. Alexander wrote: > I have to load some 400,000 (perhaps upto 1,200,000) numbers in memory, > none of them larger than 100. I am looking for ways to reduce the > memory load. > > If I store each as an int,...

Forms Buttons for use with macro
Bear with me - haven't been on newsgroups in a while. Win 2003 XP PC Colleague is putting buttons (with macros) into a spreadsheet but would like to color them. I can't see where you could. Or can they? Tnx Pat Tell your colleague to use the command button from the Control toolbox toolbar. Rightclick on it in design mode and show its properties. Change the .backcolor. Or tell him/her to use a shape from the drawing toolbar and color it anyway he/she wants. "Ms. Tude" wrote: > > Bear with me - haven't been on newsgroups in a while. > > Win 2003 XP ...

VB Script XL
I'm new to Scripting in Excel and trying to select a Range of cells beginning at B13... want to End-down and then go right 23 cells and have that range active to print... Closest I've come is Range ("B13".End(xlDown)).Right 23.Activate {I've thrown in Right 23 as I don't know how to include that part}... thanx in advance... Try Range("B13").End(xlDown)(1, 24).Activate Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 2 Feb 2010 13:59:01 -0800, Servant...

Email report in PDF Format using a Macro
Using a macro I can generate and export files in pdf format. Then I have to create an email, attach the report and send. Is there a way to email a report in pdf format directly from Access utilizing a macro? I don't have an answer for you but a question. I am trying to do what you have accomplised but don't know how. Thanks "lreecher" wrote: > Using a macro I can generate and export files in pdf format. Then I have to > create an email, attach the report and send. Is there a way to email a report > in pdf format directly from Access utilizing a macro? Once ...

What if scenarios using multiple worksheets
Dear Friends Is it possible to have scenarios in XL 2003 across 2 or more worksheets? Which means, for example, I had some chaging cells in one sheet other cells in another worksheet and the result in the first one. I have tried lately but I got an error message. The same problem I have encountered with the Solver. How could I overcome this issue? Thanks in advance, Catalin If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line ...

how to calculate billable rate using base rate and percentage
I have a billable rate of $70 with a mark up percentage of 17.65%. How do I calculate the base rate? Hi 70/1.1765 so 70/1.1765 = 59.5 59.5*17.65%=10.51+59.5=70 -- Please click "yes" if this post helped you! Greatly appreciated Eva "Felicia Pickett" wrote: > I have a billable rate of $70 with a mark up percentage of 17.65%. How do I > calculate the base rate? (1+.1765)*X=70 X=70/(1.1765) X=59.50 Regards, Fred "Felicia Pickett" <FeliciaPickett@discussions.microsoft.com> wrote in message news:FB923CF3-3657-4D7C-8...

Passing parameters using URL in isv.config.xml
I am trying to pass parameters using isv.config.xml's URL and receive parameters in asp. I have set the passparams="1" and winmode="0", since I want to open up a webpage. Using winmode="1" or "2", I am able to get the guid using windows dialog arguments, thats not really I want as I need to access the webpage which has javascripts which will not work on posbacks. I am having trouble how to pass parameters dynamically using winmode="0". Part of the isvconfig script: <Entity name="new_entity"> <Grid> <MenuBar> ...

Help with Formula using Analysis ToolPack Function ISODD #4
Hi Peo, Thank you for all your time and help: the formula works perfectly. Tin� Peo Sjoblom Wrote: > > This works for me > =SUMPRODUCT(--(MOD(INDEX(ROOMS,ROW(1:1),),2)=1)) > > copied down, if you copy accross replace > ROW(1:1) with COLUMN(A:A) > > Regards, > Peo Sjoblom > > "Tin�" <Tin.1eji6m@excelforum-nospam.com> wrote in message > news:Tin.1eji6m@excelforum-nospam.com... > > > > Hi Peo, > > > > Thank you for assistance. The formula is fine - but is there a wa > I > > can avoid manually changing t...