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


ADVERTISEMENT

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

Macro That Doesnt Work In 07 But Works In 03

Oct 28, 2008

I've been searching around here to see if anyone had an answer about codes and macro changes from 03 to 07. The closest thing I found was something about lists being tables and such. However, I am still unsure of what the issue might be in my case.

Sub ArrangeColumns()
'
' ArrangeColumns Macro
' Macro recorded 3/7/2008 by ****
'

'
endRow1 = ActiveSheet.UsedRange.Rows.count + 1
Range1 = "A1:O" & endRow1

Range(Range1).Select
Range("A3:O39").Select
Application.CutCopyMode = False
ActiveSheet.ListObjects("List1").Unlink
ActiveSheet.ListObjects("List1").Unlist
Columns("A:B").Select
Selection.Delete shift:=xlToLeft
Columns("I:I").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("C:C").Select
Selection.Insert shift:=xlToRight
Columns("M:M").Select
Selection.Cut
Columns("E:E").Select
Selection.Insert shift:=xlToRight
Columns("L:L").Select
Selection.Cut
Columns("G:G").Select
Selection.Insert shift:=xlToRight
Columns("B:B").ColumnWidth = 11.29
End Sub

So here, the red text is what gets flagged when you run the macro. I seem to be getting a Run-time error '9': Subscript out of range error.

I am not exactly sure what the macro does besides clean up and sort a portion of a worksheet. Also, there is not worksheet called "List1," but changing that value does nothing. Is there an equivalent command to those highlighted in red? Or, perhaps if anyone knows of a place where I may reference these commands myself, that would be great as well.

View 9 Replies View Related

Stop Macro: Button To Hit Or Better Just Some Keys To Hit To Stop It Without Using The Ctrl+alt+del Which Closes Everything

Dec 13, 2006

my excel sheet runs through a lot of calculations, opens Flowmaster, a simulations program, passes on data, receivs data and so on. Is there any way to have a user input to stop the whole simulation. During the first tries I had a lot of break point in my debugger. But now I want to have a button to hit or better just some keys to hit to stop it without using the ctrl+alt+del which closes everything.

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 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

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

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 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

If Else Statement Doesnt Work Well...

Mar 25, 2009

If Else Statement doesnt work well. I just create a code like:

View 2 Replies View Related

Workbook Doesnt Allow Re-saving

Dec 14, 2009

i have a workbook which is mainly use in construction building design... i want users to input ther respective data in that workbook to calculate and design only, but i dont want the user to save the changes they made...

View 9 Replies View Related

Replace Doesnt Look In Values

Aug 8, 2006

I need to replace format for multiple cells. Most of them have formulas and shows values. But Replace wont let me choose to look in values as you can see in the picture:

I'm completely lost. I just did replace with "Look in: values" option couple of hours ago. And now this... Or am I going crazy?

View 3 Replies View Related

Change Formula So It Doesnt Retun #value!

Nov 29, 2008

i have this formula in cell L30. change formula so it doesnt retun #value!

View 2 Replies View Related

Dynamic Range Doesnt Allow New Data

May 5, 2006

I am trying to create a dynamic range for a list that I have. I also want to create an extra empty cell at the bottom or top to type in text that I need only once in some special cases. I named the list and changed the reference to =offset(Sheet1!,$A$2,0,0, counta(sheet1!$A$A),1). The data starts from A2. However when I used Data> Validation>List to see the data, I am always missing the last row in the list.

View 3 Replies View Related

Settings Tab For Data Validation Doesnt Appear

Aug 10, 2006

Im trying to create a validation for cells but the settings tab under data>validation menu isnt there.

View 2 Replies View Related

Delete Row If Value(array) Doesnt Exist In Column

Jun 23, 2009

I noticed there any plenty of examples of delete row if variable EXIST. But im after deleting rows if an array of string items DONT exist. EG. vList = Array("AWilson54", "ADavey99", "MPaterson44").

its for a varying size document and the column to search through is (AN). Does anyone already have something for this? as i mentioned before there are plenty of search hits on if a value exists but not the opposite.

View 3 Replies View Related

Select First Empty Cell... Doesnt Work

Aug 10, 2009

I am using this code to select the first empty cell in column A.

View 9 Replies View Related

Remove All The Rows Which Doesnt Belong To The Current Month

Aug 23, 2009

Need to create a macro to remove all the rows which doesn't belong to the current month, i have attached a sheet but don't know how to create a macro.

View 5 Replies View Related

Sorting Data Doesnt Move Hyperlinked Cells

Sep 20, 2007

Ive spent a few hours adding email hyperlinks to data in column B.

I now want to sort the data in the worksheet into ascending order using the data in column A.

The problem is, none of the hyperlinks from column B sort with the data in column A, they are still in the cell locations I had originally entered them.

View 9 Replies View Related

Spinner Link Cell Doesnt Change When I Copy And Paste The Row

Jan 24, 2010

I have a Piece of Code that copys a Row in my Spreadsheet and Pastes it underneath the original Row.

The Problem that I am having is that within the Row it Copys there is a Spinner in it. I want the Spinner to be copied also but the Cell that the Spinner is Linked to Says as the Original Cell, it doesnt move Down with the New Row Pasted in.

Here is the code that i have written to Copy the Row and Paste it down a Row.

View 5 Replies View Related

Shorten This Query: If The Cell Doesnt Contain Any Info Dont Update

Apr 9, 2007

i have some code (see below). at the part where it says

If ActiveCell. Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value

i want to make this smaller - i have to write this line bout 60 times 3 times over (incresing the numbers as i go ((0, 9..10..11 etc etc and (Skill_32..33..34 etc etc) )) to give you an idea of what im trying to accomplish - im opening and writing to a database but if the cell doesnt contain any info dont update.

Function EditCPS(ByVal StaffNumber As Long)
On Error Goto Err_Handler
Dim SkillsBuilderDB As Database
Dim RSSkillsBuilder As Recordset
Dim MySQL As String
Set SkillsBuilderDB = OpenDatabase(Worksheets("Adding Data").Range("IV1")) ' database location
MySQL = "select * from CPS where StaffNumber=" & StaffNumber
Set RSSkillsBuilder = SkillsBuilderDB.OpenRecordset(MySQL)
With RSSkillsBuilder
.Edit
If ActiveCell.Offset(0, 9).Value <> "" Then
.Fields("Skill_32") = ActiveCell.Offset(0, 9).Value
Else............................................

View 7 Replies View Related

Stop All Macro's

Mar 7, 2007

i need to make a button that Immidiately stops all macro's, Or (and This will maby be little more difficult) Stop all macros on a specific time inserted in 1 cell

the macro's that i want to stop running, are all single macro's which i activate with only 1 button i created.

i like something like this:

if "A1"(this is the cell where i have my updated clock) == A2 (the time i insert here stops the macro's at this time) do stop Macro's

View 9 Replies View Related

Macro To Stop At A Certain Row,

Mar 26, 2009

If you run it now, ctrl t, it produces letters and changes alphabets as it hits a yellow box. If i change or add a yellow box, the letters change fine. The macro works perfectly, but instead of inputting the range or K3 to what ever, i would like to to run this macro and stop once it sees the row with the sort number of 4. ( this changes row postion depending on which sheet im working on, this is the problem).

View 4 Replies View Related

Stop Macro If Condition Met?

Apr 27, 2014

I tried the belwo code to stop Macro if column "A" has the value "#NA" but i am getting error.

{if the Value "#NA" not in the column "A" then macro can continue to run}

[Code] ......

How to rectify the error.

View 10 Replies View Related

Macro To Stop Running At Last Row

Aug 10, 2009

I have a spreadsheet containing a macro to automatically sum values from week to week and display the max/min and average. This is almost fully working. The problem I have is the macro copies the sum formula down the page and doesn't stop at the last row. It always adds one or two extra rows. The formula should stop at row 77 however this does not happen and therefore the sum keeps going. Which in turn, distorts the max/min figures.

View 2 Replies View Related

Get Macro To Repeat Itself Then Stop?

Mar 13, 2012

I'm trying to create a macro to input information into a cell then repeat until the information stops.

So say I have 10 rows of information that fill up A1:D10. in E1:E10 I'd like a macro to insert some data into E1 then go to E2 and do the same until the end and then stop. So since A11:D11 would be blank the formula would just stop.

I actually work on 100's of cells a day and this is for a bigger project I'm trying to put together or I would just drag.

View 7 Replies View Related

Stop Macro If Condition Is Met?

Jun 17, 2014

I need a code that will stop a Macro if any cell in column 3 equals #N/A All i have right now is:

If Selection.Columns(3).Columns = "#N/A*" Then
MsgBox "Incorrect Value"
Exit Sub

View 8 Replies View Related







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