Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Sum Range Defined By Reference Cell


I have a list of numbers in Column A and in Column B I have a standard Sum formula which sums the adjacent number in Column A and the four numbers before it.

A B

1 =Sum(A1:A5)
3 =Sum(A2:A6)
5 ...
3 ...
5
6

I would like to have a formula which references another cell to define how many rows to sum from the starting cell. So in the above example, the total number of rows which are added together is 5. If I wanted to change all of the SUM formulas to add 6 rows, I would like to be able to change the number in the reference cell to 6 and all of the sum formulas would switch to adding 6 rows.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Cell Reference Used For Defined Range
In the attached sheet. Cell C6 represents the concatenation of aspects of a product. I want to know if it is possible to use that cell as the named range (there is a corresponding named range) in a lookup. The lookup gives me the product code.

View Replies!   View Related
Cell Reference Or Defined Name For A Sheet
I'd like to be able to refer to a cell or local scope defined name within my sheet that references another sheet, or accomplish the same functionality in some way.

For example, if I have a sheet MyInformation and then another sheet MyPrintedInformation, where the printed information sheet shows MyInformation in a more printer-friendly format, I might have this in MyPrintedInformation:

a cell or defined name that says (this doesn't work, but just as an example):
let's call the named variable TheSheetToPrint
='MyInformation'. Then from within MyPrintedInformation all of my references look like this

Some cell :

=TheSheetToPrint!A2

View Replies!   View Related
Application-defined Or Object-defined Error :: Add Named Range At Run-time
Trying to add a named range at run-time

Here's what I have so far ...

View Replies!   View Related
Application-defined Or Object-defined Error :: Range & Offset
this is the line it gives the error on.

Range("A1").End(xlDown).Offset(1,0).Select

View Replies!   View Related
Range Selection Error (application-defined Or Object-defined)
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):

View Replies!   View Related
Application-defined Or Object-defined Error Using Range
So I'm correcting macro at work and keep running into this error:
"Application-defined or object-defined error"

View Replies!   View Related
Name Last Cell In Defined Range
In several places in my code I need to define ranges and save them in Application.Names. I need to be able to reference the last row and column in that range but I don't know how. Is there a reference within the Range object for the boundaries used to create it?

ie, the B3 in Range("A1:B3")

Not all of the ranges will have content in them and many of them will overlap, so looking for the last non-blank cell won't work.

View Replies!   View Related
Application-defined Or Object-defined Error On An Active Cell
I have a very strange problem in DEBUG mode, because i get this error "Application-defined or object-defined error" when referring to a cell and assigning it a value so it goes to my error handler and i have a Resume Next there. It continues to go through the code whilst continuing to go to the error handler but when i step out of the function it restarts again from the beginning on the called function and then on the second run of my code it seems to WORK!?! So i'm thinking what the hell is going on, it falls over and fails the first time round and works the second time round?
In free-run mode from excel i just get a #VALUE!

View Replies!   View Related
Reference Cell Looks At One Range Then See If Any Exist In Another Range
I have a workbook with two sheets. The first sheet is the data sheet.

Data Worksheet
A B C D colA colA,B colA,B,C colA,B,C,D
104 20 21 CR10600S 104 10420 1042021 1042021DUS
104 20 21 CR10600SX 104 10420 1042021 1042021DUS
104 20 21 CR10603S 104 10420 1042021 1042021DUS
104 20 21 CR10603SX 104 10420 1042021 1042021DUS
104 20 22 CR49605S 104 10420 1042022 1042022HKZ
104 20 22 CR49605SX 104 10420 1042022 1042022HKZ
104 20 23 CR39601P 104 10420 1042023 1042023SPR
The next sheet is where the magic happens. Separate this worksheet into two sections. The reference section (column A and B) and the entry section (column E-G). Column D is a formula that will concatenate column E-G together. In the entry section any number of combinations can be entered. In the reference section I need to take the style number and reference it back to the data sheet. Then take the possible combinations from that style (column E and greater) and see if any of those combinations exist on the other sheet in column D. If so then say added....otherwise say needed.


"....In Pricing Sheet" is where I am looking to put needed or added or something like it.
A B C D E F G
STYLE …IN PRICING SHEET Concat DEPT MCL SCL STYLE
CR10600S Added 1042021 104 20 21
CR10600SX Added 1042022 104 20 22
CR10603S Added
CR10603SX Added
CR49605S Added
CR49605SX Added
CR39601P Needed
Any ideas on how to do this in formulas and without code? I have tried and looked into index, match, dget and lookup. I just don't think I am finding the right combination of formulas.

