Sum A Dynamic Range Within A For Loop?

Sep 15, 2014

I am working with loans, and I'm trying to program a routine that adds every installment a client owes, so I can know the total amount owed, depending on how many unpaid installments she has. Let say for a client I have every installment in column A (rows 1 to 5), and in column B, I want to add them all. The output should be

Cell B1: "sum(A1:A5)"
Cell B2: "sum(A2:A5)"
Cell B3: "sum(A3:A5)"... and so forth

Since I have many loans, I need a dinamic routine to that sums every installment, where amount of installments vary from client to client. My proposal is (adding from the last installment to the first one):

VB:
months = 4 'an example
For j = 0 To months - 1
initial_row = ActiveCell.Row 'because I run this several times. In this example I am in row 18[code]....

For some reason this is not working, and what I see in Excel after running it is: SUM('AJ18':'AJ18'), which doesn't work.

View 1 Replies


ADVERTISEMENT

Change Dynamic Range While In Loop?

Sep 29, 2012

I am wanting vba to loop though a range basically till the last row of the column however within the loop the row gets inserted and the lr which is pre defined does not change. I want to be able to change my lr within the loop, maybe reset it or something...or may be alternative..

I tried using a method where it will loop through a each cell in col however that takes too much times....because it is going through each column...

Code:
lr = Cells(Rows.Count,
Code:
iCol).End(xlUp).Row
For y = 1 To lr
Cells(y, iCol).Select
If ActiveCell.value "" Then

[Code]....

View 4 Replies View Related

How To Name A Dynamic Range & Make A Validation List (of 2 Dynamic Ranges)

Dec 22, 2009

I have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.

Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?

i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both

View 9 Replies View Related

How To Loop Through Dynamic Ranges Using Columns Rather Than Rows

Jun 23, 2014

I have a list of several hundred columns, beginning with column "G:G", with varying numbers of rows of data in column - each row dipicting a monthly data point. I'd like to average the numbers in each column (need to average over the appropraite time-frame) and compare that average with the corresponding average (same time-frame) for benchmark (column"F:F"). The problem is I don't have the same number of data points in each column; some have data points for every month for the past 33 years, and some just a few years; almost all have differing beginning and ending dates as well.

[Code] ....

View 9 Replies View Related

Rename Sheets By Using Dynamic Loop For Rows?

Jan 30, 2014

In column C I got some data like this:

Number of .csv
01
02
03
04

Number of .csv
05
06
07
08

Notice that there is an empty cell in between.The data starts at C12 up to C21. The data is in Sheet2.There are also 8 more sheets( Sheet3 to Sheet10). I want to rename each sheet, starting from Sheet3 according to each cell. For example the Sheet3 to be renamed to 01, Sheet4 to 02.

What I can do is something like this:

[Code] .....

And repeat this code for every block of data I got by changing everytime the i and the a. But this method is not so optimized because there are cases that the number of rows for each block is not the same and I have to change everytime the i counter. Is there any way to do 1 loop for all the sheets using maybe Worksheets.Count and another dynamic loop for the rows ? The data always start every 6 rows eg( C12, C18 etc). Also I was thinking to define an integer representing the number of rows for the loop...

View 6 Replies View Related

Loop Through Of Sheets And Execute Calculations Based On Dynamic Sheet References

Jun 30, 2014

Is it possible to loop through a list of sheets and execute some calculations that have sheet references from a different list of worksheets? For example, you have a list (list 1) of your worksheets, which will be the destinations of the calculations, and you have a second list (list 2) of worksheets that the calculations are based on. So, lets say there are sheet1 and sheet2 in list 1, and sheetA and sheetB in list 2. The calculations based on sheetA would appear in sheet1, and calculations based on sheetB would appear in sheet2. I thought the code would look something like this:

Code:
Sub LoopthroughWorksheets()
Dim sheet_name As Range
Dim sheet_name2 As Range
Set sheet_name2 = Sheets("WS").Range("F:F")

[Code] ......

I'm getting a "Run-time error '1004: Application-defined or object defined error" at this line:

Code:
.Range("K1") = .Range("sheet_name2.Value!A14").Value

View 2 Replies View Related

Loop Through Rows To Calculate Median For Several Dynamic Ranges With Numerical Data

Jul 6, 2006

loop that can calculate the median of dynamic ranges. I need to run through all columns and for each column find the the ranges (there are more ranges and they are seperated with a blank row) with numerical data and then calculate the median and thereafter continue down to find the next range with numerical data in that row and so on.

View 4 Replies View Related

Dynamic Named Range Sort Including Cells Outside Defined Range

Apr 3, 2008

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 9 Replies View Related

Creating Named Range Taking Avg To Date Of Dynamic Range

Jul 15, 2014

I have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.

View 1 Replies View Related

