Code That Gets Stuck In The Loop And Will Not Stop Unless I Press The Esc Key

May 26, 2007

I have the following code that gets stuck in the Loop and will not stop unless I press the Esc key:

Sub WIP()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long
Dim newRow As Long
Set ws1 = Sheets("PAYCALC")
Set ws2 = Sheets("WIP")

Application.ScreenUpdating = False
With ws2
.Range("A2:C" & .Range("A2:C2").End(xlDown).Row).Clear
End With

x = 10
lastrow = ws1.Range("C5").End(xlUp)
Do
newRow = ws2.Cells(65536, 1).End(xlUp).Offset(1, 0).Row
ws2.Cells(newRow, 1) = ws1.Cells(x, 2).Offset(-2, 0).Value
ws2.Cells(newRow, 2) = ws1.Cells(x, 2).Value
ws2.Cells(newRow, 3) = ws1.Cells(x, 2).Offset(3, 0).Value
x = x + 21
Loop Until x >= lastrow

There's a twist to this. I have the same identiclecode in a different module. The only difference is ws2 is different and there are 7 newRow's. Other then that the two are the same.

That one works just fine without getting stuck. It will go through 200+ sets of records and stop when it reaches the last one.

The one above will not stop even though I just put in three records on ws1 for testing.

View 9 Replies


ADVERTISEMENT

Loop Code Does Not Stop

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

VBA Code To Activate/Press Button On Web Page

Jul 6, 2009

VBA Code To Activate/Press Button On Web Page. press a button on web page using VBA

View 4 Replies View Related

Automatically Refresh Without Having To Click Or Press Enter To Get VBA Code To Work

Feb 25, 2013

I have some VBA code which hides columns based on a cell value. The cell value changes according to which option button is selected. The code works but not when the button is selected and the cell value changes. It is necessary to click elsewhere in the sheet or press Enter to get the columns to hide. I want it to do it automatically as a user wouldn't know to click elsewhere.

The working code is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("N5").Value = 2 Then
Columns("O:R").EntireColumn.Hidden = True
Else
Columns("O:R").EntireColumn.Hidden = False

End If
End Sub

N5 is the linked cell for the option button.

View 4 Replies View Related

Getting Loop Until IsEmpty To Stop

Feb 21, 2012

I'm trying to get a looped function to continue down a worksheet until a specific column has no data in it. My code is below, and it works great for doing what I want it to do (copy a row and continue pasting it every 3 rows down until the row where Column E has no data in it) but it doesn't stop at the end of the data:

HTML Code:
Sub Test2()
' Select Row 11, *first line of data*.
Rows("11:11").Select
Application.CutCopyMode = False

[Code] ....

View 6 Replies View Related

Loop In Macro Doesnt Stop!

Mar 18, 2008

What I am trying to do is create a chart for each row in my raw data - the rows does change on a week to week basis as we are comparing stores