View Replies!   View Related
Getting A Range Reference From A Cell
i need to copy a range from one workbook ("weekly data") to another ("Yearly data"). The range to copy is called 'weekly data'. The ranges to paste to are called 'week 1' through to 'week 52' (this is the yearly data sheet). On the yearly sheet there is a cell Po1' that week on week will state the week number ie 'week13', 'week14' etc. code to use this data in 'P1' to tell the macro to copy to the appropriate week. I have attached as far as i got (then failed due to lack of experiance).

presuming that both books are open

HTML windows("weekly data").activate
sheets("weekly transfer").select
range("weekly data").copy
windows("yearly data").activate
'only one sheet in workbook
range ' data from p1 .paste

View Replies!   View Related
Cell Address In VBA :: Reference A Range In VBA
I have a variable which contains the column number. How do I then reference a range in VBA using that column number, as opposed to the letter? I know how to convert the number into the letter, but I also know there has to be something more simpler. For instance, a form of the ADDRESS function, but in VBA?

View Replies!   View Related
Reference A Cell In Sheet Range
Im currently located in Sheet1. I want to reference a cell in Sheet2.Range("B2") using a R1C1 reference.

View Replies!   View Related
Fastest Way To Reference A Cell - Range() Or Cells() ?
I have a loop that executes roughly 7.7 million times when my VBA program runs. Neednless to say it take a long time to run - usually a couple days.

The inner-most loop contains a line of code from way back in my early vba programming days when I knew even less than the small amount I know about programming now (and if you can follow that sentence you might be able to understand some of the spaghetti code I write :-) ).

for k = 1 to n

if Worksheets("Personell").Range("D" & Trim(Str(k))) > dtCompleted then ...

Would it be faster to use this syntax (which I just found out about):

if Worksheets("Personell").Cells(k,4) > dtCompleted then ...

It would cut out 2 functions calls, trim() and str(), so it would be faster, right?

I am almost embarassed to post the Range("D" & Trim(Str(k)) line of code because it looks so convoluted now, but that's how I learned to program, just fumbling through until it ran...

So in short, I just wanted to confirm that the cells() syntax run faster before I spend an hour editing and tested.

View Replies!   View Related
How To Turn Range.find Into A Cell Reference
I am writing a function that opens up an external excel document, identifies a string in a certain range and then i also want it to return a cell reference for that string including the number of rows beneath the cell that the string was found in.

View Replies!   View Related
Passing A Value In A Cell To A Named Range Reference
I have a formula which is using named ranges-

=SUMPRODUCT(xxx-30,Ship_30)/SUM(Ship_30)

where Ship_30 name references the following-
='sheet 1'!$C$20:$C$38

The value of 20 is static, the value of 38 is dynamic. Each new time period adds another value (i.e. 39,40,41)

Having to edit each name range reference in the name manager is not acceptable since there is potentially over 30 to edit each month.

I would like to edit a cell which would have contained the value 38 (or C38) with a new value of 39 (or C39) so the range reference would be updated with the new value.

The goal would be to have a couple of rows with the following that could be edited with new values

View Replies!   View Related
Reference Last Cell In Dynamic Named Range
In the attached spreadsheet I am trying to always reference the sum column from Qty in Det Est.

This is because the number of roads changes for each project.

I created a dynamic range name surfacing with the following formula:
=offset(qty!$a$1,0,0,counta(qty!$A$A), counta(qty!$1$1)).

My question is what formula do I use to reference the sum column for each row on the Det Est Sheet.

View Replies!   View Related
Start Function Range Reference Row From Cell Value
Is there a way to start match from a relative position?

Say a match I have in column "A" returns 344. Is it possible to start a new match for column "B" from that spot, instead of having it return a match starting at the top?

View Replies!   View Related
Dynamic Named Range Sort Including Cells Outside Defined Range
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:

=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)

When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....

View Replies!   View Related
VBA - Format Range Font Based On Value In A Reference Cell
I am preparing a model for a client that runs excel 2003. I need to change the format of a range based on the value in a cell (reference cell) that is outside the range to be reformatted.

Reference cell is a named cell with the value calculated with a Match() function, the only possible values are (1, 2, 3).

I need the VBA code to retrieve the reference cell value and apply one of three formats to the range. I believe the code for that part is something like:

Private Sub Format_Font()
Set vrange = Range("d63:r63")
Set refrange = Names("ind_agentfee").RefersTo
Set refrange = Mid(refrange, 2)
On Error Resume Next
If refrange = 1 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
Else
If refrange = 2 Then
With vrange.Font
.Style = "Comma"
.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
Else
.Style = "Percent"
End If
End Sub

I am having problems getting the proper opening statements and declarations correct and also get an "Else without Then" error with all combination's that I am attempting. I am sure that the code is mess now as I started with a recorded macro then modified it multiple times while researching the web.



View Replies!   View Related
How To Update (automatically) The Range Of A Defined Name Range
I am looking for a Macro that will allow me to update the range of a Defined name range such as every time I add a name into the list, the only thing I will be doing is passing the client name and the list of client will be automatically updated and my drop down ListBox will show the new client name, which can be selected by the user.

View Replies!   View Related
Cell Reference :: Cell Range?
I have a formula:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Sheet1!N:N,ROW(Sheet1!N2:N65536) -ROW(Sheet1!N1),0,1)),--(Sheet1!N2:N65536="Dog"))

Calculating the sheet takes forever because of the cell range. Sometime the sheet will have 10 rows other times it will have 10,000 rows, so I put the range N2:N65536. I already have a count of the number of rows in a cell. Can I refer to that cell in the range, for example: N2:N&B1 (where B1 has the number of rows)?

I tried it a ton of ways but can't get it to work.

View Replies!   View Related
Add A Value To A Defined Name Range
I need to add a value to a Defined Name Range

ThisWorkbook.Names ("Test"), RefersTo = 10

How do I add a value to "Test" storing a tracking value

I use to keep this value on a hidden worksheet.

but my co-working messing around change this value.

hopefully using a Named Range would help prevent them from messing with it. I am not allowed to password protect the worksheets.

View Replies!   View Related
Deleting A Defined Range Name?
Say i have a range A1:B3 and i define it as "Fruit", but then later decide i want to call it "food", or even just revert back to cell numbers. How would one go about doing this?

View Replies!   View Related
Range Defined By Inputbox
I have a spreadsheet that has 7 columns starting at B and 12 rows. Column B has numbers from 1 to 12 down the rows. I have an inputbox that asks the user for a number from 1 - 12 that relates to a tax period. What I'd like to do is depending on what number they give have that define the range. If they gave me the number 2 my range would be C11:F11, if the number was 10 the range would be C7:F7. The inputbox returns strTaxPeriod.

View Replies!   View Related
Corners Of Defined Range
How does one select a range with multiple rows and columns, then select the cell in any one corner of that range. What does the "Set" command do?...is it used when an inputbox command is assigning a range instead of a value to a variable

View Replies!   View Related
FillDown Range Defined By Variables
I am trying something out the ordinary, instead of hardcoding the Range, I want o find the last used row of the Range and append, then filldown.
I think my idea is good, but either syntax is off or not going to be allowed to do this.

.Range("F" & lLrwT + 1: "F").FillDown

>>I have posted this on VBAx too.<<

View Replies!   View Related
Allowing User Defined Range
I would like to alter this code so that the user chooses the column where the numbers are. They aren't always in column D....

View Replies!   View Related
Create Loop In A Defined Range
i created list in Excel and i wrote into (a1 cell-income, b1 cell-expence, c1 cell-kredit and d1 cell-debet). i enter a2 cell income (for ex:1000) and b2 cell expence (for ex: 100) and I give condition in VBA that if income greater than expence then VBA writes a2.value-b2.value into d2 cell else if expence greater than income then it writes b2.value-a2.value into c2 cell but i only define this one row (for ex: c2, a2. b2. d2).

i would like to assign it the rows as long as i want
that is i would like to create loop
(for ex: a(i), b(i) and so on

here is

View Replies!   View Related
Application Defined Or Object Defined Error When Creating A Pivot Table
I keep running into this error when I try to create a pivot table.

The debugger highlights the following line:

View Replies!   View Related
Set Bold To Font On Another Worksheet: Application Defined Or Object Defined Error
'Code1
Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new").Activate
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
'Name
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
'city
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)