Dynamic Range For Pivot Table :: Error : Range Is Invalid

Oct 28, 2009

I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:

=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))

I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.

View 3 Replies View Related

Convert Dynamic Range To Static Named Range

Sep 13, 2007

My searches have not produced anything that I could apply to this situation.

I'm trying to write VBA that would:

1. Search a Workbook for Dynamic Ranges.

2. When a Dynamic Range is found the code would:

A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"

3. Save Changes.

4. Close File.

My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.

View 9 Replies View Related

Create Macro That Can Create Dynamic Copy / Paste Loop?

Feb 12, 2014

I need to create a macro that can create a dynamic copy/paste loop. So far what I have is horribly inefficient. Each row in colmn A(minus the header) has a unique number in it. For each unique number, I need to paste it based on the number of column headers in row 1(minus column A). So, if there are 20 column headers, I need to copy cell A2 and paste it 19 times in another sheet. Then, I need to move to the next number in column A and do the same thing. Here's what I have:

[Code] .........

You can see that this is not dynamic. If I add another row to my table and rerun the macro, it will not catch it. I've attached a sample file to show you the big picture of what I'm trying to do. The data that I have is in Sheet1, and I'm trying to get it into the format in Sheet3. Rows/columns will be periodically added to the table in Sheet1, so the macro needs to be dynamic to catch that. The data in Sheet3 will always remain, and the macro will add the updated data below the old data in Sheet3.

FC_Macro_Sample.xlsm

View 8 Replies View Related

Dynamic Range 2003: Run Calculations On This Range

Nov 5, 2009

I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.

View 4 Replies View Related

Dynamic Range Used Named Cell Range

Jul 7, 2014

Line of code that will Select a Named Range in this case I have Named a CELL "DataSummary" Need to use that named range by selecting 30 columns and 54 rows.

Range("DataSummary),(??,??) doesn't work.

View 1 Replies View Related

Set Range Variable To Growing Dynamic Range

Mar 6, 2008

I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:

Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))

Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.

View 3 Replies View Related

Dynamic Name Range

Jun 19, 2009

I have this macro named ABC to bring the name ranges to another worksheet. The name ranges are already saved in the workbook with the below name range. I used "***" so that this formular can go to the last cell that has "***" on A column and it worked untill someone typed "(xxx)" and the range now stops right before the "(xxx)"

=OFFSET(Storage_Team2!$A$1,0,0,MATCH("***",Storage_Team2!$A:$A,-1),37)

What would be the best to create the range according to the specific words in a column?

View 9 Replies View Related

Dynamic Range Within VBA

Jul 9, 2006

i am coloring cells:

Sheets("MySheet").Range("a1:f200").Interior.ColorIndex = 5

i want a dynamic range, so something that would look like this as an excel function:

=OFFSET('MySheet'!$A$1,0,0, COUNTA('MySheet'!$A:$A),6)

i want the equivalent of this programmed (hard coded) into vba

i don't want to reference to a defined name with vba, because the user can delete that.

View 9 Replies View Related

Sum Dynamic Range

Jul 15, 2007

I wonder if there is a way to Sum the total of a Dynamic Range so that the summary will be presented in the upper Cell of the summed column?

(please see the attached picture).

The reason is to anable FREEZING of the first row (display the totals all the time even when scrolling down) as the list will get very long after a while.

View 9 Replies View Related

SUM Dynamic Range ...

Jun 24, 2008

I am looking to sum a dynamic range and can't quite figure it out. The data expands down rows every week, and I want the sum formula (the final row) at the bottom to include the newly updated rows. I can't dynamically name the numbers to be summed because the dynamic named range will include the sum number and create a circular reference. For example:

5
4
3
2
14

the bottom row (14) is the sum. Next week, my numbers are

5
4
3
2
5
19

I have a macro that will automatically put the sum formula in the correct row and column, but can't quite figure out the formula.

View 7 Replies View Related

For Next Loop Using Used Range

Jun 21, 2012

Code:

For lead = 1 To 20
CurrentFile = ActiveCell
Workbooks.Open Filename:=CurrentFile
ActiveWindow.Caption = "Graboid"

I have a file I use to grab information off a website I subscribe to.

I get daily emails containing a list, then I use that list to extract additional information for each item in the list.

View 8 Replies View Related

How To Loop Through Range

Jan 12, 2013

