Sort Code Only Executes When Debugging

Nov 15, 2006

Within the code I am asking excel to sort two difference worksheets with virtually the same code, except that one of the worksheets does not get sorted. If I step through the code, it works and the worksheet is sorted, but if I just run the macro the worksheet does not get sorted, as if the line of code in question is being skipped. Pointedly, my question is - Why does the code work while debugging (stepping through) but not when I generally run it?

Also, as far as the code is concerned, I am sure that the all variables contain the information I need them to and am also sure that any ranges created from those variables are also the ranges they should be. Here are the two lines of code that are supposed to sort. The UBOC cash sheet gets sorted, but the LNB cash sheet does not.

'Sort the UBOC cash sheet.
wsUBOC.Range("A5:G" & lUBOCRow).Sort Key1:=Range("A6"), Order1:=xlAscending, Key2:=Range("D6"), _
Order2:=xlAscending, Key3:=Range("G6"), Order3:=xlAscending, Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

'Sort the LNB cash sheet
wsLNB.Range("A1:G" & lLNBRow).Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("C2"), _ .............

View 2 Replies


ADVERTISEMENT

MsgBox Does Not Appear After The Code Executes.

Jan 5, 2010

I have a code, which works, but I want msgbox appear after the code executes. But it does not.

View 5 Replies View Related

Scrambled Charts After Code Executes

Jan 11, 2007

I have an excel based report consisting of several worksheets. Each worksheet contains VBA code which allows the user to generate custom reports with a series of Excel Charts being generated. I have two related problems with the output/display.
Whenever I save the file the code is triggered causing each sheet to update and scrambling charts. (To fix this I have to leave the report and then re-enter, which then works fine).

Is there anything I can do to stop this from happening? For example, to avoid the code being run on save or to refresh the active screen. A couple of things I should mention: I already have screenupdating turned off so the user is not seeing each action being calculated. At present to avoid the chart display glitches with complex tasks, I have added a line to my code at the end which moves off screen and then returns to the current screen. That ensures the chart no longer lingers on screen, however it does mean that for a split second a phantom graph appears in the middle of the screen before disapearing.

View 2 Replies View Related

Code Executes 8 Times Without Loop Statements.

Feb 8, 2010

I was playing aroung some VBA code, but the code executes 98 times and I don't know why. For example, range A1 contains 5. When I type, say 3, in A1, I want A1 to show me 8 (5+3). Then I may type 10, in this case A1 must show 18 (8+10). And so on. I decided to place entered values in different column, then sum them in A1 with below stated code

View 3 Replies View Related

Make Sure User Is Within Certain Rows Before Code Executes

Aug 14, 2007

I have created code to enter data from the Active cell. However, I would like to edit it so that the user can only make this work if they are on Rows 6 through Row 29.

Here is the
Sub DropDown7_Change()
'Right here I want: If ActiveCell. is in Row 6-29 Then
Application.ActiveCell.Value = Range("D3").Value
Application.ActiveCell.Offset(, 1).Value = Range("F3").Value
Else Msg = "Move into the proper rows"
End Sub
How can I do a check before the code is executed to be sure the users active cell is in Rows 6-29.

View 9 Replies View Related

Mouse Click Freezes Screen - Code Still Executes

Jun 16, 2006

it would be possible to have text disappear after being displayed in Excel for a while.

My son and I played around with games (helping create a photographic memory, I told the youngster!) on the thing and until yesterday neither of us noticed that clicking the mouse anywhere (actually just clicking it) causes the screen to "freeze" while the code still happily erases the text in the background.

Hitting ESC allows the screen to cath up to the actual code EVEN THOUGH the ESC key is actually disabled from breaking the code.

View 9 Replies View Related

Macro Code Executes Very Slowly: Filling Some Cells On Different Worksheets

Nov 9, 2006

I have the following code attached to a userform. It works perfectly (for what I need) although I know it may not be perfectly written. However it does seem to take for ever to execute considering it's just filling some cells on different worksheets. Have I missed something out or got it tied up doing something that takes a long time. If it's just a fact of life that it takes this time then I can live with it.

Private Sub UserForm_Activate()
Dim wsCB As Worksheet, wsLL As Worksheet, wsBond As Worksheet
Dim rngFound As Range
Set wsCB = Sheets("Current Bonds")
Set wsLL = Sheets("Landlords")
Set wsBond = Sheets("Bond")
TenancyStartTxt = Format(TenancyStartTxt, "dd-mmm-yy")
RnwlDteTx = Format(RnwlDteTx, "dd-mmm-yy")
Application. ScreenUpdating = False
10 MyBond = Application.InputBox("Bond number for renewal?", "Bond Number")
strFindMe = MyBond
With wsCB
Set rngData = .Range("c13:c490").......................

View 5 Replies View Related

UDF- Debugging

Nov 2, 2007

I'm using this as a formula =ReturnClient(A1) for example. I put the msgbox in there just to get some feedback on what was going on in there, and it does correcty show the first two digits of the cell it's referencing, but the select case is not ever hit.

