Nested Loop Hangs Excel?

Jul 4, 2014

I have a problem with a nested loop in my macro as Excel hangs every time i run my script. I suspect it is because my sheets have too many elements.

I am doing a software localization, and I need to translate records in column A (english) to column X (slovenian) in the same row. With each new version of the software, the number of rows changes.

I already manually did one translation (44000 rows), and now i need to do a new version translation, so I want to use my old translation (because many if not most of the elements are the same) and insert values into the new excel file.

In the new file in sheet1 I have a full column A with english words and an empty C column with slovenian words that I need to fill.

Into sheet2 I copied the old english in column A and slovenian translation in column B.

On sheet 3 i created a button that when clicked, goes though each row in sheet1.columnA, compares it to sheet2.colimnA, and if there is a match, copies the value from sheet2.columnB into sheet1.columnC. So, this should fill most of the translations I need to do, and the rest I will do manually

I am quite new to VBA so this is what I came up with:

[Code]....

The number of rows is around 44000 in each sheet. How I could optimize or even completely avoid the nested loop, because each time i click the button I need to wait for almost an hour and then Excel hangs.

View 8 Replies


ADVERTISEMENT

Excel 2010 :: Freezes Or Hangs In The Middle Of Work?

May 24, 2013

I have an issue with the excel freeze in our organisation.

Our organisation works on Citrix and the office applications have been upgraded to 2010 very recently.We have both 2003 excel users and 2010 excel users.There is a user who has a problem working on excel 2010 the excel freezes.Excel 2010 works fine for some time and all at a sudden hangs,once its hung it freezes all the other applications.

The same file runs properly on the other 2010 systems.The Excel file which hangs are also not of huge in size.

I am unable to get to the rootcause of the issue why the excel freezes or hangs in the middle of the work getting to the root cause of the issue and an answer for the excel hanging.

View 3 Replies View Related

Microsoft Word Hangs With Excel Launch Code

Apr 29, 2012

Consider this procedure to execute a MS Word mailmerge from within Excel:

Code:
Option Explicit
Dim objword As Object
Dim odoc As Object
Dim odoc2 As Object
Dim wdsendtonewdocument As Object
Dim mypath As String

Sub merge()

[Code] ........

The application hangs on the line in red. The file name (worksheets("Frontpage").Range("B15")) exists. It hangs with periodic dialogues "Microsoft Office is waiting for another application to complete an OLE action."

I end up having to go into task manager to close the word application before I can regain Excel control again.

View 9 Replies View Related

Excel Hangs And Freezes After Writing Simple If Statement

Nov 3, 2012

Background: The user makes a selection from a drop down box on the main sheet (sheet5, G12). The drop-down box is linked to *Sheet31.Pax_Nav*. If the drop-down box's linked cell value is less than 5, then do nothing (manual input required). If it is greater than 5, then the vlookup matches that number to a person in the database and returns their weight. The code will pull the required person's weight but then Excel will hang and freeze.

Private Sub Worksheet_Calculate()
On Error Resume Next
If Sheet31.Range("Pax_Nav") > 5 Then
Sheet5.Range("G12").Value = Application.WorksheetFunction.VLookup(Sheet31.Range("Pax_Nav").Value, Sheet31.Range("H17:L48"), 5, False)
Else
End If
End Sub

View 9 Replies View Related

Nested Loop. Inner Loop Not Running

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

Nested For Each Next Loop

Feb 17, 2007

I think its something simple I'm missing here. I have a range of stock prices (Range("E3,E4,E5,E6")) what I'm trying to do is use for each next loop to calculate the number of shares that I could trade. and place those shares in a range of cells adjacent to its corresponding price (Range("G3,G4,G5,G6")). I think the problem is in my loop statements but I can't seem to find it . Could someone kindly show me the error in my ways.

Code: ...

View 9 Replies View Related

Creating Nested For Loop

Feb 4, 2013

I'm trying to finish writing the last portion of a macro created to automate my data analysis project for my research but I'm having trouble writing the last For Loop of the code. I have a target cell B25 which states a given temperature of my reaction. Once a value is entered in B25, 5 activity coefficients are calculated and displayed in cells B31,35,39,43, and 47 which I would then like to copy and paste in cells J31-J35.

However, I'd like to calculate the activity coefficients for a range of temperatures which are listed in cells J30-W30. Thus, I need to create a For loop which copies the value from J30 into B25, copies the values from B31,35,39,43, and 47 to J31-J35 and then moves to the next column replaces the value of B25 with K30 and repeats the same process until all the columns are filled.

