Results 1 to 3 of 3

Thread: VBA Code Works Only in debug Mode.

  1. #1
    Member
    Join Date
    Nov 2011
    Posts
    41
    Rep Power
    0

    VBA Code Works Only in debug Mode.

    Hi All,


    Today I came across a very intresting problem ie I wrote a line of code for sorting my data on the worksheet but line of code is not working Automatically.It is working only when i debug the same line. I dont know what is going on and why it is happening.

    My Line of code is :
    Code:
    ThisWorkbook.Worksheets("TempCoverage").Range("A1").CurrentRegion.Sort Range("A2"), xlAscending, , , , , , xlYes

    Thanks.

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I think your problem is revolving around your worksheet references... your sort range is referencing your TempCoverage worksheet whereas your Key1 argument is referencing the active sheet... if those two worksheets are the same, then the code works fine, but if the active sheet is not TempCoverage, then Excel generates an error (if you don't see an error message, it might be hidden behind an On Error trap... you didn't post enough code for us to know for sure or not). Try changing your posted line of code to this and I think it should work fine afterwards....

    Code:
    ThisWorkbook.Worksheets("TempCoverage").Range("A1").CurrentRegion.Sort ThisWorkbook.Worksheets("TempCoverage").Range("A2"), xlAscending, , , , , , xlYes
    Although if you have other code that needs to reference the same worksheet within your procedure, it might be better to wrap everything using a With/End With block...

    Code:
    With ThisWorkbook.Worksheets("TempCoverage")
      .Range("A1").CurrentRegion.Sort .Range("A2"), xlAscending, , , , , , xlYes
    '
    '  other code needing to reference the same worksheet (note the leading dot in case With/End With is new to you)
    '
    End With
    EDIT NOTE
    ---------------------
    By the way, I am guessing your code worked in debug mode because you went to the TempCoverage worksheet "to see what was wrong" thus making it the active worksheet.
    Last edited by Rick Rothstein; 05-28-2012 at 10:27 AM.

  3. #3
    Junior Member
    Join Date
    Jun 2012
    Posts
    1
    Rep Power
    0
    Can i know more about VBA code?

Similar Threads

  1. Combobox Not Working In Excel Workbook Shared Mode
    By peter renton in forum Excel Help
    Replies: 15
    Last Post: 06-03-2013, 01:25 PM
  2. Changing Slideshow viewing mode to kiosk using vba
    By Times in forum Powerpoint Help
    Replies: 1
    Last Post: 05-10-2013, 12:37 AM
  3. VBA Code to extract subtotals
    By Howardc in forum Excel Help
    Replies: 2
    Last Post: 12-02-2012, 01:15 PM
  4. VBA Code to Extract data
    By Howardc in forum Excel Help
    Replies: 1
    Last Post: 07-24-2012, 11:37 PM
  5. VBA Code to Clear the Immediate Window.
    By technicalupload in forum Excel and VBA Tips and Tricks
    Replies: 0
    Last Post: 09-02-2011, 03:04 PM

Posting Permissions

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