Function ReturnClient(refNumCell As Range) As String

Dim cell As Range
Dim clientInit As String
Dim first2Dig As String

For Each cell In refNumCell.Cells

first2Dig = Mid(cell.Value, 1, 2)
MsgBox (first2Dig)

View 9 Replies View Related

Debugging Qtn -

Jul 9, 2008

= OFFSET(AutoMatrix!$B$1,MATCH(C3,TEST),MATCH(H1,AutoMatrix!C1:Z1,0))," ")

Can anyone seem to find any fault on this statement?

When i try to SHIFT,CTRL & ENTER - it's complaining about semi cols. on the extreme right

TEST - is a Name definition
AUTOMATRIX - is Worksheet name

View 9 Replies View Related

Cannot Save While Debugging Error

Aug 16, 2014

I got an error message that I cannot save while debugging. But I have repeatedly hit the stop icon to stop debugging!

I have minimized all the windows (except the spreadsheet as it does not allow me select it and just beeps) to make sure there are no hidden message boxes.

I do not want to force exit and lose my work but I cannot get out of debug mode for some reason!

View 6 Replies View Related

Debugging A Nested Function

Oct 2, 2009

I am receiving an error using the following string as I am not using the OR request properly.

=IF((+$B7&$C7&$D7&$E7)="","",IF(+D7="",+I6,+D7) OR IF(+D7="(blank)","",+D7))

View 2 Replies View Related

Debugging RefEdit Compile Error

Jul 9, 2009

I sent the attached to a user who said it wouldn't run when the 'Draw Venn' button is click - some sort of RefEdit compile error. I can't replicate the error and can't find what's causing the error.

I had started using RefEdit control but couldn't get it to work the way I wanted, so just went with input boxes. I run this on XL07 SP2, other user has XL03 - not sure if that's relevant to the error they're getting.

View 4 Replies View Related

Debugging The Type 13 Mismatch Error Quickly

Apr 29, 2009

What is the quickest/best way to find out exactly what is causing a Type 13 Mismatch error?

Debugging with F8 is taking forever as I have to go through loops that go through 6000+ rows each...

View 10 Replies View Related

Suspend User Defined Function While Debugging

Nov 12, 2006

I am presently trying to debug a large and repetitive piece of code and find that I keep ending up in a function within the same module. I assume this is because the function is being triggered as I manipulate data. If this is the case, does anyone know how to suspend the function while I debug? I guess I could comment it out as I do not need the data it is returning but during normal running, this is not possible and I expect if it is fired off then it will slow things down considerably if my code keeps getting interrupted by interreupts from the function.

View 2 Replies View Related

VBA Code To Sort Data - Run-time Error When No Data To Sort

Mar 4, 2010

I havet he following code which sorts data. If there is no data to sort I keep on getting a run time error. Could I add something to my code to prevent the run-time error, as sometime there won't be any data to sort. The code runs when I switch to the worksheet in question.

Sub SortMeetings()
Dim iCTR As Integer
Dim yCTR As Integer
Dim zCTR As Integer

zCTR = 11
For iCTR = 12 To 23
For yCTR = 1 To 10
If Len(Range("D" & iCTR).Offset(0, yCTR)) 0 Then
Range("AA" & zCTR).Value = Format(Range("D" & iCTR).Offset(0, yCTR), "HH:MM") & " " & Range("D" & iCTR).Value
zCTR = zCTR + 1
End If
Next yCTR
Next iCTR
Range("AA11:AA" & zCTR).Select
Selection.Sort Key1:=Range("AA11"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

View 9 Replies View Related

Macro That Executes Upon Open

Aug 18, 2009

I have the code below that puts in the file name, date and time and page numbers. That works fine, however, I changed the name of this macro to AutoOpen thinking that this would execute upon opening an Excel doc -- I think this works for Word, that's why I tried it. Anyway, it doesn't execute upon opening an Excel doc. Can anyone tell me how to get that going? Also, the font of the file name comes at at 12 point while the other entries come in at 6 pt - any way of making these all consistent, say 8 pt? Thanks for your help.

By the way, I'm a novice at Excel macros so being fundamental in your answer would be very helpful (i.e., don't know why there is a distinction between Regular Modules, Worksheet Modules and Workbook Modules. Seems to me I should always be creating Regular Module macaros so that they are accessible to all Excel docs but, again, I'm new at this so I'll have to read more about these items as well).

View 6 Replies View Related

Does An Add-in Script Executes From A Remote Computer?

Dec 2, 2009

i have an excel file with some vba code that includes some functions of a third party add-in also. It works fine when i open this file on the host machine. (Assuming host machine is where the Excel File is placed) But when i try to open the same file from some other computer(remotely/by sharing folder), the add-in malfunctions because of one reason that it considers the remote machine to be its host. Is it possible to set a permanent host machine for that any add-in. Or is it possible for that add-in / Excel file to do its calculations and updation of links on the host machine where it is placed.

View 4 Replies View Related

Timer Running While Macro Executes

Jun 2, 2013

Is there a way to have a count up timer display in a cell while a macro is running, and then stop when the macro stops

View 5 Replies View Related

Excel 2007 :: Divide Operation Executes More Than Once

Mar 12, 2012

Using Excel 2007.

My vba code seems to be dividing a range by 1M more than 1 time

My initial value is 51543942

After by code runs the display is 0.00 MB and the value in the formula bar is 0.000000000051543942 or 5.15439E-11

I would like the final display to be 51.54 MB

what I might be doing wrong?

Code:
'Format columns
r = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range("C2:C" & r)
.Range("IV1").Value = 1000000
.Range("IV1").Copy

[code].....

View 8 Replies View Related

Excel 2007 :: VBA Macro Executes When Press ESC?

Jun 4, 2012

I have an excel workbook created in 2007, but now running in 2010. In the workbook I have a form Combo box with a macro assigned to it. When I click any value in the combo dropdown list the macro executes. In 2007 I could click the combo dropdown box and then press ESC to exit from the form control without executing the assigned macro. Similarly I could also click on any other cell to exit from the control without executing the macro.

But, in 2010 if I press ESC or click on any other cell the macro will execute. Sometimes I want to be able to exit from the combo box without executing the macro.

View 3 Replies View Related

Sort Data Without Using Code

Dec 12, 2009

Can a sorted array be produced from a table without using code? The attached sample gives a better idea of what I'm trying to do.

View 4 Replies View Related

Auto Sort Code

Dec 19, 2008

Need to add an auto sort code to the end of this worksheet module 2, so that after all the dates are figured, it will sort by this date, no matter how many dates there are.

View 5 Replies View Related

Sort Code In Macro

Feb 3, 2009

I have a sort function in one of my macro. Sometimes it works, sometimes it does not. I can't figure out why. I am trying to sort columns A-F and each column has a header.

View 2 Replies View Related

Auto Sort Using VBA CODE?

Jan 4, 2013

I have created a spreadsheet using columns A to G with row one as the following headings Surname, Middle Name, First Name, Alias, Offences, Temp# and CRO#.

The thing is I tried writing a VBA code to let the Surname(column A) automatically sort in ascending order but it doesn't work.

where sorting begins in Column A with the range A2:A5000 and at the same time row one being frozen.

View 8 Replies View Related

Code For SORT Is Not Working

Jan 20, 2010

why my vba code for sort is not working here. When I do F8 it simply passes the sort code (the one in red) with no action happening. Why.? and do I need to do. Thanks.


Sub Macro1()
'
' Macro1 Macro
'
Sheets(1).Copy Before:=Sheets(1)
ActiveSheet.Rows("1:2").EntireRow.Delete shift:=xlUp
ActiveSheet.Columns("C:M").EntireColumn.Delete shift:=xlToLeft

With ActiveSheet
With Range("A1:C10000")
.Sort key1:=Range("B2"), order1:=xlAscending, Header:=xlGuess,_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End With
End With

View 9 Replies View Related

Sort Data With Code

Dec 20, 2006

I have a set of Data and I would like to order the data based on a column which is G and contains Dates. I would like to order the data Desc order but this must be done in VBA as its a monthly report and needs to be scheduled.

View 3 Replies View Related

Excel 2007 :: VBA Find Macro Executes But Copy / Paste Not Working

Mar 23, 2012

Excel 2007, Windows XP Pro

Dim strLoan As String
Dim longCat As Long
Dim rHere As Range
Range("A1").Select
[Code] .....

I can see the cursor move to all the desired cells when this macro executes; it just isn't dropping any data where it should be. I have been working at this stage for the last five hours with no success. I don't know whether my copy-paste methodology is broken or if it is my selection criteria

View 1 Replies View Related

Excel 2010 :: Automation Error When Click On Button That Executes Macro

Aug 22, 2012

I keep getting an automation error in excel 2010 when I click on the button that executes the macro. Excel crashes when the box appears. There is no code in the error box. Why I get this error message.

View 2 Replies View Related

Add Custom Sort List To VBA Code

Mar 12, 2009

I have a Macro that I have recorded that autosorts several columns for me. This works fine as I have 5 columns that need to be sorted in a particular way.

The problem is that I have a custom list which I stored using the: Tools>Options>Custom List tab. Now this works fine on my pc as I have the custom list stored on my PC.however the problem is when someone else uses it on their PC it may no longer work as they won't have my custom list stored on their PCs.

Is there a way to store the custom list in a Macro and then use that list to sort to the criteria needed. For example "One, Two, Three, Four" will not store in alphabetically, so hope would I be able to sort so that they would appear in a chronological manner? Below is the code for the Macro I recorded. As you can see one of the lines says "OrderCustom:=6"; this I am assuming is reading from the list I created.

View 2 Replies View Related

Code To Sort All The Worksheets In A Workbook

Oct 8, 2008

code to sort all the worksheets in a workbook...

View 13 Replies View Related







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