Avoid Changing A Loop Counter Within A Loop?
Oct 24, 2009
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.
The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.
Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?
View 3 Replies
ADVERTISEMENT
Jun 29, 2006
How can I write a For-Next Loop and have the counter skip values in between. For example:
For i = 8 to 17
When that is completed, the counter jumps to 21:
For i = 21 to 30
When that is completed, the counter jumps to 34:
For i = 34 to 43
View 3 Replies
View Related
Jun 22, 2006
How do you assign a loop counter value to a range row value? Scenario: Add cells from a column that aren’t successive. SUM C10 + C17 + C24 etc
Private Sub CommandButton1_Click()
Dim total As Integer
Dim counter As Integer
For counter = 10 To 700 Step 7
total = total + Range("Ccounter")
Next counter
End Sub
View 2 Replies
View Related
Aug 31, 2007
This loop is to find if there is the string "Need Parent " in the range. If so the Msgbox notifies the user.
After it finds the string, the loop needs to exit- I tried to assign a counter to the MsgBox, but no go....
For i = 6 To lLrw
If Range("D" & i).Value = "Need Parent" Then
MsgBox "Stop. Parent Records Still Need To Be Created."
End If
ii = MsgBox + 1
If MsgBox > 0 Then Exit For
End If
Next i
View 9 Replies
View Related
Mar 2, 2007
I am coding a macro that would take all information needed from an existing pivot to put in in a new report file.
Now the problem is that I couldn t get to paste the data (for some weird reason), so I did a loop that terminates with IsEmpty...
My problem is I have to write on loop per column, because I don t know how to tell excel to go to next column (that I know!) AND to the first row, which means doing an offset of (x,1), but how do you set the x for it to be adaptable if I change the length of the pivot table?
I really hope someone can help me with this, I tried to replace range("first cell") by cell(x,... and set x as a variable but i am not good enough obviously....
Here is what I have so far:
Dim k
Dim w As Integer
Workbooks.Add
ActiveWorkbook.SaveAs "C:Dokumente und EinstellungenCustomerServiceDesktopMonthlyReportAutomated.xls"
'iniatiation before loop
Workbooks("ChannelProductionlastestupdateJAN2007").Sheets("pivot").Activate
Workbooks("ChannelProductionlastestupdateJAN2007").Sheets("pivot").Range("a20").Select
k = Selection
View 9 Replies
View Related
Mar 31, 2008
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
View 9 Replies
View Related
Mar 12, 2014
I'm using a macro to drag down a formula across a worksheet then again further down the worksheet which is working ok but I know there has to be a better way of doing it. At the moment every time I add a new column I have to edit the macro over and over and its getting out of hand. I know there must be a way of rewriting the macro into a loop but my skills are obviously still new.
Here's the macro:
[Code] ......
This continues over to column AO so far then I drop down a few rows and do it again:
[Code] .....
I think I need to set variables that set the row and column each time. maybe a "drag formula one column at a time from row x to row y until column header is empty then move to the new row and repeat"?
View 4 Replies
View Related
Oct 16, 2007
i have set up some test script below!
i dont know if it is possible but can you concatinate two variables i.e in the example below can i move from Sum1 to Sum6 using the intiger stored in i as the end of the sum variable
Sub Sum()
Dim i As Integer
Dim Sum1, Sum2, Sum3, Sum4, Sum5, Sum6 As Integer
Dim Ltr As String
Dim Sum As String
Sum = "Sum"
Sum1 = Range("B5").Value
Sum2 = Range("C5").Value
Sum3 = Range("D5").Value
Sum4 = Range("E5").Value
Sum5 = Range("F5").Value
Sum6 = Range("G5").Value
For i = 1 To 6
MsgBox "The value in cell " & i "is " & Sum & i
Next i
End Sub
View 9 Replies
View Related
May 20, 2006
Trying to code my first custom VBA function. The function is supposed to translate the words in a cell by repeatedly looping through the string and looking through a list of provided translation words.
Problem is the variable name changes with each loop, and I can't figure out how to program the name change.
Function TestTranslate(TranslateCell, ProductCell) As String
Dim t As Long
Dim TranslateCeiling As Long
Dim SS1 As String
Dim SS As String
Dim Q, R As String
Dim W As String
Dim LLRString, MLRString, SLRString As String
Dim LRString, MRString, SRString As String
Dim TArray() As String
Looks like the loop keeps defining Q as "SS" & t instead of doing the select case formula. I tried removing the Q variable altogether and making it read just "SS" & t = Replace(R, LLRString, LRString) for example and keep getting a compile error.
View 9 Replies
View Related
Jul 27, 2006
I want to write a For loop so that it checks the range G2:R2 of Sheet1 for the first non-zero cell in that range. When it comes to the first non-zero cell, it will change the formula of A2 on Sheet2 to have the SUM of that non-zero cell and the next two cells to the right. Example:
SHEET1-
G2 = ""
H2 = ""
I2 = ""
J2 = "3"
K2 = "4"
L2 = "8"
M2 = "9"
N2 = "2"
O2 = "5"
P2 = "3"
Q2 = "11"
R2 = "7"
SHEET2-
A2 = SUM('Sheet1'!J2:L2) --->"15"
View 5 Replies
View Related
May 14, 2014
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
View 4 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 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
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
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 27, 2012
I am working on some code that loops through a column of number values. Whenever it encounters a number value and a blank cell in an offset column, it places that number value into an offset cell (forming a separate column to be compared to another column in a separate sheet). I would like to take all the values in that new column and begin placing them in a new column in a separate sheet adjacent to another table. Most of the time, these values should match the adjacent values in the separate worksheet. However, if they don't match, I would like a new row to be created for that mismatched value.
For example. This is the first worksheet. So far, my macro loops through the column with rows 1-5. It looks in the offset cell(0,2) for Isempty value and then places that value into the offset cell(0,6)
text
1
text
text
[Code]....
View 5 Replies
View Related
Aug 2, 2007
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")
For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c
View 9 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
Dec 17, 2008
Can't seem to figure out why my Do Loop keeps giving me an Error of "Loop without Do".
View 14 Replies
View Related
Apr 28, 2009
I have got a loop which is working fine but now i need another loop which will run till the end but need to repeat itself as soon the column x become 1 the highest number would be 3
here is my main loop A1 = 5000
and second loop need to run inside the this loop
Sheet1.Activate
i = Range("A1")
For b = 1 To i
If Cells(1 + b, 3).Value = "P" Then
Cells(1 + b, 29).Value = 1
Else
If Cells(1 + b, 3).Value = "S" Then
Cells(1 + b, 29).Value = 2
Else
If Cells(1 + b, 3).Value = "C" Then
Cells(1 + b, 29).Value = 3
End If
End If
End If
Next b
View 9 Replies
View Related
Oct 28, 2006
I have loops working in other loops. The macro is almsot working well. It does the calculation i want but it fails to stop a loop, because of that, the macro can't run the next main loop (c), which is to move to the next cell where the calculations must be run.
I attach a file. the troubleshooting macroation is Sub Itiration.
The code of this macro are bellow. Basically, the loop using d as counter run into an endless loop. I don't how to stop this loop without affecting the results which are calculated correctly.
Sub Itiration()
Dim CurCell As Object
Dim TempSum As Double
Dim d As Integer
For c = 3 To Cells(3, 4)
If Cells(11, c) > 0 Then
For i = 1 To Cells(10, c)
Do
View 9 Replies
View Related
Nov 18, 2006
Going through a loop, I am trying to load pictures into an image box (or alternatively into a label) one by one i.e. going through the loop the first time, I want to load picture 1, then on the second loop, picture 2 and so on. A bit like an automated slide show.
I have written a simple loop and have used the loadpicture function to load the picture into the image box. When the code runs, the image box only gets populated after the last run through the loop. I have tried using application.screen updating function and the image.activate function without success. It is a simple bit of code and I expect an easy problem to solve if you know excel vba well.
View 6 Replies
View Related
Apr 8, 2007
My code below goes into an infinite loop, with culprit line shown in bold and underline...
View 4 Replies
View Related
Jul 20, 2007
I am parsing 15,000 files from a network server. The files are all the same format and length. The problem is that the first few iterations of my loop run fairly quickly, 7 to 9 seconds a case, but after only 300 iterations I'm up to 60 seconds a case. How do I keep the last iteration running as fast as the first iteration? I've included the main loop of my parsing routine below.
Sub Fill_Summary_Tabs() 'fills out the Datapack and JMP tabs
Application. ScreenUpdating = False 'turn off screen updating for speed
Call PrepImporterTab 'formats the Importer Tab so that everything runs smoothly < 1 second
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject") 'part of the filename test
For N = StartingCase To EndingCase '***** Start of the Parsing Loop *****
Sheets("Setup"). Cells(20 + N, 1).Value = Time 'Print the start time of each case....................
View 7 Replies
View Related
Apr 18, 2013
I am trying to find a way of replacing a whole amount of data, over 1000 items, initially each cell contains this >>> 056001 Not Set, I am trying to remove 'Not Set', but the big problem I am facing is that Excel also removes the leading 0 or zeroes, when it finishes removing ... I tried several things, like changing the column to Text, and then paste the data from another column, because I read the Text format is preserved, nothing, as soon as the column is pasted it changes back to General, if I change it back to Text again, and then apply a Search and Replace, each cell is changed to 56001, which is not what I intended, the only way I have seen it works is if I manually remove the string, then it works and is changed into the text format and number as text, as I intended to
View 1 Replies
View Related
Feb 13, 2007
This works to locate the first instance found:
View 11 Replies
View Related
Jul 16, 2009
I have managed to successfully use a do until loop, however once finished, I would like it to move to the next row and do the same thing.
View 9 Replies
View Related
Jan 22, 2010
I am having some technical difficulties trying to place data onto sheet2. Sheet2 starts out blank, as sheet1 is proccessed it pastes data to sheet2. I have tried a "For Each" that failed only pasting data into Range "A1" for every found instance.
In the code below, the area colored "Magenta", I need a Loop of some type that as data is pasted into column A on sheet2, it indexes to the next available cell and continues. How do I construct such a Loop or For Each with in the existing For Each?
View 5 Replies
View Related
Feb 1, 2010
I now wonder is it possible to add Text in the row above and below the loop can this be done? Like below?
Image
Test 1 Test
Image/
Image
Test 2 Test
Image/
View 6 Replies
View Related
Sep 7, 2007
I don't get For Each loops. All I want to do is cycle through everywork sheet in the active workbook and lock certain cells. I've got the lock part working right but I have no idea how to effectively preform this on each sheet. From what I've read I think a For Each loop is the best way, but I can figure out how it works.
View 9 Replies
View Related