View 2 Replies View Related

Nested For Next Loop With VLookup

May 25, 2014

I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.

Setup Sheet
Class Number
Number of Arenas Used
Class 1

[Code].....

View 3 Replies View Related

Nested Control Loop

May 21, 2009

I'd like to know how I can loop through a series of frames and loop through each of the option buttons in each frame and write the value of the selected option back to a worksheet.

Here's an example of how I'm doing it now:

For Each ctl In fraWorkMode.Controls
If ctl.Value = True Then
ws.Range("A1").Value = ctl.Caption
Exit For
End If
Next ctl
For Each ctl In fraTransport.Controls
If ctl.Value = True Then
ws.Range("B1").Value = ctl.Caption
Exit For
End If
Next ctl
For Each ctl In fraTravelTime.Controls
If ctl.Value = True Then
ws.Range("C1").Value = ctl.Caption
Exit For
End If
Next ctl

I'm also thinking that OFFSET is maybe a better option than specifying each cell for the collected data.

These frames are on Userforms for a survey and some of the frames contain up to twelve other frames so a nested loop approach appears to be more efficient.

View 9 Replies View Related

Speed Up Nested Loop

Jul 20, 2006

how to speed up the following loop? It takes about 40 seconds.

Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim x As Integer
Dim y As Integer
Dim Snominaloc As Double
Dim Scs As Double
Dim Sirating As String
Dim Mnominaloc As Range
Dim Mcs As Range
Dim Mirating As Range
Dim Mcbrating As Range

Set Mnominaloc = Worksheets("Parameters").Range("b10")
Set Mcs = Worksheets("Parameters").Range("b22")
Set Mirating = Worksheets("Parameters").Range("b12")
Set Mcbrating = Worksheets("EL").Range("b33")......................

View 2 Replies View Related

Nested Loops. Start & End Loop

Dec 5, 2006

is there any body kind enough to look into this code and tell me from where the loop body starts and ends and which is the loop variable and how it is being incremented or decremented. i know it is a stupid question but i admit here that i learn and manage to update my sheets only because of this forum help, as i have absolutly no idea of VBA when i started some couple of months back but know i can do little bits.
thanx again for all the help i got so far and hope to get some solution this time as well

Sub LowRisk_Chart()
Clearout
Voffset = 0
Vcounter1 = 2
Vline = Sheets("ChartWork"). Cells(Vcounter1, 1)
Vblocks = Sheets("ChartWork").Cells(Vcounter1, 4)
Vstdman = Sheets("ChartWork").Cells(Vcounter1, 5)
Vlength = Vblocks

View 3 Replies View Related

Nested Loop To Concatenate 2 Columns Of Data

Oct 27, 2009

I think I just need a basic Nested For Loop code. I have a list of in column A, from A1:A537 and another list in column B, for B1:B50. I want to add a column that concatenates, each item in Column A to B1 (so 537 rows), then again each item in Column A to B2, another 537 rows, etc.. this loop is repeated 50 times, one for each name in column B.

View 3 Replies View Related

Nested Loop To Divide Adjacent Row By Column

Jan 18, 2010

I am trying to loop through Column Q2:Q2000, and divide the adjacent Row R2:HS2 by the value in the corresponding Q column.

ie. cells R2: HS2 should all be divided by Q2
cells R3:HS3 should all be divided by Q3
etc until R2000:HS2000 are divided by Q2000

I presume that i would require an initial loop to scroll through the column and a seperate loop to scroll through the row values, but i cannot get my head around how this is done.

View 14 Replies View Related

Nested For Next Loop W/ Multipule Arrays Sequence

Oct 30, 2009

I'm having some problems understanding how to change the sequence in loop structures..

This isn't the entire code, but maybe it will get the point across.

Currently the loop is giving me the following sequence..

"CL" "WTI OI" "HO OI" "RB OI"
"HO" "WTI OI" "HO OI" "RB OI"
"RB" "WTI OI" "HO OI" "RB OI"

What I want to get is:

"CL" "WTI OI"
"HO" "HO OI"
"RB' "RB OI"

Is there a way to the loop to go to: x y x y x y
Instead of: x y y y x y y y

Dim ContractCode As Variant
Dim ContractTab As Variant

ContractCode = Array("CL", "HO", "RB")
ContractTab = Array("WTI OI", "HO OI", "RB OI")

For Each x In ContractCode
For Each y In ContractTab
Debug.Print y
Debug.Print x

Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:=x
Cells.Select
Selection.Copy