the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.

View Replies!   View Related
Application-Defined Or Object-Defined Error Setting Validation Message
I have problems with setting Validation input title and message. When in column "C" I type product code, data validation of corresponding cell in column "D" changes to list of diameters allowed for that product. That works fine. But i also want to set validation message to show allowed diameters.I set it in VBA using named ranges.
But for every second line (13, 15, etc on attached file) I get an application-defined or object-defined error. The rest (14, 16 etc) works ok.

View Replies!   View Related
Rotate Range, Keep Defined Names Consistent.
as the title says, i need to rotate a range but to keep all defined names (single cell or range) consistent.

i.e.
lets say i have the following cells filled:

1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
1 2 3 4
now i have a few defined ranges. lets say:
range name, refers to
mycell1row1, A1
mycell90roanything, B1
mysomething, C1
mysomthingrange, A1:F1

etc.

what i need in the end is this:

1 1 1 1 1 1 1
2 2 2 2 2 2 2
3 3 3 3 3 3 3
4 4 4 4 4 4 4
and for defined names:
mycell1row1, A1
mycell90roanything, A2
mysomething, A3
mysomthingrange, A1:A7


View Replies!   View Related
Locate A Max From Range Defined In Other Sheet In Workbook
in my workbook i have 3 sheets named "time" "final" "push". in the sheet thats named "final" ihave in cells B2 and C2 2 values that correspond to a type interval (lets say 75-378) in the sheet "time" each row represents 1 sec, starting at one.

what i would like to do is locate the interval defined by the 2 values on "final" sheet
and find the min max and value in column S of the "time" sheet.

i.e if B2 and c2 said 78 and 378 it would go on "time" sheet select the range of S78-S378 and find the mean, the max and the average and report them in sheet "final" in cells E2 F2 G2 respectively What would be optimal is that there was a way to perhaps loop so that it can do the same thing with the range defined by the values in B3 and C3.

View Replies!   View Related
Counting The No Of Cella Given In A Range Defined In Two Cells
I have values in cells which is the address of a different cell. So consecutive columns define the range.e.g.

View Replies!   View Related
Workbook With A Globally Defined Named Range
I have a workbook with a globally defined named range "MyArray" which refers to a constant array = {"Item1","Item2","Item3"}. My general module contains the following

View Replies!   View Related
Summing Data In A User Defined Range
I have a spreadsheet which links to an external source, runs a sql msquery and retrieves data based on dates selected by the user from two drop down lists. From Date and Date To.This works fine. However I also need to total any fixed data which resides in the same spreadsheet based upon the same dates selected. The end user selects 2 dates , say 15/04/2007 (this relates to a week number,week15)and 28/04/2007(week 17)
A column of data lists the week numbers (in cells A3 - A22) and next to this their respective production quantities in cells B3-B22.

I now have a problem in totaling the production quantities in the worksheet as my user is not just selecting the week numbers 15 and 17 but 15,16 and 17.

qty
week 15100
week 16123
week 1789

How do I sum from 15 to week 17 inclusive or any other range selected?

View Replies!   View Related
Find Blank Cells In Defined Range Name
I am using Excel 2003. I have a range that I have defined in my sheet called "March". Within this sheet, I have a range name called "ACCOUNTRANGE". Basically it comprises the column B10:B200. Within this range, I may be inserting rows so range is dynamic. Well one of my problems is finding BLANK Entries in my range. How do I loop through the range to find BLANK entries and prompt the user that a BLANK entry was found, then it stops the loop and if none is found, nothing happens and continues on.

View Replies!   View Related
Sort Macro: Application-defined Or Object-defined Error
I get a run time error 1004 when I run this sort.

ActiveSheet. Range("A1:AC277").Sort Key1:=ActiveSheet.Range("G2"), Order1:=xlAscending, Key2:= _
ActiveSheet.Range("E2"), Order2:=xlAscending, header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal


View Replies!   View Related
File System Object: User-Defined Type Not Defined
I had a working subroutine which I moved to another workbook (rather than linking to it and having both workbooks open at the same time).

Now I get "user-defined type not defined" when it runs. Here is the beginning of the
Global oApp As Object

Sub UpdateForecasts()
Call Update1
Call Update2
End Sub

Sub Update1()
Dim dPath As String
Dim dDest As String
Dim dCondition As String
Dim dName As String
Dim FSO As New FileSystemObject


