Code Stops During The Loop
Apr 9, 2009
I am using Excel 2003 work PC, and when i run this simple code it stops during the loop, I have had this problem a bit its like something is hitting the esc key or ctrl - break. But no keys are being hit or are sticking.
I have closed Excel and created new work book pasted the code in but it still stops at r = r - 1, haven't done a restart yet.
Sub Macro2()
Dim r As Integer
r = 10
Do Until r = 0
ActiveCell.Value = ("Shut down in " & r)
Application.Wait Now + TimeValue("0:00:01")
r = r - 1
Loop
Application.Quit
End Sub
View 9 Replies
ADVERTISEMENT
Jan 7, 2007
I have the following code that loops through a series of data creating a covariance matrix. For some reason the loop only runs through 4 iterations and then stops. I cannot for the life of me figure out what is wrong. Any thoughts? Truncated example workbook also attached....
Sub covarmatrix()
Dim Series1 As Range
Dim Series2 As Range
Dim i As Integer
i = 0
Sheets("rawdata").Select
Do
Set Series1 = Sheets("rawdata").Range(Range("B3").Offset(0, i), Range("B65536").End(xlUp).Offset(0, i))
j = 0
Do
Set Series2 = Sheets("rawdata").Range(Range("B3").Offset(0, j), Range("B65536").End(xlUp).Offset(0, j))..............................
View 8 Replies
View Related
Jan 22, 2007
verify that this syntax is correct. I just started working with Charts in VBA and it is getting very frustrating. This code should be able to run up to 140 cycles but it quits making charts at 7. Something I'm missing? If it is of any relevance sometimes it errors out at the HasTitle and Legend properties.
Dim myChtObj As ChartObject
Dim rngChtData As Range
Dim rngChtXVal As Range
Dim rng As Range
X = 0
y = 2
z = 1
Do Until y = 8
Set rngChtData = ActiveSheet.Range("A2:A352").Offset(0, z)
View 9 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
Mar 11, 2009
I am trying to create a loop which stops once a cell is empty.
Assuming I have declared proper DIM, my code is
Row=1
Do
Row = Row + 1
SheetName = Cells(Row, "A")
Sheets.Add.Name = SheetName
Loop Until IsEmpty(SheetName.Offset(1, 0))
View 9 Replies
View Related
Mar 17, 2014
Sub DeleteSheetsPlease()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Application.StatusBar = "checking " & ws.Name
[code] .......
This skips "Parameters" and "About", then it deletes a single sheet (that does not match "Parameters" or "About"),
BUT THEN IT STOPS, leaving "deleting [WSNAME]" in the appstatus. It's like it skips the "Next ws" statement altogether after deleting a sheet.
I want it to keep looping on all the sheets, but that delete action seems to kill the looping...
View 2 Replies
View Related
Dec 14, 2007
My code below works great all the way to the end. When the last message box comes up, If the user chooses Yes. I want them to be directed to the sheet "C123". But currently it just stays on the sheet that I named "Home".
What am I missing here?
Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Log")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a Reason Code
If Trim(Me.ComboBox2.Value) = "" Then
Me.ComboBox2.SetFocus
MsgBox "Please enter a reason code and ensure that date is correct!"
Exit Sub
End If
View 9 Replies
View Related
Nov 4, 2009
I have 3 Work books - one (WB1) checks its version number against an online record and if they dont match it opens WB2 which then proceeds to download the updated version copy information from WB1 to its self which it then pastes into WB3
WB1 then gets deleted and WB3 Renamed to the old name of WB1
the issue i have is when i open WB2 using the button that also checks for updates on WB1.
WB2's code seems to just stop when it gets to the point of updating WB3 no error messages or nothing it just stops!
NewMasters(2).xls = WB1
Updater.xls = WB2
NewVersion.xls = WB3
View 14 Replies
View Related
Nov 23, 2008
I'm using Excel 2007 and my s/s is 360000 rows deep.
To cut+paste formulas+formats from one column to another I'm using the following
Sub move_formula_and_formats_from_I_to_L()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("I1", Cells(Rows.Count, "I").End(xlUp))
With cell
If .HasFormula And Not .Offset(1).HasFormula Then
.Cut Destination:=.Offset(1, 3)
End If
End With
Next cell
Application.ScreenUpdating = True
End Sub
The code stops working after 159000 rows and highlights (in yellow) the line:
.Cut Destination:=.Offset(1, 3)
View 9 Replies
View Related
Nov 17, 2006
I have an Access program that acts as a dashboard to open Excel reports. One of the Excel workbooks opens a form in the Auto_Open routine. My problem is that control is never passed back to access after the form opens. It appears that the code stops executing until the userform is closed. How can I open the excel workbook, show the Excel userform, and continue processing my Access code?
Set XL = Excel.Application
With XL
.Workbooks.Open FileName:=FileName, ReadOnly:=True
. ActiveWorkbook.RunAutoMacros xlAutoOpen
End With
XL.Visible = True
' Excel Code
Sub Auto_Open()
Unload UserForm1
UserForm1.Show
' The code get "stuck" right here
End Sub
View 4 Replies
View Related
Dec 8, 2006
I am attempting to show a User Form during the forms Initialize code. I Load the form after extracting data from a SQL Server database. The code that Loads the form is returned to after the form closes. The problem is that when I run through the Initialize code where criteria decides whether the form is actually shown to the user. If the criteria is not met there are no issue, but if the criteria is met, then the form is opened and the rest of the Initialize code is not stepped through. As there are many If Then and a Select Case and a couple With...'s I need the rest of the Initialized code to be stepped through so after the form is closed by the user and the original code which Loaded the form is returned to I do not get an error.
Oh and the big problem is that I am not getting an error message during this, the Load code is actually repeated because when the original code is actually returned to is returned on the Load UserForm code.
Private Sub UserForm_Initialize()
Dim i As Integer, Endofdata As Integer, wsheet3 As Worksheet, wbBook As Workbook
Dim wsSheet As Worksheet, c As Variant, gTotal As Long, Score As String, g3000 As Long
Dim gTotalAdd As Integer
On Error Goto ErrorHandler:
Application. ScreenUpdating = False
'cmbIDCountry.ColumnCount = 2
Set wbBook = ThisWorkbook
Set wsheet3 = wbBook.Worksheets("UpdateDetails")
Set wsSheet = wbBook.Worksheets("Data") ....................
View 2 Replies
View Related
Jul 18, 2014
I usually just use macros to clean up Data from non-excel sources. So I wrote a macro to do this, but the process requires a date to be added, so attempted to do this via an input box. The input box works, but the code doesn't, clicking the button to which the macro is bound spits out an error 91 (object out of bounds?) when it hits the search function right after the inputbox code. Both bits work as intended separately, so I guess it's just some moronic formatting error on my behalf.
[Code] ......
View 6 Replies
View Related
Feb 16, 2009
We're using XP Pro using Excel 07
The background is there are about 40 people who use a Excel based program that contains a pivot and a bunch of other tools that they use on a regular basis, this set of tools has a version number. In this excel workbook, it has a function that looks on the network drive that we have and checks the local version vs the version on our network drive, if its wrong, then the user gets a popup stating your tools are out of date, would you like to update, then they click yes and I have these lines of code
Public Function GetNewTools()
Dim MyFullName As String
'Turn off alerts
Application.DisplayAlerts = False
'Open the new version of tools
Workbooks.Open Filename:= _
"Network DriveUpdate.xlsm"
End Function
The update file has this code that executes on fileopen in the thisworkbook section by calling the following sub
The main issue we're running into is near the very end, the code never makes it to "TEST 2". After the first workbooks close, the code just stops running. No crashes, errors, freezes, anything. It just stops running and never makes it to the second msg box.
View 9 Replies
View Related
May 10, 2006
I have built a small userform with 3 fields. The macro ran OK first few times. Now, when user enters data in the form and clicks OK nothing happens. I found that repeated clicking on OK or Cancel button on form had no effect. I then observed that VBA editor was open and the yellow cursor was displayed on the first code line under cmdOK_click procedure. The code line was also highlighted in yellow. There are no errors to debug and no break points etc. When I clicked the Run (or Continue) icon from the VBA toolbar, the macro completed OK. Question: Why is the macro pausing on the first line and how can I make it run without pausing for no apparent reason.
View 4 Replies
View Related
Jan 4, 2007
I have a Userform that allows a user to select a year eg (2007) from a listbox called yearbox where Yearbox.Value = AddYear. When the user Presses 'OK' the following code runs: ...
View 9 Replies
View Related
Aug 26, 2007
I have a textbox from the drawing toolbar. When someone changes a cell then clicks in the textbox, Worksheet_Change does not run. If they double click in the cell, that's OK I can capture that event and protect the sheet, stopping them clicking in it. But if they just start typing in the cell, I can't capture that. I have seen some API code which captures keypresses, but it is not practical to use as it loops repeatedly. I could lock the textbox and have the user do something to unlock it, but this is a last resort.
View 7 Replies
View Related
Nov 10, 2008
Loop with below code. I have the below code:
View 2 Replies
View Related
Dec 7, 2009
I just need to get it to loop down to the next row
Sub ConcatenateAll()
Dim x As String, rng As Range, cel As Range
With ActiveSheet
Set rng = .Range("B1:AC1")
For Each cel In rng
x = x & cel.Value
Next
.Range("a1").Value = x
End With
End Sub
View 9 Replies
View Related
Nov 15, 2013
I am trying to loop the following code for a total of 15 worksheets without copying and pasting that same code 14 more times for each worksheet. Right now it is only executing the code on the "CAN" tab. Is there a way to make it loop where indicated below?
The 15 worksheets are:
CAN
USA
ASG
Gallia
[Code]......
View 3 Replies
View Related
Feb 24, 2012
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rw As Integer
Dim LastRow As Integer
LastRow = Sheet8.Range("B16:B300").End(xlUp).Row
For rw = 16 To LastRow
[Code] ........
Cannot get the loop to run. I am trying to make it so the loop will stop when it reaches the last non empty cell.
View 6 Replies
View Related
Mar 20, 2014
i need a code that can get parameter like a date and put it in a cell in the same column but different rows .
View 8 Replies
View Related
Apr 5, 2007
I am trying get a set of code to run through the sheets in the workbook... All sheets EXCEPT 1 named "Summary".
How can I code the proper statement? This is my current
Private Sub cmdAddDistribution_Click()
Dim ws As Worksheet
Dim lCount As Long
Dim rFoundCell As Range
'check for selected cash flow
If Trim(Me.cboxCashFlow.Value) = "" Then
Me.cboxCashFlow.SetFocus
MsgBox "Please select a Cash Flow."
Exit Sub
End If...............
View 9 Replies
View Related
May 7, 2008
I have a print code that I want to run on a loop. In column A Sheet UPSLabels, I have record numbers 1 through 94.
I have a vlookup in "C1" of another sheet that looks at the record number in "B1" and pulls in the correct information from UPSLabels based off the matchin record number in column A. I would like my code to print, update the number in "B1" by 1, and keep printing the records until it reaches 94.
I've probably supplied too much information, but the point is, I need the loop to run until "B1" reaches 94.
Sub print_loop()
Dim cnt As Range
Set cnt = Range("L1")
ActiveWindow.SelectedSheets.PrintOut Copies:=cnt, Collate:=True
Range("b1") = Range("b1") + 1
End Sub
View 9 Replies
View Related
Jan 16, 2007
How would I shorten this code using a loop and arrays? It is working correctly but needs to be shortened down.
Sub cr()
Dim j
Cells(1, 1).Interior.Color = RGB(250, 155, 100)
j = 0.09
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.094
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.043
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.05
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.034
Cells(1, 1).Value = j
Application.Wait Now + TimeValue("00:00:02")
j = 0.032.........................................
View 4 Replies
View Related
Feb 24, 2007
I enter 1 record using the form, the next records does not populated correctly. It looks like the code just keeps looping by ....
View 3 Replies
View Related
Jul 3, 2007
Im trying to make some code to go through a list of numbers, and pick the next highest number from the one entered in a form. Then I need to do some processing with that number (i need to create a worksheet with that number as the name, and place that number in a few cells on that worksheet, and the main worksheet, but thats all stuff i think i can do).
View 14 Replies
View Related
Oct 12, 2013
I use this code to delete some rows, how I would modify it to work in another spreadsheet where it would "Loop" through and delete rows that start with "User:Kellcyna" down to where the rolls start with "Numbers", and delete the rolls that start with "Total cost center" down to where the rolls start with "Numbers".
The data can contain up to 50000 rolls at times.
Sub Finally()
Application.ScreenUpdating = True
[a:a].AutoFilter Field:=1, Criteria1:="="
[a2:a65536].SpecialCells(xlVisible).EntireRow.Delete
If [a1] = "" Then [1:1].Delete
ActiveSheet.AutoFilterMode = False
[Code] ........
Here is a sample of the data I need the macro to work on. The rows I need deleted are the rows that are highlighted.
User: Kellcyna STANDARD HOURS BY COST CENTER Date: 09/29/2013 Time: 15:10:04 Page: 10
Comments:
Order Op Emp Post Work ctr Setup Unit Planned Earned Total Actual Actual Actual Total Total Orde C R
# Date SU Unit Plnd Stds Setup Run Tme Brd Tme Prod Run Time E
104527059 0010 00000000 09/25/2013 HSW01 0.000 0.4 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
104523849 0010 00000000 09/25/2013 HSW01 0.000 0.2 HR 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 PP01
[Code] ...........
View 5 Replies
View Related
Sep 3, 2007
I have some code (probably a little inefficient, but still) that should delete any rows that contain nothing in column V. My problem is that it only deletes 1 row at a time:
Dim c As Long
Dim Limit As Long
Limit = Cells(Rows.Count, 11).End(xlUp).Row
For c = 2 To Limit
If Cells(c, 22).Value = "" Then
Cells(c, 22).EntireRow.Delete xlUp
End If
Next c
View 4 Replies
View Related
Apr 3, 2008
I have a requirement where I have to add a row with x columns using vba. this is the code I am using now
k = 17
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord
Sheets("Products").Rows(k).Insert Shift:=xlDown
Sheets("Products").Rows(k + 1).Insert Shift:=xlDown
Sheets("Products").Rows(k + 2).Insert Shift:=xlDown
ThisWorkbook.Sheets("Products"). Cells(k, Prod6Col) = prodetails
ThisWorkbook.Sheets("Products").Cells(k, Unit4Col) = sSeats
isRecord = .NextRecord()
Wend
End If
In this code I have declared Prod6Col and Unit4Col as constants. instead I need to use them as variables like in this modified code. However if i use this code, I get an object definition error.
k = 17
m = 20 (20th column)
l = 18 (18th column)
prodetails = .GetFieldValue("Product Details")
sSeats = .GetFieldValue("Seats")
If isRecord Then
While isRecord
View 4 Replies
View Related
Aug 14, 2014
I'm looking to use a do until/loop code to find a cell that equals a named range ("Clause") that is located on another worksheet, the code I have so far is:
[Code] ......
I've used something similar before and works, but I just cant get it to work. The values that I need to find the match to the ("Clause") cell are directly below the original activecell.
View 6 Replies
View Related
