VBA / Using Nested Loops To Expand Summarized Table?
Oct 14, 2013
As far as I can tell there needs to be a nested loop with some kind of variable inside a variable, but to be honest it's well beyond my current skill level... I have the file hosted on my Google Docs below.
I have a basic while loop that does what I need it to do stand alone. Now I want to replace the 3 hard coded ranges (n51,r26,m26) with ranges that update during iteration #2. The new ranges for the while loop would be (n52,r27,m27). I will eventually have more than 2 iterations.
Sub Macro6() ' ' Macro6 Macro ' Macro recorded 3/19/2008 by User ' ' Keyboard Shortcut: Ctrl+g '
Dim rng As Range, Acell As Range Dim rng2 As Range, Bcell As Range Dim rng3 As Range, Ccell As Range
is there any body kind enough to look into this code and tell me from where the loop body starts and ends and which is the loop variable and how it is being incremented or decremented. i know it is a stupid question but i admit here that i learn and manage to update my sheets only because of this forum help, as i have absolutly no idea of VBA when i started some couple of months back but know i can do little bits. thanx again for all the help i got so far and hope to get some solution this time as well
The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.
My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.
The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).
I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.
Possible to get a pivot table to automatically expand the range selection of data as it grows. I have a worksheet that grows daily and I am running a pivot table from this. If I refresh the pivot table it doesn't pick up new data unless i change the range or i have a huge range selected in the first place. Problem is if i select a huge range then the grouping options i am using won't work correctly.
I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
Recorded macro. The hope is to insert a excel formatted table a set number of times. I have found a loop code that references a Cell A1 and repeats that amount of times. So if A1= 10. There should be 10 tables inserted. However on the second time there is a fault with the table name. I need the name to change each time the loop is run. ie Table1, Table2, Table3 etc up until the loop stops (A1 contents).
I am using excel for windows 2010. The macro that i have so far is below.
Sub LoopTest() Dim n Dim V Range("A1").Select V = ActiveCell.Value
I am trying to write a nested if statement for my excel table. I calculated the betas of the stock portfolio. I would like to create an if statement that would do something like this:
if beta is less than 1 -- Below Average Risk if beta is greater than 1 but less than 1.25 -- Average Risk if beta is greater than 1.25 but less than 1.75 --Medium Risk if beta is greater than 1.75 -- High Risk...
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate() Dim sBook As String On Error Resume Next Application.EnableEvents = False Col = 8 ' Sets start column Do sBook = Cells(2, Col) ' References file name TaskRow = 6 ........................
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
how can i make a loop throught the G colum and to check if the cell before the currect is bigger/ lower or equal to the currect cell. and if its higher then an arrow will be shown '^' if its lower an upside down arrow will be show 'v' equal will show -> arrow. example:
G2 = 200 G3 = ^ 300 G4 = -> 300 G5 = v 209
so how can i change the G3 to G4 G5 etc.. in a loop in the: Range("G3").Select command? and i need to change the
.Value = "=$G$2" to Value = "=$G$3" Value = "=$G$4" etc... in 2 places................
i have this code that upon opening of workboot it generates the next number thats available on sheet 2 i then enter the data that i need to and click the button it then transfers to sheet2 and prints 2 copies and then clears the cells that i have input data to
what i really need it to do is exactly as it does right now but to then change the next number available to what it is ....
First of all, I'm completely new to both this forum and VBA. I have just done a programming course in java before. I hope you will forgive me if this have already been posted. No to my problem.
I am tryring to use different projected values to project other subparts of those units, which is done in the sheet mean needed weekly. Tje values are taken from projections. The answer that I get from mean needed weekly should be copied to a third sheet.
This is the code that I am using right know, but I cant get the for loop to work (or more exactly to be able to place the "i" in my text without making compilation errors).
One of the multitude of areas where I still need drastic improvement is with efficiency. Specifically, avoiding loops.
Are any of you aware of any good, basic level sites, blogs, posts, etc., about better methods or processes? I'm not particularly experienced with programming (I've been a software TESTER for 13 years and just started some programming in recent months), so the more basic the better.
I'm trying to better understand loops and variables. I have an instance I'd like to try and implement but I'm struggling with it. I know how to set a variable as 1 and loop through the code increasing the number each time but this one is a little different.
At the start of my code, I scan the worksheets for the worksheet names -
Monday Tuesday Wednesday etc etc
and then assign these worksheets as
Monday = ws Tuesday = ws2 Wednesday = ws3 etc etc
That way in my code I can do things to the workbooks like -
ws.Range("D4").Value = "blah blah" and always know it will be tackling the right worksheet regardless of the sheets position in the workbook or sheetcode.
Well I have lots of changes to make on each of these sheets (Monday -> Sunday) which are identical. What I'd like to do is something like this -
For each VARIABLE in (MY VARIABLES WS,WS2,WS3 ETC) VARIABLE.Range("D4").Value = "blah blah" Next VARIABLE
Is it possible to use a variable to change a controls name in a For Loop? I have several worksheet combo boxes that have the same root name, e.g., cbobox1, cbobox2. cbobox3, etc. I want to be able to keep the main name and change the numeric part in a for loop and also perform some function with the control. For example:
For x = 1 To 10 Worksheets("Name").cbobox(x).Clear Next x
Is it possible to do 2 loops in one procedure? I want to copy and paste formula the cells in one column and again in another column so I did two Do Loops. It only ran for the first loop but not on the second, although they have almost the same code (I just edited the column from C to I). Please see below
I have a data validation list that has approx 40 items.When I select the cell with the drop down list - only 10 or so items appear . I then have to use the scroll bar on the side to look further in the list. Is there a way that more than 10 or so ( possibly all ) items appear in the drop down box when the cell is selected - or is there a way that one can scroll down using the mouse wheel. ( I have to use the scroll bar at the moment ).
I want to find some code that will search down a column until it finds one of a number of different words. when it finds one of the words it stops and deletes the row on which the word is contained. after deleting the data in the row, it continues searching down the column until it finds another of the words.
for example......see work sheet attached,
I want some code that will search down column B until it finds Sally or Robyn. As soon as it finds either of these names it stops and deletes all the data in that row and then continues searching down column B to find Sally or Robyn again........
Is using a loop the best way to go with this?
MOD: thread moved to parent Programming forum (VBA)