My raw data is: (I would post data although its too wide
Column A - New Store
Column B - Like Store
Column C:N - New Store Sales
Column O:Z - New Store SOH
Column AA:AL - LIke Store Sales
Column AM:AX - LIke Store Sales

I am trying to loop my code so that it creates a new chart for each row....although the loop keeps going and it doesnt move down a row each time

my code is:

Range("A1").CurrentRegion.Select
rnum = Selection.Rows.Count
Range("C2:N2").Select
For i = rnum + 1 To Rows.Count
If rnum >= 1 Then
Charts.Add
ActiveChart.SetSourceData Source:=Range("'CHART RAW DATA'!$C$2:$N$2").................

View 9 Replies View Related

Loop In Macro Doesnt Stop

May 14, 2008

im trying to use a loop to open each file within that folder. My problem is it keeps looping and only opens the same file -

my code is:

'Enter names of files within C:Temp in activesheet
Dim myDir As String, fn As String, txt As String, myList
myDir = "C: emp"
fn = Dir(myDir & "*.xls")
If fn = "" Then Exit Sub
Do While fn ""
txt = txt & vbLf & Left$(fn, InStrRev(fn, ".") - 1)
fn = Dir
Loop

View 9 Replies View Related

Out Of Stack Space/can't Stop Loop

Jul 25, 2006

I've got this code to display a certain cell from multiple files, and functionally it works, but I get a out of stack space error after it runs for a while. I'm sure this is because the loop doesn't know when to stop, which I thought would be solved by having " Do Until sBook = "" " instead of Do; however, then the code will not run at all.

Private Sub Worksheet_Calculate()

Dim sBook As String
Dim sFilePath As String

On Error Resume Next
Application.EnableEvents = False

Roe = 3
Do

View 3 Replies View Related

Stop Loop When Button Clicked

Dec 11, 2006

I have a form with two buttons, one is to start downloading, one is to stop it, a sub is called when start buuton is clicked, there is a DO LOOP, I hope the DO LOOP stop when I clicked STOP button,

View 9 Replies View Related

Press Command But And Execute The Code Assigned To Another Command Button

Apr 14, 2009

Is it possible that when you press a command button, that the first thing it does is to execute the code assigned to another command button (IE in another sub).

View 9 Replies View Related

Loop Is Supposed To Stop When The Cell That Is 4 Columns To The Left

Aug 29, 2008

The loop is supposed to stop when the cell that is 4 columns to the left, in the workbook named "Complete DSS", changes numbers.

/ This is the cell ref. / This is the original
Do While ActiveCell.Offset(0, -4) = ThisTape

The number will stay the same for a while until it changes tapes, then it Should stop when they are no longer equal.

It used to work, but maybe some recent changes have messed up the function.

Sub Compare_printout_2_WDMS()
Application.ScreenUpdating = False
Dim ray, sht As Worksheet, Exist As Boolean
Dim BookA
Dim I As Long
I = Worksheets.Count

AreaNum = InputBox("¿Que es el Area?")
ThisTape = Range("C8").Value
ActiveWorkbook.SaveAs Filename:="C:Documents and Settingssmith27DesktopArea 319 printoutsArea " & AreaNum & " " & ThisTape & ".xls"

Columns("A:A").insert
LR = ActiveSheet.Range("B65536").End(xlUp).Row..........................

View 9 Replies View Related

Start/Stop Loop Based On Condition/Criteria

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

Run Loop 10 Times Then Stop Using A Timer Or System Clock To Restart

Jul 2, 2014

How do I run this loop 10 times ....Then using a timer or system clock to restart it.

Code:
Public Sub Workbook_Open()
Dim ws As Worksheet
Do
For Each ws In ThisWorkbook.Worksheets

[Code]....

View 1 Replies View Related

Code To Stop Scroll

Dec 25, 2008

is there anyway to stop screen scroll?

so that you can only see the visible cells at opening of workbook?

View 14 Replies View Related

Stop Code For ThisWorkbook

Nov 5, 2009

I use a code in ThisWorkbook to always open on sheet 1 when opening up the excel spreadsheet.Is there a code that I could put in to ThisWorkbook that would do a check that the workbook was e.g. "WorkbookA". If it wasn't then the start up code would be cancelled/ignored.The reason for this is that I am copying this workbook whenthe user has entered details in to it and so I do not want this code to run in the saved workbook, that will have a different name.

Workbook A is the master copy or template

View 7 Replies View Related

Code Stop After Sheet 180

May 6, 2008

I have the below code (pieced together and donated by other users on the forum)

Application.Calculation = xlCalculationManual
'
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim Ctr As Long 'set ctr for Invoice
Dim wsData, wsInv As Worksheet
Dim Ibk, Rbk As Workbook

Set Ibk = Workbooks("Invoice.xls") 'Invoice as Ibk
Set wsInv = Workbooks("Invoice.xls").Sheets("Invoice")
Workbooks.Open Filename:="\cmc-dc01usersdcMy DocumentsProjectsRemake GoodyearRentCharge.xls"
Set Rbk = Workbooks("RentCharge.xls")
Set wsData = Workbooks("RentCharge.xls").Sheets("Sheet1")
With wsData........

View 10 Replies View Related

Stop Code When Value Reached

Oct 15, 2006

I have written a code but i can not make it stop where i want it to stop. I would like the code stop when cells(31,4).value reaches 0. Pls refer to attached sheet for a clear example.

The code is as follows


Sub production2()
If Cells(35, 4).Value > 0 Then
Cells(31, 4).Value = Cells(35, 4).Value
For j = 5 To 16
If Cells(36, j).Value < Cells(29, 4).Value Then If Cells(31, 4).Value > Cells(29, 4).Value Then Cells(37, j).Value = Cells(29, 4).Value
If Cells(36, j).Value < Cells(29, 4).Value Then Cells(37, j).Value = Cells(29, 4).Value
Cells(31, 4).Value = Cells(31, 4).Value - Cells(37, j).Value
Next j
End If
End Sub

View 9 Replies View Related

Stop Code After Certain Time

Jun 14, 2008

Trying to get my code to loop for one minute. Have tried this code (after searching through a few threads):

Sub LoopUsingTime()
x = 1
EndTime = Now + TimeValue("00:01:00")
For lCount = Now() To EndTime
Sheet1. Cells(x, 1) = "the time is now " & Now()
Sleep 5000
x = x + 1
Next lCount
MsgBox ("loop finished")
End Sub

View 2 Replies View Related

If Statement To Stop Dim Code From Running

Apr 29, 2014

Attached is an example of the program. The purpose of this program is to allow someone with very little knowledge about the information to enter data. The first tab labeled "Instructions" is will have two macros assigned to the buttons at the bottom of the page. The first macro labeled "Move Data" will move data will clear data in the shaded cell on the "Entry Form" sheet and fill those cells in with '----. The second macro labeled "Generate and Save" will save a master copy of the program, save a historical copy of the program by date, and print/save a pdf copy of the "Reports" sheet.

Each one of the historical (the last 8 sheets of the program) sheet receives data from the "Entry Form" and then moves the data by date into the table for archiving purposes.

If you look at the code for the "Entry Form". I have a couple of subs. The first being Worksheet_change. the first section of this code is to force certain cells to be uppercase. This part of the code is working fine. The second part of the code is the Dim MyValues. This part is working fine also, but...

My issue is... When I run the move data macro from the "Instructions" sheet the Dim MyValues reads these as changes to the cells and wants to push the data to the historical sheets.

What I would like is an IF Statement above the Dim MyValues part of this code that stops the Dim MyValue part of the code running if those values are blank, 0, or have '---- entered into them.

Test.xlsm

View 3 Replies View Related

Stuck With Array Formula?

Jan 19, 2014

I am using the following array formula "=SUM($I$35:$I$1000*($G$35:$G$1000=$B$10))" which works fine but if there is nothing to sum it returns "0", but I would prefer a blank cell if there is nothing to sum.

View 3 Replies View Related

Excel Gets Stuck On A Cell?

Aug 26, 2013

I have a client that enters a lot of data into various spreadsheets, we recently upgraded her computer and along with it upgraded Office to 2013. Since that has happened she has experienced some issues with navigating cells in Excel. What will happen is she will be typing than tabbing or using the arrow keys and all of a sudden the active cell will get stuck on one of them. Arrow keys do not move the cursor to the next cell and the only way for her to fix it is to alt tab to another spreadsheet and then back to the one she was working on. Once that is done it is working fine.

There seems to be no rhyme or reason to when this happens to her. I have re-installed (used the big one that re-downloads from the web), it has no add-ins on either that are conflicting and I know it is not the keyboard since when I log in through TeamViewer while it is happening I am also unable to move with the arrow keys.

View 10 Replies View Related

Stuck In Design Mode?

Apr 29, 2008

For some strange reason the workbook I have been using on a daily basis has suddenly decided to go into design mode and stay there. No matter how many times I click the design mode button in and out it always stays in and I can't use my buttons.....it just selects them?

View 9 Replies View Related

VBA Code Won't Stop Users Using Spreadsheet After Certain Date?

Jul 28, 2014

The code that I'm using is supposedly not allow spreadsheet to be opened after a certain date, or does not allow a user to press any of the command buttons and comes up with a message saying the spreadsheet is closed for new entries.But is not working for me.

I placed the Code in the ThisWorkbook module.

[Code].....

View 8 Replies View Related

How To Stop Running The Code When Pressing Backspace

Jun 22, 2009

I have the following code to add dash mark to TextBox1 value depending on some condtions (mentioned in the code), the code works perfectly, but when we use Backspace Key to clear some charactors it is conflicting with the code running when changing Textbox1 value.

View 11 Replies View Related

Code To Stop Macro And Move On If No Value Found

Jul 2, 2013

I have created a few macros and once I was happy that they worked independently I joined them to run as one macro in several steps. I have just discovered a bug ...

The macro filters on a column and copies and pastes that range into another sheet.

HOwever, sometimes (as I have just discovered) when it filters, there will be no data against the value it is looking for.

I need to say, if no value, move to next step.

I.e. here's my code. It's looking for ASIA and it will copy and paste all rows with Asia to another page. It then does the same for EMEA. Thing is, in my example just now, there was no EMEA data and some times there may be no ASIA data so I need to build in some rules that if it does not find either of these it continues to finish macro or pops up and says 'NO ASIA found, CLick to continue' or something?

Selection.AutoFilter
ActiveSheet.Range("$A$12:AA" & lngLast).AutoFilter Field:=27, Criteria1:="ASIA"
Range("A13:Z" & lngLast).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("ASIA DETAIL ").Select
Range("A19").Select

[code]...

View 1 Replies View Related

Range- Code Stop When It Finds The First Match

Aug 22, 2008

Sub do_it()
Dim x As Range
Set x = Columns(4).F ind("*test*", lookat:=xlWhole)
If x Is Nothing Then GoTo 999
r = x.Row
Cells(r, 1) = Cells(r, 1) & " (W)"
999
End Sub

I need the above code to continue down column 4 and add the " (W)" to the entire column.
The code stop when it finds the first match.

View 9 Replies View Related

Stop Code Firing On Selection Change

Nov 22, 2006

how I can disable an InputBox? I've got some code that whenever someone selects a cell in a specified range, an input box pops up (running a macro) - this can get annoying sometimes though if just browsing. Does anyone know a macro where I can "disable" this?

View 4 Replies View Related

Stop Screensaver Activating While Code Running

Jul 10, 2007

I have a file that shows a rolling screen and updates information every 30 mins. I want to be able to stop the screensaver from activating while this file is open.

I have found this code on the forum that does this, how do I call this function from another module? ...

View 4 Replies View Related

Stuck With Dropdown List Data?

Mar 23, 2014

I am currently stuck with a drop down list problem. I am trying to make a benchmarking solution for a group of 20 stores. All stores sell products uniformly and their sales are displayed in percentages of the cumulative total. Currently there is a benchmarking page where all stores sales per product is displayed, however I would like the ability to select specific stores data from a drop down list. I have pictures displaying the basic concept of what I want to achieve.
stores.PNG

On the left a list of products and the quantities sold in percentages per store for benchmarking purposes.

What I would like to achieve relates to the drop down list on sheet 2.

store 1.PNG

Here store 1 can see its sales in percentages, and has a drop down list to compare to different stores. If store 3 is selected I would like the data to be pulled into that column from the benchmarking sheet and the same goes for the other stores of course.

View 7 Replies View Related

Getting Run Time Error 91 - Stuck With UserForm

Aug 2, 2014

I'm getting a 'Run Time Error 91' saying that 'Object Variable or With Block not set'.

Error lies here:

Code:
Me.lstCategpry.Column = CustRec.GetRows(Fields:=Array("name"))
Userform Initialize:
Code:
Private Sub UserForm_Initialize()

[Code].....

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved