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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Oct 8, 2008
code to sort all the worksheets in a workbook...
View 13 Replies
View Related
