Loop Range Of Cells. Top To Bottom, Left To Right
Nov 7, 2006
I have a spreadsheet which is going to be populated everyday from figures from another spreadsheet using autofilters and the very useful SubTotal worksheet function. I'll attach a copy of the work in progress, also there will be a macro in here which Everytime the user presses update, it copies a range of cells and formatting and pastes it to the next available cell on the right. I've also drawn the movement of the cells i'd like The reason being, is that its going to give day to day, up to date figures from a datasheet.
The problem i have is that when i come to populate the grid which has been copied, i need a calculations to populate each cell in the Less<1week column, then when it gets to the bottom, move Back to the top, and accross to the right 1, and then populate down again, and so on until the end of the grid in question, i dont think i can use a for/next loop becuase it will be a different column address everytime. I dont really like loops, but i feel this needs it.
View 6 Replies
ADVERTISEMENT
Oct 27, 2008
I am trying to find a code that will simply find the last cell in Column A of a named Range (titled: ConditionlFormatArea)?
Also IF I could get a line(s) of code that would just find the last cell in Column A of the Used Area.
I ask for both because I am not yet certain which line of code will work best for me.
When I have that cell located I need to Remove the Conditional Formatting. I think I can get the code for that part.
View 9 Replies
View Related
Aug 24, 2006
Is there any easy way to identify the bottom left cell in a named range?
View 6 Replies
View Related
Sep 30, 2013
I tend to use a lot of For Each loops when I want to go through a range looking for something but this time I'm not so sure it will work.
I have 9 databases each containing over 400 rows with different pieces of client information, these databases are shared between around 40 people (I've also used the 'Allow users to edit ranges' utility to restrict access). What this means however is that the users cannot delete an entire row, instead they can only 'clear contents' on individual cells. This is fine until I try to generate statistics from the databases using autofilters which stop at the first blank row.
So I need to write a backwards for each statement that starts at the bottom and deletes all the blank rows on the way up to the top. I've tried similar things to this before using the For Each but when you use it to delete a row it sometimes skips the next row as it continues at the next one.
I've not managed to get it to work before, I'm just looking for a little guidance really as to how to do this correctly.
VB:
For TopRow = 1 To BottomRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row Step -1
'If is blank delete row.....
Next
View 1 Replies
View Related
Nov 17, 2008
I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.
I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.
What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.
code that I found that works:
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
I tried this:
For Each col In NewSh
ColNum = ColNum + 1
Range(RwNum, ColNum).Select
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
Next col
View 9 Replies
View Related
May 8, 2014
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like
Variable 1
Variable 2
Variable 3
[Code].....
View 9 Replies
View Related
Apr 13, 2013
I have a range selected (A4:C15).I would like to deselect the range and make A4 the active cell. Is this possible without a macro?
With a macro, one could use:
Application.Goto Selection.Cells(1)
I generally select a large number of cells and would like to go back to the start cell in the range. I could do it by pressing the left and right arrow keys but is there a shortcut that will allow me to do the same?
View 2 Replies
View Related
Aug 9, 2006
I am having a problem with a custom funciton I am trying to create. It will exit after it is finished with the IF Then Else statement. I need it to continue onto the Do loop at the bottom that does all the work.
Function UPCECheck( num As String) As Long
Dim CheckNum As Long
Dim TempCheck As Long
Dim X As Long
Dim Holdtxt As Variant
UPCECheck = 0
CheckNum = 0
Debug.Print Len(num)
If Len(num) = 12 Then
Holdtxt = num
ElseIf Len(num) < 12 Then
Holdtxt = "000000000000" & num
Holdtxt = Val(Mid(Holdtxt, Len(holdtext) - 12, 12))
End If...................
View 6 Replies
View Related
Sep 24, 2006
I need a cell formula that will (a) identify the highest N values in an above specified column range, (b) color the interior of those N cells (I suspect that this is not possible), and most importantly (c) return the average value of N corresponding cells, where the corresponding cells are located on the same rows as the identified N high value cells but in a specified column to the left (not necessarily adjacent)
Does anybody know what this formula would look like?
Example:
-----------------------
...| A | B | C | D | E | F |
-------------------------
1 |....| * |.........| 7 |...
-------------------------
2 |....................| 2 |...
-------------------------
3 |....| * |.........| 6 |...
-------------------------
4 |....................| 1 |...
-------------------------
5 |....| * |.........| 5 |...
-------------------------
6 |....................| ? |...
-------------------------
? = average of B1,B3,B5 where (N = 3) and (specified column to the left = B)
View 4 Replies
View Related
Aug 29, 2008
The loop is supposed to stop when the cell that is 4 columns to the left, in the workbook named "Complete DSS", changes numbers.
/ This is the cell ref. / This is the original
Do While ActiveCell.Offset(0, -4) = ThisTape
The number will stay the same for a while until it changes tapes, then it Should stop when they are no longer equal.
It used to work, but maybe some recent changes have messed up the function.
Sub Compare_printout_2_WDMS()
Application.ScreenUpdating = False
Dim ray, sht As Worksheet, Exist As Boolean
Dim BookA
Dim I As Long
I = Worksheets.Count
AreaNum = InputBox("¿Que es el Area?")
ThisTape = Range("C8").Value
ActiveWorkbook.SaveAs Filename:="C:Documents and Settingssmith27DesktopArea 319 printoutsArea " & AreaNum & " " & ThisTape & ".xls"
Columns("A:A").insert
LR = ActiveSheet.Range("B65536").End(xlUp).Row..........................
View 9 Replies
View Related
Aug 11, 2009
Do Mymacro1
loop until
I've tried a couple of ideas but none seem to work. I even googled this hoping that since this seemed like such a simple thing that I couldn't be the first person to run into this... guess I was wrong. the range of cells is BF30:CF56. I tried using a cell in the worksheet to test if all the cells equaled 0, that didn't work, even after I wrote the calculate code into the macro. Nothing I try seems to get the code to stop. is there somthing I'm missing?
View 9 Replies
View Related
Mar 14, 2012
I have merges cells starting at "AS18:CC18" and continuing through "AS616:CC617". I want to loop through each row from row 18 to row 617 and perform an action on the cell content (
code to loop through "AS18:CC18" to "AS616:CC617"?
View 1 Replies
View Related
Feb 6, 2008
I’m having some difficulty getting my code to work. On a worksheet there are pairs of columns containing data; the leftmost column’s range is named. I have a userform with several optionbuttons that are named with the corresponding range names. I can successfully populate the userform's combox with unique items from a named range that is selected with the optionbutton.
When user selects item with combobox, I want to parse that named range with the combobox value, selects the value in the corresponding row (to the right) and then populate the userform's listbox with that value. The nonworking code is given below:
Private Sub cbxFiltList_Change()
Dim fCntrl As Control
Dim TempRange As Range
Dim TestRange As Range
Dim cLoop As Range
Content_lbx.Clear
For Each fCntrl In UserForm1.Frame1.Controls
If TypeName(fCntrl) = "OptionButton" Then
'If the optionbutton = True then the make the range to be tested the same name as that of the optionbutton'..............
View 2 Replies
View Related
Mar 7, 2008
I am trying to create a loop in which the code "reads" ... lets say... cell F5. Determines if cell F5 = 1. if not, it moves on. only, instead of moving to cell F6, it moves to cell G5. so i need to add one to the column letter. How is this best achieved? having trouble with this. adding a letter to it concatinates them, and adding numbers, well its a mismatched type so that simply does not work.
View 3 Replies
View Related
Mar 7, 2013
I have a large matrix, with categories as columns and entries as rows, that have an "x" or a blank for each category showing if that entry has it or not. Some of these categories will get hidden based on the current user's usage. I want to use a subset of the unhidden columns and hide all the row entries that have ‘x’’s for this subset of columns.
The way I want to go is to create a list of the unhidden columns that match my subset criteria, I was thinking in a Range object. From there, I can use these columns and go row by row checking the rows value at that column to determine if I should hide it or not. This is how I think I should approach this and I'm having a bit of trouble with objects/syntax in VBA. Here is the code/pseudo code I imagine making this possible.
VB:
Dim Subsets As Range
For Each col In Sheets("Test").Range("A3:M3")
row 3 has the option # For Each column that determines If it Is In the subset
If col.EntireColumn.Hidden = False Then
[Code] .....
I am pretty sure everything except the Set Subsets = Union(Subsets, col) line is okay. The questions I have about using the Subsets Range object as this data structure are
Obviously, how can I "build" this object as I go along checking for unhidden columns that include my options? Why wouldn't Set Subsets = Union(Subsets, col.Entirecolumn) work?What's a good way to put ranges into Subsets so that I can easily use it to know which columns to check in each row entry? Can I add an entire column, or should I just reference the column of the cell that's in Subsets?
I've read over Ranges in VBA and am still coming up short with this usage.
View 1 Replies
View Related
Oct 4, 2006
i'm trying to get my code to work. Basically my code opens up workbook and extract the relavant data into the main workbook. Previously Its just a single For loop and it works perfectly after changing to double for loop its stuck.
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim rCell As Range
Dim lCount2 As Long
Worksheets("Data").Select
Range("a2:ac65536").Clear
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
Set wbCodeBook = ThisWorkbook..................
View 6 Replies
View Related
Jan 30, 2008
I've never worked with Excel macros prior to today. I'm working on this for a friend.
I've got this sheet that's going to a web address, importing a table from the page, and making a line graph of the data. I got all that to work fine (after a lot of trial and error). Now I've got the data, and I want to make a scatter plot of it...
Here's the thing. I want each month of the data represented as a line on the plot.
This is hard to explain. It looks like this:
[url]
So from there, I want a loop to scroll through the dates and select a range that represents January, a range that represents February, etc, so I can save those ranges as variables and make a scatter plot with 12 curves on it.
Here is the code I have so far, in case you want to see it, not sure why you might need to though.
View 14 Replies
View Related
Aug 22, 2008
I think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells
Sub Analysis()
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim k As Integer
Dim TheLast As Integer
TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row
For i = -7 To -26 Step -1
For x = 24 To 43
For y = 14 To 43
For k = 16 To TheLast.................
View 9 Replies
View Related
Aug 6, 2008
When I set a "for each cell in range" to go through the range, and if there are merged cells in this range, this will result an endless loop.
View 9 Replies
View Related
Mar 25, 2012
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
View 4 Replies
View Related
Nov 14, 2008
How can I determine what the bottom row is in a range in VBA? I have an SheetChange event sub that takes in Target as Range. I want to know what the first/last row/column is in the Range. So, for example, say the Sheet has values in A1:B5 and I paste over A1:B4. Target will be A1:B4. I need a method that returns 4. I tried Target.End(xldown).row, but that gives me 5 (since theres data in A5).
View 4 Replies
View Related
Sep 13, 2013
I've recorded a macro that selects a bunch of cells so I can work with them. However, it's hard-coded to the bottom cell of H1551, and I need it to work no matter how large the range is.
Code:
''' Concatenate column H with B & F
Application.Goto Reference:="R2C8"
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-6],"" "",RC[-2])"
[Code]....
View 4 Replies
View Related
Aug 13, 2008
I now have everything set up to do exactly what I want except this last bit.
All I want is for the overview page to show the top three referrers for each month. At the moment, it's finding the top and bottom 'scorer' but if more than one has the same score I can only see the first alphabetically.
View 10 Replies
View Related
Dec 17, 2012
2012PlayerStats.sample1.xlsx
I have a list of players and the team they play for in Columns A and B.
In column E I have created a list of only unique names and in F I need the Team the played for last. However with Vlookup I am only getting the team they played for first. How can I get the team they played for last?
I have tried vlookup and index/match and get the same.
View 3 Replies
View Related
Apr 11, 2012
I am in need if inverting a Range (top to bottom), in the same location, like this ....
1 2 3
4 5 6
7 8 9
to...
7 8 9
4 5 6
1 2 3
How would this be done ?
View 9 Replies
View Related
Aug 2, 2012
The following code is doing what I want it to do in part, its copying the data in range k3 to q3 to the next empty row in columns a to f
It works when I change any cell in column K (11), I want to change to only on a change in K7 or K8? As an afterthought if I'm dealing with the same sheet do I need to make reference to sheet2?
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim nextRow As Long
If Target.Column = 11 And Target.Cells.Count = 1 Then
[Code].....
View 2 Replies
View Related
Oct 23, 2008
I have a s/s encompassing over 350,000 rows.
Data consists of a series of ranges from columns A:N and rows varying in number between 3 and 30.
There are two blank lines between each range of data.
Each range is (with exception) numerically ordered down column H (e.g, 1, 2, 3, etc).
Problem: there are around 1100 occasions when a range contains a row of data which is to be disqualified from consideration. On these occasions the data always appears in the top row of the range and is identifiable in column H by the nine codes 111, 222, 333, 444, 555, 666, 777, 888, and 999).
I wish to find a macro which will :
1. where the top row of a range in column H is one of the codes 111 to 999, transfer that row of data to the bottom of the range.
2. delete the now empty top row from which the transfer was made from.
3. insert a new blank row at the bottom of the range below the row which the data was transferred to (in order to maintain the two blank rows above and below each range).
View 12 Replies
View Related
Aug 13, 2009
I was wondering if there was a way to create a two cell dynamic range that doesn't expand, but instead shifts.
The cells that I'm interested in are always at the bottom of the column of data. For example, the first two cells I want as my range are C13 and D13. Then, for my next use of the range, I would like it to include C14 and D14.
View 11 Replies
View Related
Jul 19, 2012
I need to underline certain cells based on the value of a cell in this case from column "E". Right now I run a macro to look at the value in each cell in column "E". THe macro then adds a certain amount of blank rows below the that row. So, if cell E1 has a 4, my macro runs a calculation that says 4 divided by 3, +2. So it would add 3 blank rows under row 1. It does this all the way down my column of data.
Now it gets tricky. To the right of column E, I have 6 blank columns, F,G,H,I,J,K. F,H, and J are spacer rows with no data. I need them blank with out any borders. Columns G, I, and K are the columns that I am wanting to add borders on the bottom of the cells based on the value in column E.
I need code to look at the value in E1, in this case 4, and add borders to 4 cells, in this case G1, I1, and K1 and then G2. If the value would have been 5, I need I2, 6 would add a border to K2 and 7 would add a border to G3. Catch my drift?
So, every cell in column E will not have a value because of me adding blank rows. So when there is a value, I need to add borders starting on the same row the value is in. At most, there will only be 3 underlined cells per row because of the format. Once the top row is filled, I need to drop down to the very next row.
View 9 Replies
View Related
Aug 30, 2006
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
View 7 Replies
View Related
