Loop To Select Range Of Cells Representing A Month....
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
ADVERTISEMENT
Nov 4, 2013
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
View 2 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
Apr 19, 2007
I seem to be going round and round in circles with this, but I'm sure it should be easy.
I'm just trying to select a range of cells in Sheet2 of my workbook.
I've tried many different bits of code, including:
Dim namesTotal As Integer
namesTotal = 2500
Sheets("Sheet2").Activate
ActiveSheet.Range(Cells(1, 1), Cells(namesTotal, 8)).Select
(According to the Microsoft website, this is supposed to be the way to do it?)
View 10 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
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
View Related
Feb 28, 2014
Why the following code gives me an error. I have so much trouble with ranges, I use the syntax as it is prescribed. Here is the code. I will include some code lines underneath since I imagine they wont work either.
[Code] .....
I eventually will be turning the range statements into copy and paste instead of select. At the moment I am using select to make sure the proper cells are being selected.
I am also attaching my complete workbook as well.
Attached File : Therapy Tracker - Tester V2.3 -deleted logo.xlsm‎
View 2 Replies
View Related
Mar 22, 2007
How can I select a range of cells dynamically, not know how many cells down for two columns that I will need to select, there will be a blank cell at the end of the needed range.
View 9 Replies
View Related
Jul 25, 2012
I'd like to sum a range of cells if the date in the adjacent cell is last month, so... A2:A1000 holds the dates and B2:B1000 holds the values, i'm trying to figure out how to sum all of the values for the previous month (i.e. June).
View 7 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
Jun 29, 2006
I have a series of data values in non-adjacent columns in an excel spreadsheet.
In the following example, assume the | (vertical bar) refers to the start of
a new cell:
F|45|A|30|F|15|F|10
Using the SUMIF formula, I want to add all numbers which are preceded by a
cell containing the letter F.
SUMIF(A1:G1,"F",B1:H1)
What I need to do is specify a range of every second cell in the row (starting with Cell A1) for validating they equal F, and a range of the alternating cells (starting in column B1) for the range containing the data to add. How can I specify these ranges (I can't name each cell individually as I have more than 30 cells to add up in my real life situation and the IF function allows selection of no more than 30 values)?
View 12 Replies
View Related
Oct 4, 2012
I'm looking for a macro that only selects the unprotected cells in a range. So that I can copy a formula in these cells.
I found the below macro on the web but it will select all the unprotected cells in the worksheet. I guess this one can easily be adjusted to only select the cells in a range, but I do not know how to do this.
Code:
Sub SelectUnlockedCells()
Dim WorkRange As Range
Dim FoundCells As Range
Dim Cell As Range
Set WorkRange = ActiveSheet.UsedRange
For Each Cell In WorkRange
If Cell.Locked = False Then
[Code]...
View 4 Replies
View Related
Jun 21, 2013
I have come up with this bit of code by joining in two separate sets of code that I used earlier. What I want to do is this, for a given range I need to find each cell that has a comma and once that cell is found a formula has to be applied in a cell few columns to the left.. Here is the code that I am using..
Code:
Sub Macro8()
Dim i As Long
For i = 1 To 1000
[Code]...
The sheet can be downloaded from here, I want column H to be (Col B / Col C) whenever Col A has a cell with comma in it. For this sheet col H needs to be populated 3 in places. The code above does it only for first instance.
View 6 Replies
View Related
May 30, 2007
I am looking for a macro that will allow me to select the range of A5 to the last column with data in row 5
View 9 Replies
View Related
Feb 9, 2008
I need to select data starting from cell 16B until 16K until the end of excel file... i will use this to export data into database later... I have attached a sample excel file with records needed to be selected
View 3 Replies
View Related
Mar 12, 2008
I have a spreadsheet which always contains data beginning in column A and through row H. However, there may be blank cell(s) in columns B through G. Therefore, if I use
Range(Selection, Selection.End(xlDown)).Select
it will not take me to column H if say column d has a blank cell. The same applies to my rows which always vary.
View 3 Replies
View Related
May 9, 2008
I'm trying to create a macro for another user that she will use repeatedly for many different spreadsheets. Each spreadsheet will have a varying amount of rows that will need to be exported to a different file. She will highlight the cells she wants exported and then hit a button to do so. My problem is this: how do I code a macro so that it will know which cell is the beginning of her selection and which is the end?
View 2 Replies
View Related
Jul 23, 2008
I have this:
Private Sub CommandButton1_Click()
Worksheets("Sheet1").Activate
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate
ActiveCell.PasteSpecial
End Sub
it errors to: SELECT METHOD OR RANGE CLASS FAILED
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
Aug 29, 2009
I have a sheet that has 65000 rows and 3 columns, I need to load this sheet into a system that will only deal with 25000 rows at a time. How do you do a huge range select with Excel?
I need to select range a1:c24999 cut it out and paste it into another sheet. I have about 75 of these these sheets to do is there a way with a formula, a script or? to do these huge selects?
I am a Mac user, running MS office 2008 for Macintosh.
View 11 Replies
View Related
Mar 5, 2013
How do I protect a select range of cells from being changed? There may be some formulas in certain cells, which produce data based on other worksheet cells. These cells are not for user data input, but for data output only. I don't want end users to acidently delete the formulas in cetain range of cells, by entering a value in the cell, which then will write over the formula.
View 5 Replies
View Related
Jan 13, 2009
I am having a problem putting a range select statement in my macro. Here is what I'm trying to do. I want to select E1, then count down 10 cells below and copy. My sheet is filtered, so those rows are not continues. When I do the record a macro it highlights the rows and it looks like this (e10:e506, but it's only 10 items). I only want it to count down 10 cells and copy. Is there a statement for this.
View 3 Replies
View Related
May 7, 2009
I'm trying to select a contiguous range of cells in a column and then iterate over that. I keep getting error "91", object variable not set. I have:
View 5 Replies
View Related
Apr 22, 2014
Why the following doesn't work?
Sheets("Sheet1").Range(Cells(1,1), Cells(20,1)).Copy Sheets("Sheet2").Range("A1:A20")
If I can get his to work, I will be able to use variables for the Cells arguments to give the functionality I want.
View 3 Replies
View Related
Sep 22, 2008
I’d like to copy a range of cells: (A1 – A4) and paste them into Cell A5, but here’s the rub: I would like the values after I paste them to be separated by commas.
View 9 Replies
View Related
Apr 13, 2009
I want to do is clear all the cells on a worksheet that are not locked. Problem is that any cell could be locked, no rhyme or reason.
View 9 Replies
View Related
