Endless Loop & Macros
Apr 27, 2009
I've used a loop in one of my macros and the loop goes on endlessly. I've been trying to come up witha way to overcome this problem but with no luck till now. knowing that I'm new to VBA i decided to turn to the big guns at mr.exxcel to help me out. Here's the code i have. Any alternatives to this would be VERY VERY VERY USEFUL!!!
Sub smalloops()
Dim i As Integer
Dim compliant As Integer
Dim noncompliant As Integer
compliant = 0
noncompliant = 0
For i = 1 To 60
Do Until Cells(i, 2) = ("RECOMMENDATIONS")
Do While Cells(i, 3) 0
If Cells(i, 7) = 1 Then
compliant = compliant + 1
ElseIf Cells(i, 7) = 2 Then.................
View 9 Replies
ADVERTISEMENT
May 16, 2007
Why does my Excel hang on this
While TempWaarde1 <> ""
TempWaarde1 = Cells.Value(Posi, 1)
TempWaarde2 = Cells.Value(Posi - 1, 1)
If TempWaarde1 <> TempWaarde2 Then
ListBox1.AddItem TempWaarde1
End If
Posi = Posi + 1
Wend
What I'm trying to achief is to fill a listbox with all values in the first column of the active sheet, but avoiding double entry's if they're on the row after the 1st one. When I execute this in my Userform_Initialize, Excel hangs like the while-wend loop is never finishing, while i'm sure there is no more data in the 1st column after 200 rows or so.
View 3 Replies
View Related
Dec 21, 2006
I have the following VBA in the attatched spreadsheet (seperated so I could upload)
The only problem that I have is that I cannot stop the loop, it constantly keeps going round in a circle.
What im wanting to do is when the Date checked is the End date and the End Time has been checked, to move onto the next row and do it again until it gets to row 40.
Sub Calculate()
Dim CellNo As Integer
Dim CurrentDate As Date
Dim StartDate As Date
Dim EndDate As Date
Dim theDate As String
Dim c As Range
Dim i As Integer
Dim addDate As Integer
Dim DaysHoursSick As Date
Dim StartWorkTime As Date
Dim EndWorkTime As Date
Dim endmarker As String
I've only got this VBA working really under Wednesday so I can make sure it is right before adding it onto the other dates,
View 4 Replies
View Related
Jun 15, 2009
Having another problem with the same macro that has been giving me trouble for a week now. Now, the macro will run, but after it finishes, excel stops responding. Is this because the macro continues to run indefinitely.
View 3 Replies
View Related
Oct 25, 2011
I have a multi-sheet workbook. The first sheet is a summary of results from the rest of the workbook. I would like this summary sheet to auto-refresh itself each time the sheet is activated. The VBA code triggered by the Worksheet/Activate event feeds some parameters out to other sheets, then copies back the results to the summary sheet of the workbook.
While doing so, it keeps "reactivating" the first sheet, causing it to get into an endless loop that is triggered by the Worksheet/Activate event. Essentially, I'd like the Worksheet/Activate event to go dormant for 15 seconds or so each time it is triggered.
View 3 Replies
View Related
May 14, 2014
I have a very long routine, looping through 35,000-ish rows several times to detect and delete unwanted items. Occasionally, it gets locked into an endless loop and CTRL+Break will not stop it - I have to kill Excel through the Task Manager.
How can I find out where the endless loop is so I can detect what's causingit and fix it?
View 6 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
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
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
Nov 6, 2008
This code calculates a value for column K when you punch in a number in column J (in any rows 17 to 116, separately). The first problem is that when you delete a value out of J, the value stays in K for some rows (where it should only contain a value if J does). I'm guessing it has something to do with the triggering event that I put in bold. Secondly, I need to be able to protect row K at all times from deletion or entering another value. It ONLY should calculate a value based on J. So I need to use the module (below the code) and I want to integrate it into the code or call it in a module. It's not working properly because it prevents ANY and ALL changes, even the ones caused by new values entered in J (which should be allowed). It should only prevent deletion or manually entering a value on the worksheet. So the bottom line is that when you enter a value into J, unless column E says "Annuity" it will calculate using the formula given. If you delete J, K should go away as well. And at no time can you delete or manually change K. And when you switch values in E, it must reloop to check if it says "Annuity" or not, and if not, it must clear J (which clears K).
And lastly, this thing is brutally slow, so maybe there's an easier way to write it. Sorry for all the detail, but I think it's an easy fix for someone who knows programming, but i don't know how to put it together! NML Inventory is the Activesheet.
View 6 Replies
View Related
May 23, 2006
I want to be able to run this many times looping through different file names. The only thing that changes between file names is the number.
Application.Run "'C:Documents and Settingspadi4545DesktopProjectRuns10.xls'!main"
ActiveWorkbook.Save
ActiveWindow.Close
View 3 Replies
View Related
Aug 8, 2007
I have some code which, which I have modified it to create check boxes for all rows of data, but when I run it, it begins to wipe out the data, and create an endless column of numbers and check boxes. Here is the code:
Sub SelectCell()
Application. ScreenUpdating = False
Dim Z As Range
Set Z = Cells(1, 1).EntireColumn. Find("*", SearchDirection:=xlPrevious)
For i = 2 To Z
Range("A" & i) = i
Set cll = Range("A" & i).Offset(0, 1)
For Each shp In ActiveSheet.Shapes
If Left(shp.Name, 8) = "CheckBox" And shp.TopLeftCell.Address = cll.Address Then
shp.Delete
Exit For.......................
I have tried to set it to recognize how many rows are filled with number, give that to variable Z and then apply the code but it simply does not work.
View 7 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Jul 28, 2008
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
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
Jul 23, 2005
What is the commands or script for deleting a macro automatically using
another workbook macro.
View 9 Replies
View Related
Jul 14, 2008
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 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
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
View Related
Dec 14, 2009
I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
View 11 Replies
View Related
Apr 13, 2007
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
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
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
