Loop Stops Prematurely
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
ADVERTISEMENT
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
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
Aug 13, 2007
With the assistance of SHG, Parsnip, Dave & Daddylonglegs I have manage to put together a little app that assist our staff with scheduling dates in a calendar to avoid a specific trend.
Code provided by SHG has worked perfectly; see below:
Public DaysOff As Range
Function datNext(datLast As Date, DaysOff As Range) As Date
Dim datBeg As Date, datEnd As Date
Dim iLastPd As Integer
Dim iDay As Long, nDay As Long
Dim iYr As Integer, iMo As Integer
Though the built in DAY function on the calendar is affecting the dates that are calculated cos the function PD also references this. Whenever I change the year or month on the calendar; it changes the UDF, which is not supposed to be. If the recommended dates are going change all the time; as a result of this, it makes this app useless.
How do I get around this? Also is this what is causing the UDF to recalculate?
View 9 Replies
View Related
Dec 25, 2009
I have a macro to reset certain column that are offset by 13 to the right.
If the test for the loop is ActiveCell = BLANK the macro works, but creates an
un needed column at the end.
If I test for a Blank cell before copying the formula the macro ends prematurely.
I am stumped.
View 2 Replies
View Related
Jan 30, 2007
I do not want ThisWorkBook to close prematurely whenever the specified Activeworkbook is closed without saving because there are other tasks to complete. The Workbook_BeforeClose dun seem to fix it for my case below.
Here’s my situation. I want to copy data from a range of a particular protected sheet in 5 (password-protected) workbooks of a similar format, to 5 worksheets(unprotected) in ThisWorkBook, which is the Master Workbook. The password of the protected sheet is common to the 5 workbooks. The passwords of the 5 workbooks are contained in a sheet in ThisWorkbook. After copying the data from Workbook1 ( 1 of 5), ThisWorkbook are prompted for closure, when Workbook1 is closed without saving. Responding to the prompt to save or not to save, forces ThisWorkbook or MasterWorkbook to break the For-Next loop. By setting Cancel = True in Workbook_BeforeClose, I can stop Thisworkbook from closure, but I will still break the For-Next loop. I have considered the method of copying data from a closed workbook, but I dun think VBA allows copying or referencing a closed workbook which is password protected at workbook and worksheet level.
Here’s my coding :
Sub UpdateMaster()
Dim MasterWB As Workbook
Dim TempWB As Workbook
Dim Source As String
Set MyWB = ThisWorkbook
For i = 1 to 5
Set TempWB = Workbooks.Open (Filename:=WB(i), Password:=password(i) )
TempWB.Sheets("ProtectedSheet").Unprotect Password:="SheetPasswd"
MyWB.Sheets(i).Range(XXX).Value = TempWB.Sheets("ProtectedSheet").Range(YYY)
TempWB.Close False ‘dun want to respond toclose ThisWorkbook else it breaks for-next loop
Next i
Application.Quit.
End Sub
View 9 Replies
View Related
Feb 25, 2009
The macro stops in the last row - it isn't able to select a cell in another sheet than "Menu" (I tested it different variations).
View 9 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
Jan 24, 2014
I have a spread sheet which has a number of protected cells so that they user can tab only on required cells to enter data. At times some of the rows are hidden however pressing "TAB" will continue to tab through on the cells within the Hidden Rows. Is it possible to only TAB through Cells which are visible?
View 1 Replies
View Related
Apr 13, 2014
i ran some tests and it was working good only doing folders with ~100 files. i tried running the macro on all the files (~70,000), but I ran into problems with the computer going into standby while I was away. I tried running a batch of 3000 files and it completed, but only got up to around ~400 out of ~3000 records. Maybe, it stops cause of code related inefficiencies?
Here's the code:
macro_forexcelforum.txt
View 1 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
Dec 2, 2011
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
View 5 Replies
View Related
Dec 10, 2013
I have a macro stored a module at Personel workbook and i am using personel workbook as a hidden one. when i assign a shortcut below macro it is not working but whrn i am press F5 at VBA Editor it is working.
Here is the code:
Code:
Sub Transferdata()
On Error GoTo err1:
Application.DisplayAlerts = False
[Code]....
View 7 Replies
View Related
Mar 12, 2009
I have a problem with the following code
Dim strProcLine As String
With ActiveWorkbook.VBProject.VBComponents(ActiveChart.CodeName).CodeModule
' MsgBox ok
' adaugat procedura goala
.CreateEventProc "Calculate", "Chart"
'MsgBox ok
strProcLine = "Format_Chart"
' MsgBox ok
.InsertLines .ProcBodyLine("Chart_Calculate", 0) + 1, strProcLine
' MsgBox ok
End With
' MsgBox ok
This is part of a larger macro, wich makes 2 pivottables and for each PT a chart, and for each chart i create an even procedure (Chart_Activate) wich calls a procedure to format the chart. Tha macro is alocated to the click even on a button in the sheet where i get the information from.
If i run the macro(click the button) with Microsoft Visual Basic Editor opened all goes ok. But if i close Microsoft Visual Basic Editor and then run the macro it stops right before .CreateEventProc "Calculate", "Chart" of the first chart , and i can't understand why. No error mesages delivered, nothing.
View 9 Replies
View Related
Mar 23, 2007
I have figures and text that I have been trying to pick up using concatenate but when I get so far the workbook won't calculate. I have numbers plus text like this -24A They start in R8 then U8 then X8 then AA8 then AD8
In AI8 is a count of these numbers between R8 and AD8 (Maximum of 5)
My initial thought was to concatenate(r8"/",u8,"/",x8,"/"aa8,"/",ad8)
Which works but if you only have one number in R8 it looks like this -
24A///. I tried embedded If statements and concatenate and got the calculate problem. A two number example of what I would like is 24A/26B.
View 3 Replies
View Related
Apr 11, 2013
Problem with the attached Range Overrun.xlsm.
Form opens with set number of rows. End User then enters figure into C3 of how many row are to be added.
VB:
Option Explicit
Dim c As Range
Dim j As Integer
[Code]....
Macro works correctly by adding the number of rows listed in C3. It SHOULD then "name " various cells in the original and new rows.
BUT the Macro goes ON to name cells in rows BELOW the ones that are added.
So on the attached worksheet the original rows were 6 - 9. Rows 10 - 12 were added, but the macro names the cells from Rows 6 - 14.
View 2 Replies
View Related
Feb 6, 2008
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies
View Related
Jun 10, 2008
I want to make the worksheet protected, however, when I protect the worksheet the macro will not run.
View 14 Replies
View Related
Jan 3, 2010
I work in Excel with a VBA script. The purpose of my script is that every 5 seconds a procedure will be executed. This works well. If my workbook is not active, it does not work. Or when I work with another workbook. Or when I work with another program. Then the script will stop to execute. It looks like a break. I have tried several scripts that have the same effect. I want the script continues normally with execution.
View 3 Replies
View Related
Sep 28, 2011
I've had a macro running for ages, and just found it bugs out when there are spaces in a sheet name.
The user selects a destination cell as part of a form, which passes the variable to the following steps:
Code:
looperx = 0
Do
looperx = looperx + 1
Loop Until Mid(celldestinationstring, looperx, 1) = "!"
Sheets(Left(celldestinationstring, looperx - 1)).Select
The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9
This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.
View 8 Replies
View Related
Jun 17, 2013
I have two subroutines. One subroutine updates some figures on spreadsheet 1, and then calls a subroutine that updates some figures on spreadsheet 2. Spreadsheet 2 is supposed to save and close, and return to spreadsheet 1, but it only gets as far as opening spreadsheet 2 and updating the figures. These are the two subroutines:
Code:
Sub UpdateLegalAndMeans()
Dim wBook As Workbook
Dim count As Integer ' counter for counting down backlog figures
[Code]....
View 4 Replies
View Related
Apr 7, 2008
I've a userform style progress bar which stops updating if i use application.wait within my code like this:
UserForm1.FrameProgress.Caption = "0%"
UserForm1.Label1.Caption = "Starting Process.."
UserForm1.LabelProgress.Width = 0
UserForm1.OkButton.Visible = False
DoEvents
Application.Wait (Now() + TimeValue("0:00:01"))
View 9 Replies
View Related
Oct 2, 2008
Here is the find / replace vba I am using.
Sub Replace()
Cells.Replace What:=Chr(19), Replacement:="-", LookAt:=xlPart
Cells.Replace What:=Chr(24), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(145), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(146), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub
It makes the replacements up to line 37, then stops. If you then delete the first 37 rows and re-run the macro, it again performs replacements, but only on the next 37 rows.
I would like the macro to do the whole sheet...
View 9 Replies
View Related
Oct 14, 2008
My Workbook contains the following macro in Sheet 1, which displays a pop-up calendar in L15 when that cell is selected:
Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.Select
Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("L15"), Target) Is Nothing Then
Calendar1.Left = Target.Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub.........
View 9 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
Jun 13, 2009
I have a command button on worksheet2 of my spreadsheet, which has a macro assigned that will take the user to worksheet1 when it is clicked.
My problem is that when I hide worksheet1 using xlSheetVeryHidden - the maro seems to die and the command button no longer works.
View 9 Replies
View Related
May 16, 2006
I have joined out of desperation relating to a problem with an Active X control inserted into a userform. This control accesses and returns data from an instrument on the RS232 PC comms port. All works well until I try to save, copy or print the workbook using usual VBA code, then mysteriously the code just stops, and Excel needs to be restarted again. It seems to just get lost!
View 2 Replies
View Related