Sheets(y).Select
Range("A3").Select
ActiveSheet.Paste

Next
Next

View 9 Replies View Related

Nested Next Loop: Evaluate The If Statement Below Over A Range Of Cells

Aug 22, 2008

I think the problem is the order in which I have the "next" loops. I would like to evaluate the if statement below over a range of cells

Sub Analysis()

Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim k As Integer
Dim TheLast As Integer

TheLast = Sheets("Name").Range("c" & Rows.Count).End(xlUp).Row

For i = -7 To -26 Step -1
For x = 24 To 43
For y = 14 To 43
For k = 16 To TheLast.................

View 9 Replies View Related

Nested Loop Not Working; Pulls Data From Multiple Files Into One Table

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

For/next Hangs Up On Second Time Around

Jan 25, 2007

I have and issue within my For/Next loop. I'm simply gathering name and address data from columns in the workbook and printing them out (one at a time) onto a preprinted form letter. I use offsetts to move around the data row and get the appropriate items(address1, address2, city, etc). The first time through, it prints out. I'm then raising the row offset by 1 to get the second name and address data.
I'm using a static starting point on the data sheet but the second time through, I get a Runtime error 1004 at the point where it is supposed to select that starting point. Oddly, entering Debug I have done something which allows it to continue. It gets the second batch of data and prints properly. I have not been able to duplicate how that happened.

View 6 Replies View Related

Macro Hangs After Execution

Jul 11, 2006

I have written a macro .When i execute the macro the result is displayed but my excel file hangs after that.

View 3 Replies View Related

Workbook Hangs When Opened With Macros Enabled

Jan 30, 2008

I have been working on an excel spreadsheet and have come acrss this problem once before too. When I try to open this file it hangs and does not respond. the file size is not too big (490KB). When I disable macros i can open the same file but when the macro are enabled, it hangs. have been trying to look for a solution but just dont know what to do.

View 3 Replies View Related

Macro Code Hangs/Freezes Adding Rows

Sep 15, 2006

I've managed to use some code I found to add a new row below the selected row, and duplicate all the forumlas of the source row. It worked fine dozens of times yesterday, but today it's decided not to work. It gets as far as creating the new row(s), but then just hangs & excel crashes before duplicating the formulas. I've even tried reverting to an earlier version, which also worked fine, but this crashes also!

Sub Add_New_Row()
' Unlock Worksheet
Worksheets("Sheet1").Unprotect Password:="*****"
Dim x As Long
ActiveCell.EntireRow.Select 'So you do not have to preselect entire row
If vRows = 0 Then
vRows = Application.InputBox(prompt:= _
"How many rows do you want to add?", Title:="Add Rows", _
Default:=1, Type:=1) 'Default for 1 row, type 1 is number
If vRows = False Then Exit Sub
End If
'if you just want to add cells and not entire rows
'then delete ".EntireRow" in the following line
Dim sht As Worksheet, shts() As String, i As Integer
Redim shts(1 To Worksheets.Application. ActiveWorkbook. _
Windows(1).SelectedSheets.Count)..................................

View 7 Replies View Related

Linked Cell Combo Box Hangs Running Macro

Dec 1, 2006

I am trying to create a menu that calls macros based on your choice using a drop down combobox (from the control toolbox). Everything works except: When i try to reset the drop down to "Main Menu" using the Linked cell $B$1 excel hangs and suts down evertime. What i would like it to do is reset the drop down to "Main Menu" each time one of the other drop downs are selected. I have really enjoyed this forum and it has been a great resource for me in the past I hope someone can help. I have attaching the file for review.

View 3 Replies View Related

Excel 2007 :: Too Many Nested Functions

Jan 31, 2014

Attached is a small spreadsheet, my problem is I am using Excel 2007 and it only allows me 7 nested functions and I need to do 31.

Nested Function.xlsx‎

View 8 Replies View Related

OpenText Function In Macro Hangs Unless Stepping Through Code In Debug

Oct 29, 2009

We have had a macro running for a few years (Excel 2007 now, but started in 2003) that imports about 35 text files into separate sheets, creates a calculated SUM field for each sheet and copies that value to a title (or summary) sheet. We use this 2 to 4 times per year at inventory time, copying to a new file and deleting the old data before running the macro. The imported files initially create new workbooks, but the data is copied to the initial workbook into a distinct sheets for each file.

Recently (well, last August) this macro started hanging after importing (Workbooks.OpenText) a number of files, and not necessarily the same file every time (on repeated runs.) While trying to figure out the problem, I have now managed to get it to hang every time on the first file! However, if I am stepping through in the debugger it continues past the OpenText command and on F5:Run/Continue will then continue processing the rest of the files normally. If I delete the first file before running the macro, it then hangs on the second file instead.

Without debugging, the first file will import, display on-screen, and there it stops. If I put a break-point on the very next instruction after the import, that break-point is never reached. THINGS I'VE TRIED:.......

View 2 Replies View Related

Excel 2007 :: Nested Formula Limitations

Dec 29, 2011

I'm trying to do a nested formula to automatically assign a region number based on what the County is. We have 88 counties we deal with, split into 7 regions. What is the best way to go about setting up a formula to do this, so I don't have to do it manually? I believe the last time I tried doing something like this, I received an error saying that Excel didn't support a formula as big as what I was writing.

View 9 Replies View Related

Excel 2010 :: Use Nested IF Formula To Color Cells?

May 16, 2014

I am trying to use the IF formula or a similar VBA/Macro to color certain cells. We deal with 16 different sand types that come in on rail cars. I want to put in the sand type in a cell; EX: C11 has text "30/50BH" if this statement is true to color cells A11-D11 yellow. If it is not a true statement to check for the next sand type, "20/40BH" and go on from there. If I can get an example of what to do I can build it for the 16 sands we have.

The formula I have in mind would be something like

=IF("30/50BH",[colorA11:D11,Yellow],[IF("20/40BH",[colorA11:D11, Purple], .......

I'm sure there is a less brute force method of doing this, but my knowledge of programming and excel is limited. I am using the 2010 version of Excel.

View 14 Replies View Related

Excel 2010 :: Logic Error In Nested If Then Statements

Oct 15, 2011

Using Excel 2010.

I extracted data from .pdf to Excel using Able2Extract. Now I need to scrub the output a bit.

I see commonalities in the data for the start and stop of each set of data that I can key in on.

Once I find the start and stop points for each set of data I would like to fill all rows in-between the points and then discard anything that remains outside of these boundaries.

I have something wrong in my logic, way too many rows are deleted.

Code:
Option Explicit

Sub GetLineSets()
'Purpose: Identify relevant line sets, delete all other rows
Dim wbBook As Workbook
Dim wsData As Worksheet
Dim strFormula As String
Dim lngRows As Long
Dim C As Range
Dim blnFlag As Boolean

[Code] ......

View 4 Replies View Related

Excel 2010 :: If Statements For Date Ranges (Nested)

May 18, 2013

I have got a table with data from 2005 to date, (for example) For the purpose of what I am doing I need a column which shows date ranges between September 2005 - August 2006 to show as 2005/2006, then September 2006 - August 2007 to show as 2006/2007. I have done some research and seen that, Potentially, a nested 'IF' can be used but it can only be used 7 times which would cause a problem going forward....

Is this the only way or is there a better way (without using VB)?

I am using Excel 2010

View 7 Replies View Related

Excel 2010 :: Maximum Number Of Nested IF Statements

Jul 29, 2014

1) What's the maximum limit of IF statements in a formula for MS Excel 2010?

2) I have 8 nested IF statements, but I am having trouble with the False part of the argument. I was able to get the False part to work for the 4th argument (because I simply put a comma and closed it's respective colored parenthesis) but the following 3 I can't seem to get and the following message pops when I hit enter "The formula you typed contains an error."

For the False part of the remaining 3 nested statement which is at the end of the formula I just type:

IF($E$2="Q3"........)))IF($E$2="Q2".....)))IF($E$2="Q1")))

And close it's respective colored IF statement argument. I tried putting a comma at the end of each False part for the arguments but it pops with the message "You've entered too many arguments for this function."

View 9 Replies View Related

Macro Stops Running The Macro Completely And Hangs In The Middle

Jul 3, 2007

Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.

They are four PC's along with our Managers laptop that run this Macro daily.

About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.

We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.

I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.

I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.

View 9 Replies View Related

Excel Crash When Run Loop

Jun 3, 2012

I have problems with my excel crashing all the time when I try to run my code. It seems to happen in a loop where I open a .txt file and pull data from it, then close it, and open the next one...

There are no error code, just a message that Excel has stopped working and it will be closed, and then it is forced to restart. Just a mess!

This is my loop code:

Code:
Option Explicit
Dim Desktop As String
Sub Open60only()
Desktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
Dim which_folder As String, which_file As String, which_string As String, tick As String
Dim lastrow As Long
Dim wb1 As Workbook, wb2 As Workbook

[code].....

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved