Results 1 to 8 of 8

"Not Responding" while code is running... need help with optimizing my code please!

Threaded View

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question "Not Responding" while code is running... need help with optimizing my code please!

    Hi VBA gurus,

    When i run my code, the application status shows the count but stops after a short while and it just turns to "Not Responding". Could it be that my code is inefficient? I'm not an expert at vba as its all mostly self-taught. please someone help!!!

    Alittle background:
    1. the code is found on a master workbook (which i leave open in the background), I then open a separate workbook each time to run the macro.
    2. the data starts from row 4 in each separate workbook.
    3. for each row of data found in a separate workbook- 2 points are used to trigger a match in the "ref" sheet of the master workbook. Namely: the active workbook's name and one other data points (sort of like a sumproduct/ 2 intersection)
    4. once the match is found, other data from the "ref" sheet is then copied over to the active workbook. and a string is introduced to another cell in the row as well

    Is there a more efficient way to do this??

    btw, i am running close to 25000 rows of data in one instance. and i have to do this for 15~20 separate workbooks

    Sub Matcher()
    
    Dim x, y, z As Long
    Dim wsf As WorksheetFunction
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set wsf = Application.WorksheetFunction
    
    lrow = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
    
    For x = 4 To lrow
    
        For y = 2 To ThisWorkbook.Worksheets("Ref").Cells(Rows.Count, 1).End(xlUp).Row
            
            If Replace(ActiveWorkbook.Name, ".xlsx", "") = ThisWorkbook.Worksheets("Ref").Cells(y, 1) And ActiveWorkbook.Sheets(1).Cells(x, 5) = ThisWorkbook.Worksheets("Ref").Cells(y, 16) Then
            ActiveWorkbook.Sheets(1).Cells(x, 96) = ThisWorkbook.Worksheets("Ref").Cells(y, 4)
            ActiveWorkbook.Sheets(1).Cells(x, 97) = ThisWorkbook.Worksheets("Ref").Cells(y, 8)
            
            With ActiveWorkbook.Sheets(1)
                .Cells(x, 108) = Replace(ActiveWorkbook.Name, ".xlsx", "") & "-" & .Cells(x, 107) & "-" & .Cells(x, 96)
                .Cells(x, 14).Copy Destination:=Cells(x, 105)
            End With
            
            End If
        
        Next y
        
    Application.StatusBar = "Updating " & x & "of " & lrow & "(" & Format(x / lrow, "0%") & ")"
    Application.StatusBar = False
    Next x
    
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    MsgBox ("Done")
    
    End Sub
    Last edited by dexcee; 08-27-2013 at 02:42 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Running Code After "Opening File" Event Has Triggered Data Connection Refresh
    By david.nicholls in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-09-2013, 07:22 AM
  2. [SOLVED] Code behaves differently between "step into" and running outright
    By theletterh in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 04-13-2012, 09:52 AM
  3. Excel and Outlook become "NOT RESPONDING" when running Macro
    By g1987 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-13-2012, 03:28 PM
  4. Optimizing code time running
    By Pichingualas in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-02-2012, 12:13 PM
  5. Stop code from running when I click "Cancel"
    By Dean in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-01-2006, 08:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1