Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sort Marco won't work in 2010
My office has just converted us to excel 2010. My lovley spreadsheet macro that made me the envy of the office no longer works and everyone now wants my blood.
The sheet is protected. I have a macro that will select a variable amount of rows and sort alphabetically. It looks for the hidden cell xdummy then selects every row above it up to row 13, then sorts A -Z. Except now it doesn't. Help! Sub Sort() ' ' Sort Macro ' 'Sheets("Cashflow").Unprotect "Cashflow2012" Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Rows("13:" & ActiveCell.Row - 1).Select Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Cashflow").Protect "Cashflow2012" End Sub Last edited by GSENSEI : August 31st 12 at 04:06 PM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Marco won't work in 2010
On Friday, August 31, 2012 8:01:35 AM UTC-7, GSENSEI wrote:
My office has just converted us to excel 2010. My lovley spreadsheet macro that made me the envy of the office no longer works and everyone now wants my blood. The sheet is protected. I have a macro that will select a variable amount of rows and sort alphabetically. It looks for the hidden cell xdummy then selects every row above it up to row 13, then sorts A -Z. Except now it doesn't. Help! Sub Sort() ' ' Sort Macro ' 'Sheets("Cashflow").Unprotect "Cashflow2012" Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Rows("13:" & ActiveCell.Row - 1).Select Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Cashflow").Protect "Cashflow2012" End Sub -- GSENSEI Give this a try, from excel 2010. Option Explicit Option Compare Text Sub ASort() 'Sheets("Cashflow").Unprotect "Cashflow2012" Dim i As Integer Dim j As Integer Dim k As Integer Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate i = ActiveCell.Row j = 13 k = i - j ActiveCell.Offset(-(i - j - 1), 0).Resize(k - 1, 1).Select Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'Sheets("Cashflow").Protect "Cashflow2012" End Sub HTH Regards, Howard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Marco won't work in 2010
On Friday, August 31, 2012 10:01:35 AM UTC-5, GSENSEI wrote:
My office has just converted us to excel 2010. My lovley spreadsheet macro that made me the envy of the office no longer works and everyone now wants my blood. The sheet is protected. I have a macro that will select a variable amount of rows and sort alphabetically. It looks for the hidden cell xdummy then selects every row above it up to row 13, then sorts A -Z. Except now it doesn't. Help! Sub Sort() ' ' Sort Macro ' 'Sheets("Cashflow").Unprotect "Cashflow2012" Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Rows("13:" & ActiveCell.Row - 1).Select Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Cashflow").Protect "Cashflow2012" End Sub -- GSENSEI try Sub Sort() with Sheets("Cashflow") ..Unprotect "Cashflow2012" set mf=.Cells.Find(What:="xdummy",lookIn:=xlFormulas,L ookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) if not mf is nothing then mr=mf.row ..Rows("13:"mr).Sort Key1:=.Range("a13"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom end if ..Protect "Cashflow2012" end with End Sub if that doesn't work send this msg and file to dguillett1 @gmail.com |
#4
|
|||
|
|||
Hi folks,
I've tried both suggestions but neither one is working still. I can't send the actual sheet out due to office policy. But to make things worse it turns out we can't even sort manually when sheet is protected. I can get the protection to turn off and on in the macro that is fine. Its now my ability to sort that is the problem It seems to have no problem findind and selceting the correct range its just the sorting that gives issues. this bit is in yellow. Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Any ideas? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort Marco won't work in 2010
On Friday, August 31, 2012 10:01:35 AM UTC-5, GSENSEI wrote:
My office has just converted us to excel 2010. My lovley spreadsheet macro that made me the envy of the office no longer works and everyone now wants my blood. The sheet is protected. I have a macro that will select a variable amount of rows and sort alphabetically. It looks for the hidden cell xdummy then selects every row above it up to row 13, then sorts A -Z. Except now it doesn't. Help! Sub Sort() ' ' Sort Macro ' 'Sheets("Cashflow").Unprotect "Cashflow2012" Cells.Find(What:="xdummy", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate Rows("13:" & ActiveCell.Row - 1).Select Selection.Sort Key1:=Range("a13"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Sheets("Cashflow").Protect "Cashflow2012" End Sub -- GSENSEI In mine, try changing Rows("13:"mr).Sort Key1:=.Range("a13"), _ to Rows("13:"mr).Sort Key1:=.cells(mf.row,"a"), _ |
#6
|
|||
|
|||
GSENSEI[/i][/color] In mine, try changing Rows("13:"mr).Sort Key1:=.Range("a13"), _ to Rows("13:"mr).Sort Key1:=.cells(mf.row,"a"), _[/quote] Still doesn't like it. god i hate 2010! (this seems fine) Set mf = .Cells.Find(What:="xdummy", LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False) If Not mf Is Nothing Then mr = mf.Row This highlights in red Rows("13:"mr).Sort Key1:=.cells(mf.row,"a"), _ Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End If And I get a Complie error message. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2010 to auto-generate an email to recipients in outlook 2010 | Excel Programming | |||
Form 4 Range of time from 1/20/2010 4:00 AM To 1/21/2010 10:00 AM | Excel Worksheet Functions | |||
How do I make an exact copy of my bill work sheets to year 2010 | Excel Discussion (Misc queries) | |||
customer sort order in a marco | Excel Programming |