I am attempting to loop this function until it finds the next empty row in column C.
Private Sub Workbook_Open()
Range("C2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",RC[-1]+30)"
Range("C3").Select
End Sub
I have a feeling this is an extremely basic and easy task and I am perhaps, overthinking the code?
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
I want to use the function VLOOKUP at my VBA program inside a FOR Loop
View 3 Replies View RelatedI'm currently working on a program in which some sort of recursive looping seems logical.
The relevant part of the code I've currently written looks like this:
Sub hello()
Dim Max_stream As Integer
Dim j, jj As Integer
Dim P_sum, P_min As Double
Dim antal_in, antal_ut, rad_in, rad_ut, ut1, ut2 As Integer
Application. ScreenUpdating = False
Max_stream = WorksheetFunction.Max(Worksheets("Data"). Range("B65536").End(xlUp).Value)
For j = 1 To Max_stream
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.
Here is the code for the loop:
could provide me a loop function in VBA for these calcultions.
Cell A1*E1 + B1*F1 + C1*G1
Cell A2*E1 + B2*F1 + C2*G1
......till
CellA100*E1 + B100*F1 + C100 * G1
These are the calculations for E1-G1.
The process must be repeated till E50-G50
So the second step is:
Cell A1*E2 + B1*F2 + C1*G2
Cell A2*E2 + B2*F2 + C2*G2
......till
CellA100*E2 + B100*F2 + C100 * G2
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.
Below is the data.
10010
10011
10011
[Code] ....
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
[Code]....
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,
View 3 Replies View RelatedI 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]....
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)
text
1
text
text
[Code]....
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
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 ..................
Can't seem to figure out why my Do Loop keeps giving me an Error of "Loop without Do".
View 14 Replies View RelatedI 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.
My code below goes into an infinite loop, with culprit line shown in bold and underline...
View 4 Replies View RelatedI 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.
Another interesting dilemma to solve. Using this formula:
View 2 Replies View RelatedFunction Haversine has correct value in debugger but in cell it has the same value as Haversine2. Is this a known bug?
Public Function Haversine(lat1 As Double, long1 As Double, lat2 As Double, long2 As Double) As Double
Dim temp As Double
[Code]....