I have a very long routine, looping through 35,000-ish rows several times to detect and delete unwanted items. Occasionally, it gets locked into an endless loop and CTRL+Break will not stop it - I have to kill Excel through the Task Manager.
How can I find out where the endless loop is so I can detect what's causingit and fix it?
Why does my Excel hang on this
While TempWaarde1 <> ""
TempWaarde1 = Cells.Value(Posi, 1)
TempWaarde2 = Cells.Value(Posi - 1, 1)
If TempWaarde1 <> TempWaarde2 Then
ListBox1.AddItem TempWaarde1
End If
Posi = Posi + 1
Wend
What I'm trying to achief is to fill a listbox with all values in the first column of the active sheet, but avoiding double entry's if they're on the row after the 1st one. When I execute this in my Userform_Initialize, Excel hangs like the while-wend loop is never finishing, while i'm sure there is no more data in the 1st column after 200 rows or so.
I've used a loop in one of my macros and the loop goes on endlessly. I've been trying to come up witha way to overcome this problem but with no luck till now. knowing that I'm new to VBA i decided to turn to the big guns at mr.exxcel to help me out. Here's the code i have. Any alternatives to this would be VERY VERY VERY USEFUL!!!
Sub smalloops()
Dim i As Integer
Dim compliant As Integer
Dim noncompliant As Integer
compliant = 0
noncompliant = 0
For i = 1 To 60
Do Until Cells(i, 2) = ("RECOMMENDATIONS")
Do While Cells(i, 3) 0
If Cells(i, 7) = 1 Then
compliant = compliant + 1
ElseIf Cells(i, 7) = 2 Then.................
I have the following VBA in the attatched spreadsheet (seperated so I could upload)
The only problem that I have is that I cannot stop the loop, it constantly keeps going round in a circle.
What im wanting to do is when the Date checked is the End date and the End Time has been checked, to move onto the next row and do it again until it gets to row 40.
Sub Calculate()
Dim CellNo As Integer
Dim CurrentDate As Date
Dim StartDate As Date
Dim EndDate As Date
Dim theDate As String
Dim c As Range
Dim i As Integer
Dim addDate As Integer
Dim DaysHoursSick As Date
Dim StartWorkTime As Date
Dim EndWorkTime As Date
Dim endmarker As String
I've only got this VBA working really under Wednesday so I can make sure it is right before adding it onto the other dates,
Having another problem with the same macro that has been giving me trouble for a week now. Now, the macro will run, but after it finishes, excel stops responding. Is this because the macro continues to run indefinitely.
View 3 Replies View RelatedI have a multi-sheet workbook. The first sheet is a summary of results from the rest of the workbook. I would like this summary sheet to auto-refresh itself each time the sheet is activated. The VBA code triggered by the Worksheet/Activate event feeds some parameters out to other sheets, then copies back the results to the summary sheet of the workbook.
While doing so, it keeps "reactivating" the first sheet, causing it to get into an endless loop that is triggered by the Worksheet/Activate event. Essentially, I'd like the Worksheet/Activate event to go dormant for 15 seconds or so each time it is triggered.
When I set a "for each cell in range" to go through the range, and if there are merged cells in this range, this will result an endless loop.
View 9 Replies View RelatedI 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
Here is what I am trying to do: I have 30 duplicate worksheets that I enter data every 3 months. I have a macro that clears certain fields and copies data from one field to other and I run this macro after selecting each sheet one at a time. How can I create a macro that will run this macro from the 1st sheet through to the 30th sheet in one click. My macro is called Clear_data.
View 12 Replies View RelatedI have an add-in that can run the code below. It works fine, however, I need it to run whether there is just one row of data, or 100 rows of data. I have headers, so the first row of data starts in row 2, and the last no matter what will be in row 100 (so 99 rows of actual data technically). There are usually only 10 rows of actual data as the rest are usally set to blanks with iferror. The code below works, but I need it to run for however many rows of actual data there are.
View 1 Replies View RelatedI am using VLOOKUP with the not_exact_match set to True, however instead of finding the next largest value that is less than value, I want to find a way of returning the next largest value that is greater than value. I have looked at using MATCH and OFFSET to try and increment the returned value by 1.
View 9 Replies View RelatedI am having an issue getting an IF statement to work inside a DO loop. I have Microsoft Excel 2007 and Windows 7 Entreprise.
In my Excel spreadsheet, I have in column B a list of keywords and in column C the category to which each keyword belongs. For instance, the keyword "boot" goes withe the category "Shoes", "scarf" goes in "Accessories", etc.
In another column, I have a list of product descriptions (like "Black Boots" or "Silk Scarf") and I would like to assign the relevant category to each product. I thought to do this by creating a new function in VBA. This function would go through the whole list of keywords in column B, checking if the keyword is included in the product description. If it is, the function's result would be the relevant category, and if it is not, the function would search the next line in column B, until a relevant keyword is found, or until the end of the list.
Here is the code I have written so far:
Code:
Function SegmentSearch(Item)
Dim i As Integer
i = 1
Do
i = i + 1
If Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE" Then SegmentSearch = Cells(i, 3)
Loop Until Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search(Sheet5.Cells(i, 2), Item)) = "TRUE"
End Function
Without the loop, the function works fine (if "Item" happens to include the first keyword in the list) but as soon as I try putting the loop I get a #VALUE! error.
I'm trying to loop through worksheets as follows:
If ws.Name "DataTable" And ws.Name "Summary" Then ws.Activate
code to do some copy/paste stuff ....
Next ws
Try as I may, I can't get the code to skip the worksheets named "DataTable" and "Summary". Based on other posts, I've tried swapping out the And for an Or. Also tried using LCase(ws.Name), to no avail. Note that I'm acually trying to skip several other sheets as well but only listed two here for simplicity.
I'm using Excel 2007.
Need a code that sends emails to the intended receipients based on hold codes if Columnd J reads as YES Email formats are listed on "Email Format" tab, code should pick the required field value from the table (Hold Report).
Analyst name, Phone number are entered in the text boxes (data validation for these is desired)
option boxes are used to send email based on the selection as below
if "send initial email without attachment" is selected then emails should be sent without the attachments
if "send initial email with attachment" is selected then emails should be sent with the attachments
Attachments are usually .PDF files and are stored on users desktop with file name as .pdf
if the follow up otion is selected the subject line on the mail should be appended as *** 1st Follow-Up*** along with standard subject line
Rest of the requirement remains same for followup option (same email format is used) based on the option selected, code should be able to send emails with or without attachments. I have provided three command buttons that are intended to work as follows;
Validate data: need to validate the data to ensure that each row has atleast one email address in "TO" column and there are no spaces in the email address.
Clear Data: Should clear the existing data from the table
Send Emails: Should send emails
Standard subject line is a combination as below
Invoice on Hold for ; Invoice number: ; PO Number: ; Invoice Amount:
Note: Column H and I apply only for hold codes Qty Ord and Max Ship Amount
I use Microsoft 2007.
I'm working in XL07 on Win7.
The workbook has two worksheets "Data" & "Crunch". "Data" contains the raw data-range B1:U50. "Crunch" is the processing worksheet. Row 1 is a header row. Columns A-D are empty. Column E (rows 2-81) contain the numbers 1-80.
The macro is intended to run in "Crunch". I need to add the CountIf function in every cell/row filling the range F2:BC81. A loop does the trick for me to fill the rows in each column but I don't know how to move the loop down each column without creating a new loop.
A section of the code is below. Notice a majority of the code is the same, the copy in red is variable from loop to loop. I need to repeat this loop through column "BC" (50 times in total). My guess is there is a way to write this code one time instead of 50 times adjusting the variable components.
Code:
'Find total # of records and then store in variable
totalrecords = ActiveSheet.UsedRange.Rows.Count
'CountIf Statement
[Code]....
Here is my situation:
Background:
- Excel VBA 2007
- I have a macro that inserts a set of vlookups in range D3:D8.
- When D3:D8 has been filled, I need the macro to COPY the Vlookups in D3:D8, skip down one cell, and paste in the next set of open spaces.
- I have a Do statement that isn't working, and and IF...THEN that quits the loop when a blank cell is found (This means the range of vlookups is no longer needed).
Objectives:
- Fix Do...Loop so it copies D3 throuh D8, skips a cell below D8, and Pastes in the next section? - There will sometimes be only one section to paste in, and sometimes 20. It is dynamic.
Code:
Sub InsertVLookup2()
' This macro inserts the VLookup into cell B2
Dim lastcolumn As Long
Range("D3").Formula = "=VLOOKUP($A2,INDIRECT(""'"" & B$1 & ""'!A:I""),9,FALSE)"
Range("D3").Select
[Code] .......
I am in the process of re-building a major project due to bugs. In the process, I plan to re-organize my sub-routine grouping within modules. In the source file, I have 18 standard modules, with 10-30 routines in each one.
What I would like is a printout of each routine name and which module it resides in, so I can check off each one as I copy it and paste it into the new file. I see that I can get a psuedo tree-view of my project in th eObject Browser, but can't figure out how to print that.
using Excel 2007. I need a code to identify a worksheet within a workbook by cell/range value. The book is used by various users. They have the rights to add new sheets and all but delete columns in the 'master worksheet'. The sheet names can be changed by the user but I need to rename the master sheet on opening the file. To do this I have put a specific value in a cell within the master worksheet which then should allow me to find the sheet and rename it. (let say Range A1 has a value of "this sheet") I have a mental block on how i can run a loop to search each sheet for the identifying value until the range and value is found and the sheet identified
View 5 Replies View Related(Excel 2007) The issue I'm having is that a loop statement of mine is failing to recognize that a couple values are equal. It works for the first 3 iterations of the loop, then stops recognizing. I tried copying another set of numbers in the failing one's place, and that actually worked. I can't attach anything, so I'm including a link to a detailed screen shot with some notes on it and code that I'll host on my site. Not sure how else to explain the issue, but if you take a look at the screen shot you can see the excel sheet being worked on and the vba code running. I will also include a text file of the code
Here are the links to the screen shot and text file of my code
Text file
Screen Shot
I was wondering if anyone had any code to loop through every file in a folder and list the file name along with every sheet name in that file? I'm using Excel 2007.
View 4 Replies View RelatedI've got a sheet which I need to analyse and split into several different sheets but the raw data format leaves a lot to be desired as there are blank rows inserted randomly between rows of data. I need the 'good' data to stay in the same order so I've written a macro to sort through the data and delete any blank rows leaving the good stuff behind.
The problem is this takes ages as there can be up to 30000 rows that need to be checked and I need to do this 5-6 times a day. I just wondered weather there was a quicker way to do this? The code I've got is detailed below:
Sub Prep2()
'Delete all blank data rows
Dim Rows As Double
Dim Rownum As Double
Application.ScreenUpdating = False
Rows = Selection.SpecialCells(xlLastCell).Row
For Rownum = 2 To Rows
If Cells(Rownum, 11) "" Then GoTo NxtRownum Else
Cells(Rownum, 11).EntireRow.Delete shift:=xlUp
Rows = Rows - 1
NxtRownum:
Next Rownum
Application.ScreenUpdating = True
End Sub
My current task is to create a spreadsheet that will auto-hide/show rows based upon values in other cells. I am currently about halfway 40% complete with this aspect of the file. I've read over many forums, and the best what that I've seen individuals do it on other sample spreadsheets posted were a =Rand() in a random cell somewhere in the sheet, and a Private Sub worksheet_calculate(). I'm far from a VBA expert. I'm trying to learn as I go along. Here is my current VBA:
View 4 Replies View RelatedExcel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1
I am looking to implement the "Compare Documents" function (available in Word 2010) in Excel 2010 through VBA programming between different text contained in two cells.
In Word this function works quite well (not perfectly), but it highlights in different ways which part has been deleted and which one has been added between an "original" document and a "revised" one.
For the nature of my job, I need to do this on a daily basis and I used to output text from Excel to Word, then compare the two text, and then copy it back to Excel.
Here comes the problem: since in Word the text is formatted (and what I'm looking for is formatted/highlighted text as output), I can't just paste it in Excel as it is: any editing, merging, splitting done on the pasted text (that eventually I need to do) makes the formatting disappear (above all with VBA functions, that can only output data and can't format it).
In other words: given two cells containing different text, I would like to be able to fill a third cell with text formatted accordingly to the TextDiff output between the two original cell.
E.g.:
INPUT:
Cells(1,1).Value2 = "my name is Andrea and I like jogging" (original)
Cells(1,2).Value2 = "my name is Giovanni and I like running" (revised)
OUTPUT:
Cells(1,3) wll contain: "my name is AndreaGiovanni and I like joggingrunning"
Obviously, since UDF doesn't allow formatting of cells, I would need to adjust the main Sub for each pair of document I have to revise, but that won't be the problem: what I need is the engine. It's been two years and a half that I do advanced VBA programming at work but it looks like I can't grasp the rationale behind the LCS algorithm.
I have written a VBA procedure which loops through excel files in an array: in turn it opens the file, refreshes the data, saves the file and closes. I would like to write an error handler which stores the file which errored (copied from the loop iteration) and the error description to an array. The error handler should then close the file and continue with the next iteration - e.g.
Code:
If ActiveWorkbook.ReadOnly Then
ActiveWorkbook.Close
Next i
*Although a generic if error may be better.
My idea is that all files which errored in some way will be stored in an array which I can automatically print/send to a user so they can manually figure out the problem. (This element falls outside the range of this query).
Below is a simple example of my efforts which only contains one file in the array to make things simpler: the error handler section won't work, it's just there to show my thinking
Code:
Sub Refresh_CRIS()
On Error GoTo Errorhandler
Dim routepath As String
routepath = "
chdfsSharedAreaPrivateTest"
ChDir routepath
[Code] ........
Errorhandler:
On Error Resume Next
Failed = Array(i)
If ActiveWorkbook.ReadOnly Then 'Just an example for testing
ActiveWorkbook.Close
Else
MsgBox "Pivots which failed to refresh:" & Failed '& ", ", 0, "Debug" 'Test with msgbox
End If
End Sub
Using Excel 2010
In Excel 2010, you can color a cell with a fill color and a font color.
It can be done manually via one of 3 methods that I know of (aside from a macro or a routine):
-From the ribbon button (underneath the font size and increase and decrease font size toolbar buttons)
-By formatting the cell (right clicking on a cell),
-Clicking on the ribbon's font section (giving you the same 6 category format cell box as right clicking on a cell)
What I need is a routine that resets the Fill Color and Font Color toolbar buttons to "No Fill" and "Automatic" if I run it inside a macro or create a command button on a worksheet.
I'm having issues with finding anything on the loop that I'm going to need to run.
I need to open each xls file in a directory with a date in 2007. Inside of each file I need to copy a cell and paste that number into a new workbook in cell A1
Then close the first file, open the second, copy the cell and paste the data into A2. I would need this loop to continue until all files have been opened and had the data copied.
I am trying to find the first empty cell in a column to paste values. I have read the FAQ and searched the forums but every solution seems to fail me! I have tried the ...xlup, xldown, loops, For , IF statements, etc. to no avail.
My column has the first Four Rows empty with some Header rows in Row 6. I want to paste starting on Row 7. The most success I have had is finding the first empty cell and pasting a value but every subsequent attempt to find the next empty cell and paste a value just keeps pasting on the same cell found in the first attempt. Below is my most recent attempt. I am using MS-Office 2003 (or is there some special worksheet setting that prevents this from working?)
Sub My_Finder()
Dim cP1 As Range
Dim M As Integer
Dim Task As Range
Dim MyRange As Range
Worksheets("Weeks2").Activate
I have a worksheet with a various data in column B.
1/ I want to search column B one row at a time looking for a string value of 'Town'.
2/ When the first row containing this string is found select a left 35 chars substring of the value in the column C and copy this down in column A if the value in Column B = '0000/00' UNTIL the string value of 'Town' changes
3/ Repeat 2/ until the last instance of 'Town' has been processed.
4/ Column A will have some empty cells where Town has not been populated. These rows should be deleted and replaced with one empty row between each change of Town value
3/ Continue pasting this string value into each successive row
I have a range of cells which I wish to print to a .txt document. However, I would like these cells to stay aligned, one on top of the other. I am currently doing that by finding the cell with the widest piece of data for each column, and storing the width that each column needs to be to an array of integers. Then, when printing out the range, I simply add spaces to each piece of data its width is the same as the max column width. I am finding the max column width using the following loop:
'find the width for each column and store in col_width()
For cur_col = 1 To total_cols
'skip the tag switches column
If cur_col <> 3 Then
max_data_width = 0
For cur_row = 1 To total_rows
cell_data_width = Len(Find_String_Diagnostics(diagnostic_range.Cells(cur_row, cur_col)))
If cell_data_width > max_data_width Then.................