View Replies!   View Related
Application-defined Or Object-defined Error :: On Open Everytime
I had to change sheet one but everytime I open this workbook, it keep saying "Application-defined or object-defined error"

but I cannot find what is causing the bug. I have checked the name of sheet and it seem correct for me in the codes.

View Replies!   View Related
Application-Defined Or Object-Defined Error :: Subtracting Hours
I have a sub that takes the value of a cell and get the hour by using

mhours = Hour(KRT)

then using DateAdd I need to subtract the mhours from etd

and Im getting this error

Application-Defined or Object-Defined Error

Worksheets(b).Range("G" & q).value = DateAdd("h", mhours, etd)

Note: KRT and ETD are dim'd as Date
Note2: DateAdd returns the same error wether mhours is postive or negitive

View Replies!   View Related
Application-defined Or Object-defined Error On Formula Entry
I'm trying to enter a series of formulas into a worksheet using vba. However, this code is giving me Run time error '1004', along with the description in the title. This is the first formula (they're all relatively similar).

View Replies!   View Related
AdvancedFilter: Application-defined Or Object-defined Error 1004
Im trying to copy all the unique values from one column to another and the easiest way seems to be:

Set SourceRange = Worksheets(page2). Range("C1:C17365")
SourceRange. AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Worksheets(sida3).Range("B"), Unique:=True

However, I get the error- message: Run-time error '1004': Application-defined or object-defined error

View Replies!   View Related
User-defined Type Not Defined - Command Bar Control
I am trying to capture with Worksheet Change a command bar action like paste but I am getting an error :

“User-Defined type not defined”

Do I need a library of sort in References ??


Public Sub Right_Click()

Dim oControl As CommandBarControl

For Each oControl In CommandBars("Cell").Controls
Debug.Print oControl.Caption
If oControl.Caption = "&Paste" Then
oControl.OnAction = "MyPaste"
End If
Next oControl

End Sub


View Replies!   View Related
1004 Application-defined Or Object-defined Error
When opening a blank excel workbook I receive the error "1004 Application-defined or object-defined error". I press OK. And the message appears 2 more times before I am able to work in Excel. Doesn't matter what excel file I am opening, does the same thing. I do not know how to lookup programming code for any macros I am running. I would like to get rid of this error! It is a nusance.


View Replies!   View Related
Application Defined Or Object Defined Error 1004
I get runtime error 1004 "Application Defined or Object Defined Error" when I try to do the following:

View Replies!   View Related
Application-defined Or Object-defined Error On One Machine But Not The Other
I'm trying to figure out why I can run a bit of code on my machine but on another machine I get an: Application-defined or object-defined error. The code is in an excel file on a network drive and it pulls up a form, once the form is submitted it runs some code that looks like so

View Replies!   View Related
Application Defined Or Object Defined Error In Code
I am getting an 'application defined or object defined' error at the code coloured red. I'm trying to sort a list of names starting at c5


'Update Names on Sheets
If Sheets("Daily Visits May").Range("e1") = "y" Then Goto 10
If Sheets("Daily Visits May").Range("d1") > 38837 Then Else Goto 10
Sheets("Daily Visits Apr").Select
Range("b5:c660").Select
Selection.Copy
Sheets("Daily Visits May").Select
Range("b5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Sort Key1:=Range("C5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

View Replies!   View Related
Application-defined Or Object-defined Error 1004
I get "Application-defined or Object-defined error" , run-time error 1004.

Option Explicit
Sub Initialize()
Dim rList As String
Dim CountRows, CurrCol As Integer
rList = "myTemplate"
Range("A2").Select
CountRows = Range(Selection, Selection.End(xlDown)).Rows.Count

CurrCol = 7
With Range("G : G" & CountRows + 1)
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
.ErrorTitle = "JC Message"
.InputMessage = "Select requested template"
.ErrorMessage = "You must enter a template from a list only"
.IgnoreBlank = False
End With
End With
End Sub

View Replies!   View Related
Choosing Data From Defined Work Source Range
My problem is in this defined range in "C30,D30,E30, F30 & G30" there is a possibility of more than one entry. If that is the case the entry would be in "C31,D31,E31, F31 & G31" If this is the case I need to pick the later which is "E31, F31 & G31" not

"C30,D30,E30, F30 & G30". How can I do that?

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved