Nested Loops. Start & End Loop
Dec 5, 2006
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
Sub LowRisk_Chart()
Clearout
Voffset = 0
Vcounter1 = 2
Vline = Sheets("ChartWork"). Cells(Vcounter1, 1)
Vblocks = Sheets("ChartWork").Cells(Vcounter1, 4)
Vstdman = Sheets("ChartWork").Cells(Vcounter1, 5)
Vlength = Vblocks
View 3 Replies
ADVERTISEMENT
Aug 11, 2009
I seem to be having trouble creating a nested loop. It seemed simple enough in my brain holder, however, in real life...Well I am here aren't I. Here is my
View 2 Replies
View Related
Mar 26, 2008
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
View 3 Replies
View Related
Jun 20, 2007
how to nest do loops with find next capability. I have written code (that does not work, of course) that I hope will explain what I am trying to do. The code error is something like 'Object needed'.
Do
Range("r:r").Select
Selection.Find( _
What:="2", _
LookIn:=xlValues).Activate
n5 = ActiveCell.Address
View 9 Replies
View Related
Jan 29, 2014
I am trying to run 2 for each loops inside another loop, and I was wondering if there is a way to move to the next value for all loops at the same time.
So when it reaches "Next starting, final, scal", all ranges move to the next cell before performing the formula.
View 2 Replies
View Related
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.
[URL]
Company
Item
Quantity
Costs
Tesco
Cheese
4
50
Tesco
Milk
5
50
[code]....
View 3 Replies
View Related
Dec 5, 2007
On my sheet in Range("a4:a20") I have the names of the "Named Ranges" that I set referenicng ranges on different worksheets. Range("b4:b20") I count the # of rows for each of my "Named Ranges." I need a loop that loops through the values in column "B" and if it is > 0 then to take the corresponding "Named Range" in column "A" and paste it starting in cell "E6".
This is the start to my code it isn't working
Dim CellV As Range 'i want this to be the Cell Value
Dim CellValue_Range As Range 'this is the range B4:B20
Dim NRange As Range 'This is range A4:a20
lastrow = Range("a1").Value 'this counts the number of named ranges I am dealing with
For Each CellV In CellValue_Range
If CellV.Value > "0" Then
Range("a4").Value.Copy 'this range "a4" should change one row down each time it loops (this also correspondes to my "NRange"
Range("e" & lastrow).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If
Next CellV
View 9 Replies
View Related
Jul 3, 2007
Im using the following code to assign numerical values to words in two columns, then multiplying the values together and painting a cell with a specific color assigned to the final number. The problem is it wont do this past row 19
mLastRow = Sheet3.Cells(Rows.Count, "I").End(xlUp).Row
For m = mLastRow To 5 Step -1
' assign numerical values to the words in these columns
e = 22
r = 23
' Give Extent a value
If Cells(m, 9) = "Minor" Then
Cells(m, e) = 1
End If
If Cells(m, 9) = "Moderate" Then
Cells(m, e) = 5
End If
If Cells(m, 9) = "Serious" Then
Cells(m, e) = 8
End If
If Cells(m, 9) = "Critical" Then
Cells(m, e) = 10
End If....................................
View 4 Replies
View Related
May 13, 2009
I have a large range of data and on each row the cells contain either 0, 1, No or another word. I would like to display the other word at the start but im really stuck on how to do it. This other word can change on each row but each specific row will only have one word. I can post a sample spreadsheet if necessary.
View 5 Replies
View Related
Nov 30, 2006
i have a problem with a nested loop:
it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.
am i doing something wrong?
dan
==========================================================
Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster.
==========================================================
Sub new2()
Dim Org_Area As Variant
Dim Item As Variant
Dim Copy_To1 As Variant
Dim Cell_Ref As Variant
r = 1 ..................
View 9 Replies
View Related
Feb 17, 2007
I think its something simple I'm missing here. I have a range of stock prices (Range("E3,E4,E5,E6")) what I'm trying to do is use for each next loop to calculate the number of shares that I could trade. and place those shares in a range of cells adjacent to its corresponding price (Range("G3,G4,G5,G6")). I think the problem is in my loop statements but I can't seem to find it . Could someone kindly show me the error in my ways.
Code: ...
View 9 Replies
View Related
Feb 4, 2013
I'm trying to finish writing the last portion of a macro created to automate my data analysis project for my research but I'm having trouble writing the last For Loop of the code. I have a target cell B25 which states a given temperature of my reaction. Once a value is entered in B25, 5 activity coefficients are calculated and displayed in cells B31,35,39,43, and 47 which I would then like to copy and paste in cells J31-J35.
However, I'd like to calculate the activity coefficients for a range of temperatures which are listed in cells J30-W30. Thus, I need to create a For loop which copies the value from J30 into B25, copies the values from B31,35,39,43, and 47 to J31-J35 and then moves to the next column replaces the value of B25 with K30 and repeats the same process until all the columns are filled.
View 2 Replies
View Related
May 25, 2014
I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.
Setup Sheet
Class Number
Number of Arenas Used
Class 1
[Code].....
View 3 Replies
View Related
May 21, 2009
I'd like to know how I can loop through a series of frames and loop through each of the option buttons in each frame and write the value of the selected option back to a worksheet.
Here's an example of how I'm doing it now:
For Each ctl In fraWorkMode.Controls
If ctl.Value = True Then
ws.Range("A1").Value = ctl.Caption
Exit For
End If
Next ctl
For Each ctl In fraTransport.Controls
If ctl.Value = True Then
ws.Range("B1").Value = ctl.Caption
Exit For
End If
Next ctl
For Each ctl In fraTravelTime.Controls
If ctl.Value = True Then
ws.Range("C1").Value = ctl.Caption
Exit For
End If
Next ctl
I'm also thinking that OFFSET is maybe a better option than specifying each cell for the collected data.
These frames are on Userforms for a survey and some of the frames contain up to twelve other frames so a nested loop approach appears to be more efficient.
View 9 Replies
View Related
Jul 20, 2006
how to speed up the following loop? It takes about 40 seconds.
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Integer
Dim y As Integer
Dim Snominaloc As Double
Dim Scs As Double
Dim Sirating As String
Dim Mnominaloc As Range
Dim Mcs As Range
Dim Mirating As Range
Dim Mcbrating As Range
Set Mnominaloc = Worksheets("Parameters").Range("b10")
Set Mcs = Worksheets("Parameters").Range("b22")
Set Mirating = Worksheets("Parameters").Range("b12")
Set Mcbrating = Worksheets("EL").Range("b33")......................
View 2 Replies
View Related
Dec 8, 2009
I'm sure I just need a change in the line of code in red, but can anyone see why when the code reaches the For/Next loop in red it just jumps over it and goes to the End With line?
FYI - The code is supposed to check (select) the boxes in ListBox1 if any item in the list it's creating matches the value found in Sheets("Zone Associations").Cells(Rng, sZone)
View 9 Replies
View Related
Jul 4, 2014
I have a problem with a nested loop in my macro as Excel hangs every time i run my script. I suspect it is because my sheets have too many elements.
I am doing a software localization, and I need to translate records in column A (english) to column X (slovenian) in the same row. With each new version of the software, the number of rows changes.
I already manually did one translation (44000 rows), and now i need to do a new version translation, so I want to use my old translation (because many if not most of the elements are the same) and insert values into the new excel file.
In the new file in sheet1 I have a full column A with english words and an empty C column with slovenian words that I need to fill.
Into sheet2 I copied the old english in column A and slovenian translation in column B.
On sheet 3 i created a button that when clicked, goes though each row in sheet1.columnA, compares it to sheet2.colimnA, and if there is a match, copies the value from sheet2.columnB into sheet1.columnC. So, this should fill most of the translations I need to do, and the rest I will do manually
I am quite new to VBA so this is what I came up with:
[Code]....
The number of rows is around 44000 in each sheet. How I could optimize or even completely avoid the nested loop, because each time i click the button I need to wait for almost an hour and then Excel hangs.
View 8 Replies
View Related
Oct 27, 2009
I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.
View 3 Replies
View Related
Jan 18, 2010
I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column.
ie. cells R2: HS2 should all be divided by Q2
cells R3:HS3 should all be divided by Q3
etc until R2000:HS2000 are divided by Q2000
I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.
View 14 Replies
View Related
Oct 30, 2009
I'm having some problems understanding how to change the sequence in loop structures..
This isn't the entire code, but maybe it will get the point across.
Currently the loop is giving me the following sequence..
"CL" "WTI OI" "HO OI" "RB OI"
"HO" "WTI OI" "HO OI" "RB OI"
"RB" "WTI OI" "HO OI" "RB OI"
What I want to get is:
"CL" "WTI OI"
"HO" "HO OI"
"RB' "RB OI"
Is there a way to the loop to go to: x y x y x y
Instead of: x y y y x y y y
Dim ContractCode As Variant
Dim ContractTab As Variant
ContractCode = Array("CL", "HO", "RB")
ContractTab = Array("WTI OI", "HO OI", "RB OI")
For Each x In ContractCode
For Each y In ContractTab
Debug.Print y
Debug.Print x
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=x
Cells.Select
Selection.Copy
Sheets(y).Select
Range("A3").Select
ActiveSheet.Paste
Next
Next
View 9 Replies
View Related
Oct 9, 2012
I am working on an excel sheet that asks the user for a Start and End value, to create a print selection. It should then loop all of the numbers between both values into Cell B1, update the sheet and print the page each time.
I think i have the VBA code worked out, except for the fact that i seem to get stuck at the loop action for printing all values between the 2 input values ( i don't really know how to do this).
I have an example of the file uploaded here: Zippyshare.com - Printbarcodes-test.xlsm
Here is the code i am using (the loop/selection part is still missing):
Code:
Sub PrintSelection()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
[Code]....
View 9 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 31, 2006
I have a piece of code that put a check in all checkbox in column B from row 5 to row 50 but in column C, I have data from row 5 to 38. I want the macro to stop at row 38 in column B. When the cell in column C is empty stop putting checks in column B. How can I make this macro Check all checkbox down column B and stop when column C is empty. How do I add a loop to stop when the cell in column C is empty?
Private Sub CommandButton1_Click()
Dim CB As Variant
For Each CB In ActiveSheet.CheckBoxes
CB.Value = False
Next
End Sub.......
View 3 Replies
View Related
Aug 8, 2006
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 ........................
View 5 Replies
View Related
Apr 13, 2007
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................
View 4 Replies
View Related
Oct 18, 2008
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 ....
View 14 Replies
View Related
Aug 20, 2009
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).
View 9 Replies
View Related
Oct 9, 2009
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.
View 2 Replies
View Related
Oct 2, 2008
I have recorded a macro that cleans up my data that I receive from an external source and inserts two columns.
I recorded a seperate macro to execute an "if formula" that evaluates Column A and B and if A is blank return the value in B, else return the value in A. I wrote the formula =if(a1=" ",b1,a1).
I referred to the text I got in the class and found that I can use a "Next Loop". I added to the formating macro the Next Loop critera and instead of inserting text I inserted the above formula.
It ran and returned data, however it was not in the column I intended, it cascaded off to the side of my data in a step down (kinda like stairs).
View 9 Replies
View Related
Nov 3, 2009
I need to maintain a KPI in excel2000 which details weekly progress, I normally do this using some condition formatting and hiding cells, I have to be honest its not pretty.
What I would like to do is to use the colorindex function to show the wk breakdown status and a Do loop on my target range, I think!...... I've a basic example below,
If I could show the benefits to my boss he might even let me take a VBA course.....
View 9 Replies
View Related
