Loop Not Working - Move Onto Next Row
Mar 28, 2012
What i am trying to do:
*i have about 2000 rows.
*start from first row
*for each row that contains .pdf in one cell, i want another cell in that row to equal .dir
*hyperlink cell if cell contains dir
*move onto next cell (for all approx 2000 cells)
The problem:
*it is hyperlinking every cell even if the other cell doesn't contain .pdf.
*i think it is looking at the first row and if the cell has .pdf it applies it too all cells
What I want to do:
*select entire row of first row (21)
*if col 21 = .pdf then col 23 = .dir
*of col 23 = dir hyperlink .dir to fname value
*move onto next row (22)....do the same....and so on and so on all the way to row 2039
The code I am using:
Private Sub UserForm_activate()
'Unprotect Workbook
ThisWorkbook.Worksheets("Cover Page").Unprotect Password:="password"
ThisWorkbook.Worksheets("D - Documentation").Unprotect Password:="password"
ThisWorkbook.Worksheets("E - Electrical").Unprotect Password:="password"
ThisWorkbook.Worksheets("F - Process Flow & P&ID").Unprotect Password:="password"
[Code] .......
View 1 Replies
ADVERTISEMENT
Oct 29, 2009
I have written the below code (as part of a larger code set) to copy a range and paste it in cell Z4 if Z4 is empty. If it isn't empty, I want it to move one cell to the right and paste it in AA4. This should be a loop pasting one cell to the right of the last filled cell.
I have come up with the following which moves one cell to the right, but only once. It doesn't loop. Also, this loop operates within a larger loop.
Range("z4").Select
If Range("z4") = "" Then
ActiveSheet.Paste
Application.CutCopyMode = False
Else
i = 1
Range("z4").Offset(0, i).Select
i = i + 1
ActiveSheet.Paste
Application.CutCopyMode = False
End If
View 9 Replies
View Related
Nov 10, 2006
In a macro I made, I have a do...while loop:
Do While Not objXLworkBook.ActiveSheet.Cells(nSequence, 1).Value = gcstrEMPTYSTRING
.....bunch of code
Loop
In my file, the second row of data always contains blanks (i.e., I've got a HDR record, blank row and then rows of data). How do I make my code bypass that empty row of data and then continue processing all other records?
Should I put a "move to next record" in my bunch of code, and if so, how do I do that?
View 9 Replies
View Related
Dec 27, 2013
I'm looking to automatically move data from my working worksheet, when a particular column states "Won" or "Lost". I'm hoping the entire row of data can be cut, and added to the appropriate sheet, determined by "Won" or "Lost".
View 5 Replies
View Related
Jan 24, 2014
I have two sheets and need information from Sheet titled Add-Del_chng positions to be moved to the sheet titled Cost Distribution.The issues here are :
1. The values in Cells A through J should be moved only if col Z value is 'hard coded"
2. The values should be moved to the 13th row.
3. This should happen every time the value is input into col Z of sheet titled add_del_chng.
Currently the macro does not run unless I go into the code and press play. I need this to happen automatically.
View 13 Replies
View Related
Sep 19, 2013
I have a workbook with part data. Each worksheet is an individual part with its data on that sheet. I'm trying to move certain rows between two key words to a separate wb/ws for the top key word. Data looks something like this:
Supplier 1
row
row
row
Supplier 4
row
row
Supplier 15
row
row
row
As I said, each sheet could have only one supplier or a dozen. The number of rows between each supplier also vary. I was trying something like this to do what I need to do:
Code:
DistiStr = "Supplier 1, Supplier 2, Supplier 3, etc etc etc"
DistiArr = Split(DistiStr, ",")
intDistiMax = UBound(DistiArr)
Set VendorWB = Workbooks.Add(xlWBATWorksheet)
VendorWB.SaveAs Filename:="Vendors"
[Code] .......
I see where my logic problem is: It's looping through a3 - lastrow, but I'm going one row extra each time, so when it goes to the top of the loop again, I'm one row to far starting off. I originally did this because if there's only 1 supplier on the sheet, there won't be another supplier name below it to give me my range.
View 2 Replies
View Related
Apr 20, 2012
I am trying to use VBA code to take the relevant selections made to populate a Listbox and to insert them back into a cell range within the worksheet. The code I have come up with so far is:
Private Sub cmdApply_Click()
Dim i As Integer
Dim j As Integer
Dim StartRow As Integer
Dim EndRow As Integer
Dim StartCol As Integer
Dim EndCol As Integer
Dim temp
[code]....
The code should take any data within the listbox and insert the individual text into subsequent cells across the specified cell range. However, I keep getting various error messages. I have tried permutations of the code which do run but which copy a single entry from the listbox across the whole cell range!
View 5 Replies
View Related
May 7, 2008
i am use dir to put into an array all the folders in a folder then enter the first folder and repeat. the problem i am having is when it encounters a file it also puts it into the array and then errors once it trys to enter that "folder" (which is a file)
right now the code doesnt do anything but the plan is then to call another subroutine that lists all the files im looking for in a folder (that sub does work)
what am i doing wrong? all the things i can find on google show it the way i am doing it.
Sub GetDirList(topfolder As String)
Dim FolderArray() As Variant
Dim FolderCount As Integer
Dim FolderName As String
FolderCount = 0
FolderName = Dir(topfolder, vbDirectory)
' Loop until no more folders are found
Do While FolderName <> ""
If Not FolderName = "." Then
If Not FolderName = ".." Then
FolderCount = FolderCount + 1
Redim Preserve FolderArray(1 To FolderCount)
FolderArray(FolderCount) = FolderName
End If
End If
View 3 Replies
View Related
Aug 8, 2006
I've got this code that pulls data from multiple files into one table. the file name is in the top row, and each file's title, and two different sums display below that. Then it is supposed to loop through and display each task and the start and end date for that task within each file. After that the first loop brings it to the next column and file. The primary loop works fine, but the inner loop only seems to run once, as I get only the first result for each file. All I can figure is that maybe my row numbers aren't resetting like they're supposed to, or I'm completely overlooking something, which is likely, as VBA is by no means my forte.
Sub Worksheet_Calculate()
Dim sBook As String
On Error Resume Next
Application.EnableEvents = False
Col = 8
' Sets start column
Do
sBook = Cells(2, Col)
' References file name
TaskRow = 6 ........................
View 5 Replies
View Related
Aug 10, 2012
I have data in Row 53 that spans 7 columns, but stays in the same row. I want to design a loop to select every 7th cell in that row and check if it is empty. If not, add onto a "counter" then display the final number of occupied cells (the value of the counter) at the end. This is what I have so far, but I get all sorts of errors.
Code:
Sub Tester()
Dim WB As Workbook
Dim WS As Worksheets
Dim modCounter As Long
Dim Cell As Range
Set WB = Workbook("Transverse Series.xlsm")
Set WS = WB.Sheets(BM18)
[Code] ......
View 1 Replies
View Related
Jan 16, 2009
Just need to delete some hyperlinks in column A on 50+ worksheets. Thought a loop through all the worksheets would do it. Only works on active sheet. Forgive my ignorance, don't really even know where it goes, once it works - module or workbook?
View 2 Replies
View Related
Mar 31, 2008
With Sheets("regrade pharm_standalone")
For Each r In .Range("standaloneTerritory")
If r.Value = "X101" Then
r.EntireRow.Copy
Sheets("X101").Range("A1").End(xlDown).Offset(1).PasteSpecial xlPasteValues
End If
Next r
End With
-------------------
I need to repeat this loop for values from X101 to X151. In all cases, the sheet name is equal to the value I'm looking up (eg: value = X102 goes to sheet X102).
I have a named range called 'territories' that contains the list of X101 -> X152.
I'm hoping to make the code perform the loop for each of the territories without my having to copy & paste and change the 'X101' 51 times as this would seem a rather silly thing to do!
View 9 Replies
View Related
Jan 25, 2010
I have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
View 9 Replies
View Related
Jul 7, 2009
is there a way to stick a few row of cells together so that when i move one they all move.. i have some rows that when i sort them i want them to move together with the other ones..
View 9 Replies
View Related
May 14, 2014
Macro which loops through a number of files and calls the same macro in each of them. Unfortunately when I add "Application.Run..." to the code, it no longer loops through the process and instead stops after updating the first file in the loop. If I remove the "Application.Run..." code and add any other code, the loop works fine and it continues through the process repeating all the steps for each file found.
Why it stops after one file when using "Application.Run..." to call the macros?
NB I have a list of path and file names starting in row 8 of columns A and C. Each file in the list has a macro called UpdateS1 and promoupdate1.
Sub C_Run_Loop_Macro()
Dim lastRow As Long
Dim i As Long
[Code]....
View 4 Replies
View Related
Sep 11, 2013
I have working code that returns a row number within a for loop based on parameters I set.
Each time the for loop runs I would like to store this row number, then after the loop has finished, delete all stored rows.
Code:
for rowNum = 1 to x (some variable end row number which I already have worked out using End(xlUp).Row)
if x = y then
*storedRow = rowNum
end if
next rowNum
*
Lines with a * are the bits I can't work out. I've been trying to understand arrays by reading posts on what other people have done, but I can't fit (or fully understand) the reDims, or reDim preserves into my code. I've seen what appear to be quite complex ways involving uBounds and LBounds, but unfortunately I can't see how to use them.
All I want is to simply keep adding a row numbers to a variable, (i.e. row 2, 5, 20, 33, 120, etc) and then delete those specific rows.
View 4 Replies
View Related
Aug 30, 2006
I am looping through each cell in a range and I would like to loop in reverse order.
Dim CELL As range
Dim TotalRows As Long
TotalRows = Cells(Rows.Count, 1).End(xlUp).Row
For Each CELL In Range("C1", "C" & TotalRows)
CELL.Select
'Code here to delete a row based on criteria
Next
I have tried:
For Each CELL In Range("C" & TotalRows, "C1")
and it does not make a difference. I need to loop in reverse order since what I am doing in the loop is deleting a row. I am looking at a cell and determining its value. If the value is so much, then the row gets deleted. The problem is that the next row "moves up" one row (taking the pervious cell's address) and therefore the For Each Next loop thinks it has already looked at that row.
View 7 Replies
View Related
Feb 7, 2008
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies
View Related
Nov 4, 2013
I have a workbook that contains, say, 50 worksheets: the first two worksheets summarise the data and are static in that they don't move position. However, the next four worksheets contain certain data for any given month. Each time a new month comes along, say, November, I insert four new worksheets after the two static ones as a result October's four worksheets are simply moved down the line in terms of worksheet order.
I need a macro to refer to the first six worksheets only (not the other tabs). I opted for index referencing for each worksheet, ie one - six. Now within these six worksheets in any given month, I need to sort the data by a certain column. The problem: in sheets 1,4,5 and 6 I need to rank by column E, but in sheets 2 and 3 I need to rank by column C. I have stepped through the code, which works for sheets 3-6, but doesn't seem to refer to sheets 1-2.
Sub WorksheetLoop()
'
' Loop through an indexed number of worksheets; _
' & this ensures that the worksheet range is dynamic _
' and is able to adjust when new sheets are added/removed, etc.
'
'Dim ws As Worksheet
Dim i As Long
Dim ws As Worksheet
[code]....
View 2 Replies
View Related
Oct 24, 2009
I've worked on a solution for this thread (http://www.excelforum.com/excel-prog...-automate.html) but have been mentally challenged with how to avoid changing the loop counter in one of the loops I have used to resort an array of file names from the getopenfile dialog.
The aim of the shown code (see post 12 of the above link for attached file) is to check if the file containing the macro is included in the array returned by getopenfile while sorting the array of file names, and if so, moving it to the end of the array for "deletion" by redimming the array to exclude the last item. This problem of the open file being selected in the dialog may never arise, but... as the OP's request in the other thread was to allow two-way comparisons between numerous files, I've considered it likely enough to test for.
Here's the code I have settled for esp between the commented lines of hash symbols, which does change the counter (see the commented exclamation marks), but prevents an infinite loop (on my second try!) by using a second boolean flag of "HasCounterBeenChanged". Is there a better way of doing this? Or, alternatively (not in my thread title), is it possible to prevent the active file being selected through one of the arguments in the getopenfilename method?
View 3 Replies
View Related
Aug 27, 2012
I am working on some code that loops through a column of number values. Whenever it encounters a number value and a blank cell in an offset column, it places that number value into an offset cell (forming a separate column to be compared to another column in a separate sheet). I would like to take all the values in that new column and begin placing them in a new column in a separate sheet adjacent to another table. Most of the time, these values should match the adjacent values in the separate worksheet. However, if they don't match, I would like a new row to be created for that mismatched value.
For example. This is the first worksheet. So far, my macro loops through the column with rows 1-5. It looks in the offset cell(0,2) for Isempty value and then places that value into the offset cell(0,6)
text
1
text
text
[Code]....
View 5 Replies
View Related
Aug 2, 2007
For Each loop can be instructed to loop starting the bottom of the range. I know that a For To Loop can handle looping from the bottom up,
Sub Filterout()
Dim c As Range
Dim rng As Range
Dim i As Long
Dim lrow As Long
Dim counter As Integer
lrow = Cells(Rows.Count, 3).End(xlUp).Row
Set rng = Range("c2:c36")
For Each c In rng
If Left(c.Value, 1) "~~" Then
c.EntireRow.Delete
End If
Next c
View 9 Replies
View Related
Nov 30, 2006
i have a problem with a nested loop:
it seems like the first instance of the code is running the way i want it to run, but when it starts with the second instance, it does the first search and copy, but it seems like the nested loop is being ignored.
am i doing something wrong?
dan
==========================================================
Thanks to Aaron Blood for the find_range function. i also poached the lastrow function from somewhere on ozgrid, but I cant remember the name of the poster.
==========================================================
Sub new2()
Dim Org_Area As Variant
Dim Item As Variant
Dim Copy_To1 As Variant
Dim Cell_Ref As Variant
r = 1 ..................
View 9 Replies
View Related
Aug 27, 2009
I was looking for a final result as follows
21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)
21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)
View 2 Replies
View Related
Dec 17, 2008
Can't seem to figure out why my Do Loop keeps giving me an Error of "Loop without Do".
View 14 Replies
View Related
Apr 28, 2009
I have got a loop which is working fine but now i need another loop which will run till the end but need to repeat itself as soon the column x become 1 the highest number would be 3
here is my main loop A1 = 5000
and second loop need to run inside the this loop
Sheet1.Activate
i = Range("A1")
For b = 1 To i
If Cells(1 + b, 3).Value = "P" Then
Cells(1 + b, 29).Value = 1
Else
If Cells(1 + b, 3).Value = "S" Then
Cells(1 + b, 29).Value = 2
Else
If Cells(1 + b, 3).Value = "C" Then
Cells(1 + b, 29).Value = 3
End If
End If
End If
Next b
View 9 Replies
View Related
Oct 28, 2006
I have loops working in other loops. The macro is almsot working well. It does the calculation i want but it fails to stop a loop, because of that, the macro can't run the next main loop (c), which is to move to the next cell where the calculations must be run.
I attach a file. the troubleshooting macroation is Sub Itiration.
The code of this macro are bellow. Basically, the loop using d as counter run into an endless loop. I don't how to stop this loop without affecting the results which are calculated correctly.
Sub Itiration()
Dim CurCell As Object
Dim TempSum As Double
Dim d As Integer
For c = 3 To Cells(3, 4)
If Cells(11, c) > 0 Then
For i = 1 To Cells(10, c)
Do
View 9 Replies
View Related
Nov 18, 2006
Going through a loop, I am trying to load pictures into an image box (or alternatively into a label) one by one i.e. going through the loop the first time, I want to load picture 1, then on the second loop, picture 2 and so on. A bit like an automated slide show.
I have written a simple loop and have used the loadpicture function to load the picture into the image box. When the code runs, the image box only gets populated after the last run through the loop. I have tried using application.screen updating function and the image.activate function without success. It is a simple bit of code and I expect an easy problem to solve if you know excel vba well.
View 6 Replies
View Related
Apr 8, 2007
My code below goes into an infinite loop, with culprit line shown in bold and underline...
View 4 Replies
View Related
Jul 20, 2007
I am parsing 15,000 files from a network server. The files are all the same format and length. The problem is that the first few iterations of my loop run fairly quickly, 7 to 9 seconds a case, but after only 300 iterations I'm up to 60 seconds a case. How do I keep the last iteration running as fast as the first iteration? I've included the main loop of my parsing routine below.
Sub Fill_Summary_Tabs() 'fills out the Datapack and JMP tabs
Application. ScreenUpdating = False 'turn off screen updating for speed
Call PrepImporterTab 'formats the Importer Tab so that everything runs smoothly < 1 second
Dim fs
Set fs = CreateObject("Scripting.FileSystemObject") 'part of the filename test
For N = StartingCase To EndingCase '***** Start of the Parsing Loop *****
Sheets("Setup"). Cells(20 + N, 1).Value = Time 'Print the start time of each case....................
View 7 Replies
View Related
