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
ADVERTISEMENT
May 8, 2014
I have a 2010 excel sheet containing 14 columns and 45082 rows in total. I am quite illiterate when it comes to writing macros but I know that what I need can be achieved with a set of codes.
To be more clear, I inserted two tables below. The first one represents the current data structure, and the second one is the way I want my data to look like.
Current data structure looks like
Variable 1
Variable 2
Variable 3
[Code].....
View 9 Replies
View Related
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
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
Nov 7, 2006
I have a spreadsheet which is going to be populated everyday from figures from another spreadsheet using autofilters and the very useful SubTotal worksheet function. I'll attach a copy of the work in progress, also there will be a macro in here which Everytime the user presses update, it copies a range of cells and formatting and pastes it to the next available cell on the right. I've also drawn the movement of the cells i'd like The reason being, is that its going to give day to day, up to date figures from a datasheet.
The problem i have is that when i come to populate the grid which has been copied, i need a calculations to populate each cell in the Less<1week column, then when it gets to the bottom, move Back to the top, and accross to the right 1, and then populate down again, and so on until the end of the grid in question, i dont think i can use a for/next loop becuase it will be a different column address everytime. I dont really like loops, but i feel this needs it.
View 6 Replies
View Related
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
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
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
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
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
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
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
Dec 20, 2009
how to create a loop that grabs the ( t'th ) cell and prints into columns on the spreadsheet adjacently that cell and the ones before it (total = t) and repeats it consecutively until there are no more cells to transcribe into columns. For example - I have n=100 cells in a column that need to be placed into n-t columns (where t = 5 for example).
See image below for clarification, code to follow:
As you can see I need it to fill out in the yellow highlighted cells the rest of that pattern. Right now it only does it for one column, but every time I try to get it to do the rest of the columns, it prints the same first column over and over again (I'm having some severe mental block with implementing the correct loop code).
For clarification also, the user selects the t'th cell and it prints that cell and the previous (3 in this case)... and then it shifts down one cell and prints those 4 cells into the next column - which is what I want it to do.
Sub populate_range()
Application.ScreenUpdating = False
Dim rowoffset As Integer
Dim offsetnumber As Integer
rowoffset = Range("F2")
offsetnumber = Range("F2")
Range("d5:iv5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Sheet1.Select
Range("b4").Select
ActiveCell.Offset([offsetnumber], 0).Select
Range(ActiveCell, ActiveCell.Offset(-([offsetnumber] - 1), 0)).Select
Selection.Copy............................
View 9 Replies
View Related
Feb 19, 2010
I need a macro that will look - this is hard to explain so will prob do wee diagram too but basically the macro nees to start looking at sheet2 A5 (the numbers start at A5 before that is text) see if it is greater than Sheet1 ColumnA & Less than Sheet1 columnB if the number is then I need Sheet2 ColumnD inserted into sheet 1 ColumnG. There is the text END on all columns on both sheets for the loop, the reason for the loop of course is there could be 100 lines in sheet2 ColumnA & I must look at all lines in sheet1 to see if it is greater than columnA and Less than ColumnB and if it is populate Column G.
Sheet1
Column A ColumnB ColumnG
Row 11 4000 4999
Row 12 5000 6099
Row 13 6100 7400
ETC
END END
Sheet2
ColumnA ColumnD
Row5 5130 134.58
Row6 6900 6400.00
ETC
END END
After the macro the result would be
Sheet1
Column A ColumnB ColumnG
Row 11 4000 4999
Row 12 5000 6099 134.58
Row 13 6100 7400 6400.00
END END END
Sheet2
ColumnA ColumnD
Row5 5130 134.58
Row6 6900 6400.00
END END
View 9 Replies
View Related
Jul 6, 2009
I have 12 columns of numeric data in cells AA80 through AL80. Beginning with the cell on the far right (AL80), return the value if >0. Otherwise, proceed to the cell to the immediate left (AK80) and return the value if >0, otherwise proceed to the next left cell...all the way to AA80. I tried this by nesting IF functions, but have too many IF functions to nest. Lintcoop posted a similar thread in 2008 but with text data.
View 2 Replies
View Related
Mar 16, 2013
I am trying to find any way possible to freeze left side 3 columns and 3 right side visible columns on the screen. and the rest columns in between these freeze columns scrollable on left or right arrow keys as normal.
View 4 Replies
View Related
Apr 7, 2008
Is it possible to re-order entire columns based on cell values? For example, row A gives values of 1 thru 10 to the first 10 colums. I would like a macro that reorders the entire columns based on that value.
View 6 Replies
View Related
Dec 22, 2009
I recently installed 'windows 7 ultimates' , and after i insalled Excel 2007 .. the columns start from right to lfet (..., C, B, A), I want to reverse it to start from left to right (A, B, C, ...), I don't know whether the new ystem 'windows 7 ultimates' has some effect on it to be modified like this. moreover, the language of system is English. so how to solve it
View 3 Replies
View Related
Jan 7, 2010
Does anyone now if it's possible to freeze left and right hand columns simultaneously eg. cell C3 above and left AND cell DB3 above and right?
The columns on between would then scroll left or right, until the static column was reached?
View 14 Replies
View Related
Feb 20, 2014
I have a big table with a lot of data in it. I basically want to get rid of all of the empty cells, so that all the data that is there for the one person is all next to each other.
All the people's names are in a row for themselves so the empty cells would have to move to the left.
View 3 Replies
View Related
Oct 28, 2009
I'm trying to write some code to Hide columns if the first 3 characters of cells in a range equal the contents of another (formula equivalent would be something like this =IF((LEFT(A2,3))=A1,"HIDE COLUMN", "SHOW COLUMN"). I have gotten this far but cant get it to work;-
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, cell As Range
On Error GoTo ErrHandler
Set r = Me.Range("B7:CG7")
Application.ScreenUpdating = False
Application.EnableEvents = False
Row = 1
col = 1
For Each cell In r
If cell.Value = "" And Left(cell.Value, 3) = cell(Row, col).Value Then
cell.EntireColumn.Hidden = True
Else
cell.EntireColumn.Hidden = False
End If
Next
ErrHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
View 9 Replies
View Related
Sep 16, 2013
I have a column that is giving unwanted value . dont know the reason as that excel file has been created by some other guy and I just started working on it .
My Question is how to move to 2 cells left(A for example) from from that unwanted value column. and check if
A is equal to cell above it , means B Cell(Row above A but same column).
As my excel file is totally based on Forms, Macros, I am not quite familiar with macros.
Is there any way to put if condition in one cell (column) and drag it all the way down which should work for all the values in these 3 column.
And also if A=B then I want to make that unwanted value cell="".
View 1 Replies
View Related
Sep 4, 2013
I have a worksheet in excel that I have hidden columns from AA right to the end. Which gives the user a nice worksheet with everything to the right of AA hidden in blue. What I would like to do is now centre the worksheet in the screen and have the same hidden blue effect to the Right of the worksheet in this blue.
View 1 Replies
View Related
Jul 12, 2014
I need a formula for left coulmn lookup (using index and matching function) and lookup array has duplicate value.
View 1 Replies
View Related
Feb 15, 2012
Data is input into my spreadsheet. It is not always in the same cell, and is not always the same length vertically. I want the user to select the top left of the data and then automatically do text to columns. Here is my code that is not working! :
Sub TextToCol2()
Dim rng As Range
Set rng = Application.InputBox(prompt:="Select a cell", Type:=8)
ActiveSheet.Range("rng:A" & Range("A65536").End(xlUp).Row).TextToColumns DataType:=xlDelimited, ConsecutiveDelimiter:=True, Space:=True
End Sub
View 3 Replies
View Related
Jan 30, 2007
=IF(AND(EOMONTH(TODAY(),COLUMN()-2)<TODAY(),(TODAY()<EOMONTH(TODAY(),COLUMN()-1))),Sheet2!C10,"NO VALUE")
The above formual resides in each cell in row 1 column g inclusive.
The question is when I move over columns the previous column with figure is wiped out because false "no value" becomes true.
How do I stop the previous data from being wiped out once I move on in columns?
View 14 Replies
View Related
Nov 28, 2011
I have a couple of functions running in my workbook, and for no reason that I can see, when one of them runs during a simple macro I use to show/hide some columns, the worksheet vanishes. Is there a way to stop all functions running while I show/hide columns?
View 5 Replies
View Related
Feb 5, 2009
I am having trouble filling a formulae series to the left on one spreadsheet, the fomulae being references to another sheet.
For example, I have two sheets 'Mtce Options' and 'Base Case'. In 'Mtce Options' I have the following formulae
A B C
1='Base Case'!A15='Base Case'!D15='Base Case'!G15
I want to fill to the left, incrementing the column references by a factor of 2 each time, eg. next two should be ='Base Case'!J15 and ='Base Case'!M15.
However, if I autofill to the left by highlighting A1, B1 and C1 or just B1 and C1 all I get is an inappropriate reference such as ='Base Case'!D15 or ='Base Case'!F15, respectively, in D15.
View 2 Replies
View Related
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
Aug 16, 2014
I want to perform double_click event for combobox, but only for cells in certain columns and rows.
this is code :
[Code] ....
I have to loop through columns 6 to 67 with step 1, and rows 19 to 409 with step 5, but don't know how . I attached sample, coloured cells needs that double_click event.
Attached File : Loop_Column_Row.xls
View 12 Replies
View Related
