I am trying to "reverse the scoring" in a selected row of data. that is if the respondent selected 1 (on a scale of 1-4) I would like it to be replaced with 4.
I have done up this code (which might be pretty messy) but I always end up with Compile error: Loop without do.
Sub ExecuteConversionP2()
'Checks to see if the macro has already been run
Sheets("Sheet name").Cells(72, 3) = Sheets("Sheet name").Cells(72, 3) + 1
If Sheets("Sheet name").Cells(72, 3) > 1 Then
Exit Sub
'if it has already been run the cell C72 will have a number in it and it will exit
Else
Sheets("Sheet name").Select
'This is where I want the loop to start
Range("D76").Select
Do While ActiveCell.Value ""...........
I tried to create a macro that compares two columns using a match function. The macro adds the macro function to the first cell and I double clicked the bottom right of the cell to have the formula auto fill down the column.
I noticed that the macro records the exact number of rows when you perform the auto fill or fill series. Now if I add another couple rows to the sheet, the step to auto fill all the cells in the column it stops before the bottom of the column.
I have been searching for a way to make sure the formula is placed in all the cells. I have been learning about loops but am having a problem building it.
I am trying to do is loop through a series of directories under a target directory. When a directory is found, it looks within that directory and finds any .xls files, loops through those and copies/pastes them to the new target directory. I have managed to get it to work only on the first iteration of the loop through the directories, then it crashes. The error code is "Invalid Procedure Call or Argument." Here's the
If valFilePath = True Then MsgBox (msg) Else 'Search for directories within source directory strDir = Dir(ebsSource & "", vbDirectory) 'If a file has been found Do While strDir <> "" If strDir <> "." And strDir <> ".." Then strFile = Dir(ebsSource & "" & strDir & "" & "*.xls") Do While strFile <> "" 'Copy .xls files and paste in destination copySource = ebsSource & "" & strDir & "" & strFile copyDestination = ebsDestination & "" & strDir & "" & strFile FileCopy copySource, copyDestination strFile = Dir Loop End If 'Crashes here after looping through the first iteration of Excel files strDir = Dir Loop
The problem with the code above is the last If, in which the function "rekursivloop" calls itself. I don't know if this type of formulation is correct, but for the cases the If condition is TRUE (ie ut1 <> ""), the function does not return any answer. Does anyone see an error in the logic or have some example for how to write similar recursive loops?
I have a problem with one of my loops, it takes about 17 seconds to do the job of calculating a simple moving average for 200 periods on 20,000 rows. However, if I do the "FillDown" function for the same type of average, it takes 1 second.
I am having a problem with a custom funciton I am trying to create. It will exit after it is finished with the IF Then Else statement. I need it to continue onto the Do loop at the bottom that does all the work.
Function UPCECheck( num As String) As Long Dim CheckNum As Long Dim TempCheck As Long Dim X As Long Dim Holdtxt As Variant UPCECheck = 0 CheckNum = 0 Debug.Print Len(num) If Len(num) = 12 Then Holdtxt = num ElseIf Len(num) < 12 Then Holdtxt = "000000000000" & num Holdtxt = Val(Mid(Holdtxt, Len(holdtext) - 12, 12)) End If...................
I am trying write code withVBA to save separate file in a different folder with Loop.
Write a code with "loop" till it finds empty cell in the column and for every change in number a separate file needs to be saved in specified folder with file name as "10010, 10011,10012... and so on with data copied in the file saved.
I have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.
I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.
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!
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
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.
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.
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,
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
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?
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)
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
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
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
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
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.
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....................
I'm having trouble using the worksheet copy command in a VBA subroutine. I have the following line in my code:
[Code] ........
When I step through my code and execute this line, the sheet is copied as expected and put in the correct place, but then instead of the next line of code being highlighted, the pointer jumps to the first line of a function (in a different module) in my code.
