Sum Range From Active Cell To Last Used
I Have searched existing threads but cannot find the answer to my query,
As part of a longer macro, I want to sum all cells in a column which contains data. Each day the number of cells that contain data will vary.
I have tired specifying all the cells in the column which contain data as a range, by selecting the top cell and using the
Range(Selection, Selection, Selection.End(x1Down))
But I cannot get a SUM Formula of this range. If someone could show me the correct syntax to get a sum from a range like this I would be very grateful.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Expand Active Cell To A Given Range
When i click commandbutton1: Copy Active cell (columnB), next cell (ColumnC), next cell (ColumnI), next cell (ColumnO) all of which exist in the same row. I will then paste the values into another sheet in the next available row which i think i have mastered. the cells will be pasted next to each other i.e. instead of column B,C,I,O they will be pasted as column A,B,C,D. My dilema is i am not sure if you can use multiple cell offsets to perform this or if it is better to use a loop.
View Replies!
View Related
Range In Same Row As Active Cell
I'm looking for a piece of code, which would activate a certain Range i.e. the start of which would be in column A and the End in Column G. My problem is that the activated range of cells shuld be exactly in the same row as the currently active cell i.e. active cell B3 -> activated range A3:G3 .
View Replies!
View Related
Expand Active Cell To New Range
I have some working code that makes a number of changes to an excel report using VBA. Each time I execute the code I am taken to a different point in the excel report (depending on user inputs). What I now need to do is to change the cell selection from a single cell to three cells. For example, my code may result in cell B100 being the active cell. What I now want to do is change the selection from B100 to the range B100:B103. I would have thought I could write something simple like: Sheets("Group").Range("ActiveCell:Offset(0, 2)").Select I have tried every variation of the above but I can't seem to make it run. Where am I going wrong?
View Replies!
View Related
Copy Active Cell Down Adjacent Range To Right
Split from: Clear Range To Right Of Active Cell Down. I was working through this the first step was to clear the contents of the cells. After they are cleared, I have another sub that then goes and retreives a unquie number and places it back at the top of the column that I just cleared. Would this same code that cleared the cells contents work to copy the contents of the active cell down that same column, until the cell to its right is blank? -R-
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Merge Range Based On Active Cell Row
I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this? If Sigma = 0 Then Selection.EntireRow.Insert ' New row for new entries ActiveCell.Value = "NONE" ActiveCell.Offset(1, 0).Select End If
View Replies!
View Related
Range X Rows & X Columns From Active Cell
Im writing a macro and have a cell activated (using ActiveCell). If I want to select this cell (the activated one - i.e., k3) along with the 2 cells next to it (i.e., l3, and m3), how would i go about doing that? I'm wanting to shift 3 cells downward and I know how to shift them down, but dont know how to select the cells i want.
View Replies!
View Related
Range Copy: Differ Depending On The Active Cell That's Selected
The rows will differ depending on the Active Cell that's selected and I don't know how to specify this. The range I want to copy is from Column B to DA on the worksheet ("Staff") which I want to paste to another worksheet ("Leavers"). This is as far as I got 'FindRemove = lstRemove.Value 'If FindRemove = "" Then End ' Goes to the start of the Data column 'Sheets("Staff").Select 'Range("B4").Select ' Tests current cell against FindRemove 'Do 'If ActiveCell.Value = FindRemove Then 'Call CopyPerson 'Exit Sub 'Else: ActiveCell.Offset(1, 0).Select 'End If 'Loop Until ActiveCell.Value = "" 'End Sub
View Replies!
View Related
Sum A Range Of Cell
I would like to Sum a range of cells which has specific text (Text will be given specific value as classified below) in them. Here is my classification: I need column 1 to 7 to be added & the final result should be displayed in 8th column (SL) HSL = 0.50 SL = 1 HCL = 0.50 1 2 3 4 5 6 7 SL CL HSL SL 1 HCL HSL SL 1 3 0.50
View Replies!
View Related
Sum Cell Range On Specified Worksheet
I have the following line of VBA code that sums a range of cells on the active worksheet. I need to change that to sum on a specified worksheet. Is there a way to do that? I thought it would be simple, but have had no luck... Actually using variables for the cells, but simplified here for example's sake. If Application.WorksheetFunction.Sum(Range(Cells(2, 2), Cells(123, 2))) = 0
View Replies!
View Related
Control The Sum Range With A Cell
I would like to define the range of cells in the sum with a number in another cell. Say I want to sum the past 10 years, I would like the sum definition to be controlled by another cell that includes the number 10. If I want B1 to be the sum of A1:A10, B2 to be the sum of A2:A11, etc., I would like to be able to control the range of the sum with say, C1, which in this case would contain the number 10. This would allow me to easily change the range of data I am summing: if I changed C2 to 15, B2 would give me the sum of A1:A15 for example.
View Replies!
View Related
Sum A Range Based On A Cell Value
I am looking fo a simple way to carry out the following.. My Spreadsheet has a range of data from column D to O I have a Total Column Q I have a Cell, eg A2, where I enter a value from 1 to 12 Depending upon the value entered into A2 will dictate the range of cells to sum, ie A2 = 4 - Column Q would Total Range D?? to G?? A2 = 12 - Column Q would Total Range D?? to 0?? etc.
View Replies!
View Related
SUM Range Based On Cell Value
I'm trying to create a = SUM( RANGE) forumla, but, I need the first cell reference to represent the value of a different cell... something like... =SUM("A1"1:H1) Where A1 holds the value of a cell number. ie, cell A1 may have the value 'e', so I want the result to be the sum of range e1:h1.
View Replies!
View Related
Find Last Cell In The Range And Sum It Via Vba
complete the following partial code. I am trying to determine the last cell for Non-ILEC piece and then want to insert one row below the last Non-ILEC cell and insert "Total BS Non-ILEC" and sum "H" Range just like the way its done in the partial code. The partial code I have posted is working perfectly fine with regard to ILEC piece. I need to further add the Non-ILEC piece in the partial code to accomplish my task ....
View Replies!
View Related
Open Ended Cell For Sum Range
I have numerical data in column A rows 1:14. I'm adding it up in row 15. Sum=(A1:A14) Now I've been asked to insert more rows with data above row A14. What can I put in my Sum formula in place of A14 so when I insert a new row, any numerical data above it in column A automatically adds up? Sum=(A1:?) I don't want to have to go and change it to sum=(A1:A17) if I insert 3 rows.... I have a huge file to create with sections like this. User may have to insert new rows. They need to all add up.
View Replies!
View Related
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 Replies!
View Related
SUM X Column Range Based On Criteria In Cell
Im trying to do a sumif reading the name in column N (either "Business" or "Procurement"), and suming all values in the range AW:AY. this is what I have come up with.... sumif(N10:N49, "Business", AW10:AY49) but it returns incorrect values and not the whole range???? Any ideas? Auto Merged Post Until 24 Hrs Passes;here is an example of what im trying to do... H2 and H3 are my attempted formulas
View Replies!
View Related
Dificult Sum + Sum.if Date Range Criteria
0 x -2 x -1 x 3 x -4 x 1 max 5 x 8 x 9 x 1 x I need to sum the 1st column from the bottom, to the same line of the max on the right column. First thing i remembered =sum(xx:max(xx:xx)) but it doesn't work, i think because sum() does't allow any operations in its arguments. Second was to store the line of the max and try to join the column and then sum them but =lin(max(xx:xx)) doesn't work either Third i tried =sum.if() but i don't have much experience with this function. Also i'm trying to perfect a SUM.IF with a DATE RANGE CRITERIA instead of: =SUM.IF(I4:I25,">=2009/1/1",R4:R25)-SUM.IF(I4:I25,">=2010/1/1",R4:R25) why can't i use? =sum.if(xx:xx,year(xx:xx)=2009,yx:yx) or =sum.if(xx:xx,year(xx:xx)="2009",yx:yx)
View Replies!
View Related
Sort Range On Non Active Sheet
I'm trying to sort data on sheet1 and sheet2. I'm running all macros from sheet one and I need it to stay active all of the time. I'm using the following code to sort sheet2. When I sort the column on sheet2 with the code, I can not get back to sheet one without an error. Worksheets("Sheet2").Activate Columns("a:a").Select Selection.sort Key1:= Range("a1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal I've tried Worksheets("Sheet1").Activate Columns("a:a").Select..............
View Replies!
View Related
Named Range That Refers To Active Sheet
is there a way to have the same named range cover a range of cells regardless of which sheet you are on? They seem to want to default to the page you are on. I want to make some VBA subroutines that will go over each sheet, and perform the same functions on each sheet. All sheets have the same format, but different data. I guess worst case scenario I can just break down and use multiple workbooks, but this seems a little less desirable. Should I be dividing this into seperate posts?
View Replies!
View Related
Apply Auto Filter To Active Range
I find it to be a little irritating that I have to remove the autofilter, then reapply it whenever I start entering data into a new column. I want a macro that I can assign to a button so all I need to do is hightlight the range of cells I want to apply the autofilter to, and click the button.
View Replies!
View Related
Add SUM To End Of Named Range To Sum Named Range
I have a formula copied into a named cell “Dest1”. This cell is based on value in cell “X4”. Set Dest1 = Range("C1").Offset(Range("X4").Value + 1) A “Do” loop copies the formula down, to a cell which I name “Dest2”. Set Dest2 = ActiveCell How do I sum from “Dest1” to “Dest2” with result in the next row down from “Dest2”?
View Replies!
View Related
Store Active Autofilter Criteria In Array Or Range
I want to perform some operations (basically a secondary filter) based on the values which are currently filtered within a single filter column. .Autofilter.Filters(n).Criteria1 and .Criteria2 are great, but what if there are more than 2??? i.e. I have a column containing values L01 to L20. My column is filtered on L05, L06 and L07 (or some other combination). I want to extract the values and L05, L06 and L07 and do what I will with them.
View Replies!
View Related
Copy Range Of Cells To Active Row In Second Sheet
Sheet 1 has data entered into it, it is then printed out as a jobsheet, saved and the data cleared. There are certain fields on this sheet that are eventually manually replicated onto sheet 2. The row in which they must go on sheet 2 will always be the 'activerow' on that sheet from a previous operation. It would make life so much easier and save lots of time if I could incorporate copying cells C10,C12,K8,K12,M2,C27 and C29 from sheet 1 to respective cells H,I,J,M,N,R,S of the active row on sheet 2 before I carry out the clear data process.
View Replies!
View Related
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null. I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it. I've attached the spreadsheet so you get a better idea of the problem that i encountered.
View Replies!
View Related
SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula: {=SUMIFS(range to sum, range to evaluate, evaluation criterion)} The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999. So my formula looks like this: {=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)} This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
View Replies!
View Related
Find Dates Between Monthly Range And Sum Another Cells Results That Are In A Range
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View Replies!
View Related
Identify Active Cell And Use The Column To Add Formula To Another Cell
I have a range of unlocked cells (B5:S10) that users enter data in. This sum of this data is then charted. The formula (sum) in a cell equals zero even when there is no data entered by the user. This zero is then charted. I need to be able to plot the zeros if the user enters zeros but not plot the zero if the cells are blank. What I was attempting to do is to use the worksheet change event to add the formulas to a cell so that the chart does not plot the value until something was added. In my change event I need to know that a cell in the range (B5:S10) was changed and that if it was D7 (for example) that I need a formula enterd in D11 [=SUM(D5:D10)]. If it was I5 then the formula would have to go in I11 [=SUM(I5:I10)].
View Replies!
View Related
Return Maximum Of Active Cell & Arbitrary Value To Cell
I am looking for VBA that will add the value of the current active cell on the sheet to the value in cell F12. The maximum value of F12 cannot exceed 1000. So if the value in F12 = 950 and 100 is the value in the active cell the maximum value in F12 should show 1000, not 1050. It should do this on the click of a button.
View Replies!
View Related
SUM Of Values In 1 Range Based On Specific Criteria In Another Range
I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet). Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column. This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.
View Replies!
View Related
Sumif(range,30,sum Range) Not Summing Values That Equal 0
I have a simple sumif formula that says =SUMIF(W61:W112,"<30",J61:J112). In column W, there are values ranging from 0 to 5000. If the formula is written like it is above, it excludes summing values from column J when the cell in column W equals 0. Why is this? I can just add another function that says sumif "=0", but I don't think I should have to.
View Replies!
View Related
Go To First Cell In List That Matches Contents Of Active Cell
I am just learning to use VBA and this may be the most simple task ever, but I can't figure it out. I've searched for all of the keywords I can think of, but can't find a solution... I have a list of names in a sheet. Other columns in this sheet contain data like amount charged, amount paid, etc. This sheet must be manually updated (because the other program won't export the information I need) periodically to ensure proper billing/payment application in the original software (all transactions are handled by other people that I don't trust). I sort the list so that the all names that are the same (ie John Doe) are together. A short example list looks like this: Jeremy Apple John Doe John Doe John Doe Jimmy Kravitz Jimmy Kravitz In updating my sheet, I set up a macro that will input todays date in one of the columns for all occurences of that name (so, every row that contains John Doe in column B, column V will have todays date in it). Currently, in order for my macro to work properly, I have to manually make the activecell the first occurrence of 'John Doe'. When I'm ready to update 'Jimmy Kravitz', I have to select the first occurrence of 'Jimmy Kravitz' and so on. Here's my question - Is there a way to use a VBA macro to find the first occurrence of 'John Doe' (and automatically 'know' which name I am updating)? Basically, I need a macro that will take the information that is in the cell in column B in the active row, find the first row that has that same name, and make that cell (column B) the active cell...
View Replies!
View Related
Add Active Cell Data To Cell By Clicking
I want to automatically put information from several cells into one cell, when these cells are clicked. Let's say that A1:J8 is the range were a cell can be clicked and if a cell is clicked in that range the value of these cells should be put in cell A9. If possible I want to avoid private subs in a worksheet. I preffer to have a macro running, so I can decide when to stop this routine.
View Replies!
View Related
Sum Range Using 1 Column Of Named Range As Criteria
how to use SUM Formula a column from within a Named Ranges or Dynamic Named Range? For example, if the range name "MyData" refers to the address: A1:G10, how could I sum all the numbers in column G of that range where column A meets certain criteria. Eg., Column A holds fruit names: Apple Orange Banana Apple and column G holds quantities of the particular fruit. I'd like to sum column G (quantity) for only those quantities that match "Apple" in column A.
View Replies!
View Related
Sum Range With Part Text In Another Range
I have to group some data from a pdf format. I import them into a excel worksheet, but the problem is with selecting them, because the data are really messed up. I tried selecting them with sumproduct with criteria, but the problem is that the formula wants the criteria to be a text that is in only one cell, whereas in my case there I would like the formula to select for example the cell that has the text "Visa34" as well as the cell with "Visa12,FIB3" (i.e. all the cells with "Visa" even thought there might be other words or letters in the same cell...).
View Replies!
View Related
|
TRACKER
