Continuously Add To A Range Within For Each Loop Based On Cells Value?
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
ADVERTISEMENT
Feb 7, 2008
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 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 4, 2014
I am trying to run (or loop) through a column of cells, and if cell contains certain text (e.g. ALPHA, BRAVO, CHARLIE) and delete the row if found.
My macro as I run it:
Sub DATA_TEXT ()
'Number of cells to loop through (I am unsure how the .xlend works!)
For i = 1 to 2500
'If function defining the criteria.
If cells (1, i) = "ALPHA" OR "BRAVO" OR "CHARLIE" Then
[Code] .....
View 4 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
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
Jan 9, 2011
I looking for a macro that will go through multiple sheets & change specific cells to values if the column header is = to value set in specific cell.
for example
I would like the macro to look at row 3 in each tab (page 1, page 2, page 3) and if the value you is equal to X (parameter input on different sheet) then change the formula to a value in row 6 & row 12 of that column.
I'm attaching an simple example that i looking for this on. The green cells are the one i would like to change to a value.
Book1.xlsx
View 8 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
Apr 10, 2013
I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
View 1 Replies
View Related
Jan 3, 2008
Suppose there are two ranges
rngA = range("A1:B5") ' The cells that change
rngB = range("D1:E5") ' The results
Assume that based on an iterative lookup process the values in rngA change. After each iteration the values from rngA are determined and placed into rngB (this i can do). Each time rngA changes i need those values to be added to the previous rngA values (otherwise the values being stored in rngB).
View 4 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Jul 31, 2009
I'm looking to have my stopwatch run continuously until "and only until" a command button ("Stop") is clicked. I already have a code and stopwatch in place. I have 5 command buttons: start (starts the timer), call (activates/hyperlinks to another sheet where I have more command buttons and data entry needed/), stop (stops the timer), reset (resets the timer), and record (stamps the elapsed time on the sheet1).
They work great BUT the problem is, when the cell is touched (i.e. when you type a character or double click on any cell even if it's on a different sheet, or even on a different workbook) the timer stops!
I'd think that all this would require just a simple string/line inserted in my code. I'm really, really new to VBA and doesn't know how to write my own codes yet. I have to google what I need every time and do trial and error! Just to give you more info-- I'm using this for a call simulation program, in which the user is an operator taking in a call. She would have to be aware of how much time have passed after performing some actions/data entry on the different sheets on the workbook. I hope I've explained it enough. :D Anyway's here's the code I got, which I think needs just a few edits.
View 10 Replies
View Related
Jan 15, 2008
I have a series of data in 2 columns...say A and B...that I want to concatinate into a string of data...i.e A1&A2&B1&B2&C1&C2 etc etc....
Can I include this in a macro so that it will continue to concatinate the data until it gets to the end of the list (i.e a blank cell) ?
View 6 Replies
View Related
Apr 18, 2008
I am trying to lock the unused cells in 32, 2 column by 7 row named ranges, based on whether or not two cells, above each range are equal or less than each other. In other words while one of the cells is less than or equal to the second cell all cells in the range below should be unlocked, as soon as that condition is no longer true the blank cells need to be locked.
I am trying to use this in the Workbook_Sheetcalculate so that the macro will run automatically.
View 3 Replies
View Related
Sep 16, 2013
I am using Excel 2010. I have been given a task at work that can save my team a lot of time if I can solve the problem. Every month, we have a spreadsheet with about 5000 rows that we have to email. In each row, there is a range that we have to email to a specific email. For example, I would have to copy and paste Range A2-R2 in the body of the email, and then email it to whatever email is in cell S2. I would then continue this for the next 5000 lines, making it a possibility that i will be sending 5000 emails manually.
I have been trying to come up with a solution through VBA that would automatically send these emails. My goal is to automatically send the Range A2-R2 to outlook email, then cell S2 into the "To" email address box, and then automatically send it. So far, i have successfully been able to send one row, but cannot figure out how to loop it for the remainder of the rows.
A couple other key points are that I have column headings as well (Range A1-R1). If possible, I want to be able to include the column headings in the email body as well. Example - first email would be range A1-R2. second email would be range A1-R1 and A3-R3, and so forth. The body of the email would also contain a standard script, such as "Please review the information below."
The goal here is to save everyone from having to send 5000+ manual emails. This would be a big boost for my team.
Code:
Sub Email()
Dim rng As Range
Dim OutApp As Object
[Code].....
View 5 Replies
View Related
Dec 22, 2013
I am currently trying to sum up values every day of the week. (salaries )
That means I have a sheet called Salary where I have the entire year mapped out pr. day. Like you see underneath:
Date
30/12/13 0
31/12/13 0
01/01/130
02/01/130
03/01/130
04/01/130
05/01/130
etc.
In another sheet called Overview, I have another column divided into weeks, like this:
Date
30/12/12 0
06/01/13 0
13/01/13 0
20/01/13 0
27/01/13 0
Now what I need to do is, take the 7 days in a week in the first sheet, and summarize it in the first week in the overview sheet. Now that is simple the first time, =SUM(Salary!C1:C7)
But how do I drag down and automize it so that the next row becomes =SUM(Salary!C8:C14) instead of =SUM(Salary!C2:C8)?
So basically I need to autofill with intervals of 7 (the 7 days in the week) on every row in my Overview sheet.
View 1 Replies
View Related
Jul 13, 2014
I am working on making a spreadsheet that will rack which guests are using which membership for a certain client. Here is what I've got so far. My goal is to have the spreadsheet work like a calendar where the dates are changing daily as well as all the information with it. I was able to make the dates change, but I am unable to have the specific data change with them. Is there a way to set a column of information to a specific date?
View 5 Replies
View Related
Apr 25, 2007
With the MsgBox code, when that part of the VBA is reached, it requires the user to press the OK button for the next part of the VBA to execute. But is it possible when running a macro etc, for a MsgBox to appear right at the start, and remain for the duration of the macro, and then disappear (or an OK button appear) when the macro is finished? As some of the macros we run are long and it would be good to stop people from doing other things whilst it is running.
View 6 Replies
View Related
Jun 10, 2009
DISCUSSION
I have a spreadsheet that I have been creating for work. It involves a UserForm (FRM_TubingTransfer) and writing the data entered onto sheets (either the ‘MASTER SHEET’ or ‘COMBOBOX DATA’) in the same workbook.
PROBLEM
I have (with your help and much trial and error) gotten the workbook to a reasonably working model. The only major thing left is the ‘AVAILABLE TUBING’ sheet. This sheet is the most complicated, I think, because it isn’t just strictly writing the data.
First, any time there is a NEW PROPERTY / SUPERVISOR entered in the form onto the COMBOBOX DATA screen, the code needs to check the AVAILABLE TUBING sheet and check if that data has already been entered there (This is because there may be some instances where the PROPERTY NAME, and PROPERTY NUMBER are the same but have a different PROPERTY AFE. Aka, there may be two entries with ‘WELL 1’ that have different AFE numbers but they are the same location and need to be treated as such.). Also, if a pipe yard (denoted by “-YARD-“ in the Property number and AFE number column) is entered, do not enter it on the AVAILABLE TUBING SHEET.
Second, when a TUBINGTRANSFER is processed, the program needs to do several things;
For the FROM LOCATION (if a well and not a pipe yard)
Add Buried Joints to the buried cell
Add (E) to the damaged cell
Subtract (A), (B), and (C) from the total on location cell
For the TO LOCATION (if a well and not a pipe yard)
Add (A), (B), and (C) from the total on location cell
View 11 Replies
View Related
Jun 24, 2012
I'm currently doing a new worksheet for work
Cell's A1: Commission Per Sale Cell B1 Running total of commission
I would like the running total to continuously incorporate all of the above commissions and this has to be in the column next to the new sales commission.
E.g.
Commission $5 $7 $9
Running Total $5 $12 $21
View 2 Replies
View Related
Apr 15, 2013
I want to create a excel file with VBA code that ping's the IP's from column A, give a response to column B (OK or NOT OK) and in column C gives the last "OK" ping date and time. Something like:
Code:
A B C
194.154.200.10 OK 14/04/2013 13:10
194.154.200.11 NOT OK 14/04/2013 13:00
194.154.200.12 OK 14/04/2013 13:10
The ping process should be a continuous loop.
What I found this far involved text files and I don't want that.
View 3 Replies
View Related
Jun 29, 2006
I’m trying to program a specific label within a userform to continuously display the system time… I’ve tried to code something that loops while the userform.visibile property is true. This obviously just hangs the system… Is there an easier (or any other way) to do this?
View 7 Replies
View Related
Aug 28, 2006
currently i am putting together a vba code to do the following:
1. Cycle through 3 sheets and waiting for 2 seconds on each sheet
2. Refresh after the cycle has finished
3. and then be contiously looped.
4. a button or something to make it stop looping.
This is the code i have got thus far:
Sub SwitchingSheets()
NewHour = Hour(Now())
NewMinute = Minute(Now())
NewSecond = Second(Now()) + 2
WaitTime = TimeSerial(NewHour, NewMinute, NewSecond)
Sheets("Sheet1").Select
View 3 Replies
View Related
Oct 18, 2011
I have data on my excel spread but can not transform them into continuously compounded monthly returns.
View 1 Replies
View Related