I am trying to do Loop through Range I have workbook and it has two sheet (sheet1 and sheet2) sheets ("Sheet1").range(A1:K30") hold the Doc_ID (E.g. 78002)

What I want to do........if Range(A1:K30) = Inputbox("Enter Your Doc_Id Number") Then Copy that Cell Only and paste it to in Sheets("Sheet2") -Column A

View 6 Replies View Related

For Each Loop In A Range

Jan 12, 2014

I have a range of letter values. The values vary between A, a, and B. I have a "For Each" loop that doesn't do what I'd like. The way it's written, I'd think it'd delete every column that doesn't contain the letter "A".

Code:
For Each k In rngMyRange
If k.Value "A" Then
k.EntireColumn.Delete
End If
Next k

It seems to miss entire columns. Do I need to initialize k?

View 8 Replies View Related

Sum Of Range + Loop

Nov 2, 2007

I have percentages in each row. Let's say from A4 to Z4. Each cell can be 20% at maximum In Visual Basic I want to write a macro that sums the cells up. So when the sum is less than 100% I want to distribute the remainder among the cells that are below 20%. I want to do this until the total is 100%. So the new percentages have to be entered in the appropriate cells.

How can I do this? I need some sort of loop but I can't get anything to work. With a 'for each cell' statement I always get Value#.

View 9 Replies View Related

Sumif For Dynamic Range

Nov 1, 2012

I am currently recorded a macros for a sumif formula. But since the number of rows will not be same every time.

I want the code for a dynamic range.

VB:
Range("E7").Select
ActiveCell.FormulaR1C1 = _
"=SUMIF('V1 Pivot'!R5C2:R32C2,'Task Level Tab'!R7C2:R30C2,'V1 Pivot'!R5C3:R32C3)"
Range("E7").Select

[Code] ....

View 9 Replies View Related

Formatting For Dynamic Range

Dec 21, 2012

I need the code for formatting to be edited, so that it work dynamically for any range

VB:
Range("B16:L16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)

[Code] .....

Here the columns "B" and "L" shall be constant, but the number 16 may vary depending upon the number of lines in the sheet.

View 1 Replies View Related

COUNTIF With Dynamic Range?

Feb 15, 2013

I have column A fill with dates and column B with customer code. I'm looking to count the number of time the customer code (column B) is repeating but ONLY in previous year. Meaning that the count range must be adapt for each customer code. Will a countifs is able to work with this request?

View 6 Replies View Related

Name A Dynamic Range With 2 Columns

Jun 28, 2014

I have data in 2 columns on which I need to do vlookup. This range for vlookup increases. I want to name this dynamic range. How do I do it?

View 1 Replies View Related

Dynamic Range With 2 Columns

Nov 11, 2009

I'm trying to name a dynamic range for A2:B10000.... I know how to use the OFFSET function but was wondering to do it using the INDEX function

Both column will always have the same number of used rows.

Another question:

If I have dates in Column A, and values in Column B....what formula do I need to get me the last value (last row,most recent Date)

View 2 Replies View Related

Referencing A Dynamic Range

Feb 16, 2010

1. I have a worksheet with subtotal and total row above all columns

I currently use SUBTOTAL(9, A6:A6000) and SUM(A6:A6000)

however the no of rows is dynamic based on imported data so I want the range end (i.e. A6000) to also be dynamic i.e. refer to the last cell with data in the column.

Something like SUBTOTAL(9, A6:LastCell in A)

2. I also use SUMPRODUCT to do calculations

e.g. SUMPRODUCT (($B$6:$B$6000 = "ABC") * ($A$6:$A$6000))

again how can I make the sumproduct dynamically use all rows to the last active row.

3. I have tried just setting the second part of the range reference to a very large number but am worried that this slows down the calculation. Does it?

e.g. SUMPRODUCT(($B$6:$B$100000 = "ABC") * ($A$6:$A$100000))

View 9 Replies View Related

Vlookup On Dynamic Range?

Aug 20, 2014

I'm trying to find a way to perform Vlookups against a dynamic range of data, where the number of rows/values in Col B is always subject to change.

The way that the workbook is structured is as follows:

Sheet1: Except for Cols A & B, this is a blank sheet. This is the sheet in which the vlookup values will need to fall into based on the number found in its' Column B

Sheet2: The sheet that contains the data that will be passed into Sheet1 via vlookup, and all of its Columns contain data.

So, basically, Sheet1 is a shell that needs to be filled with data copied over from Sheet2 based on vlookups against Col B.

The vlookups will need to be shifted back 1 column.

And examples of how the Vlookups need to work is:The value in Sheet2 [Col E], needs to get passed into Sheet1 [Col D]Along those lines:

The value in Sheet2 [Col F], needs to get passed into Sheet1 [Col E]

The value in Sheet2 [Col G], needs to get passed into Sheet1 [Col F]so on and so forth

So basically, all of the values passed into Sheet1 from Sheet2 need to be shifted back by 1 column until we reach the last Col (Col M in this file).

Where

The value in Sheet2 [Col M], needs to get passed into Sheet1 [Col L]

The number of columns will remain fixed, but again the number rows will vary week to week in both of the two sheets, so I would need to have a way of creating a Vlookup through VBA that accounts for the dynamic range in Col B.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved