Macro help #12

Hi,
I am working on a macro in Excel 2003 Pro, and everytime I go to sort a 
column of data.  There are about 10 rows of data in about 200+ columns.  I 
can only get the macro to sort the column that I edited it with.  How can I 
create the macro, so each time I click on the first row of each column, click 
the macro button I will assign to the toolbar, then that row will be sorted, 
and do this for each column?

Any help at all would be greatly appreciated,
Jeff Garrett (user_jeff@hotmail.com)
0
Garrett (8)
11/30/2004 3:21:06 PM
excel.misc 78881 articles. 5 followers. Follow

11 Replies
550 Views

Similar Articles

[PageSpeed] 28

Hi Jeff

please post your current code and then we can suggest changes to it.

Cheers
JulieD

"Jeff Garrett" <Jeff Garrett@discussions.microsoft.com> wrote in message 
news:0C7E7FE2-A2BC-4F36-A680-C86C80128577@microsoft.com...
> Hi,
> I am working on a macro in Excel 2003 Pro, and everytime I go to sort a
> column of data.  There are about 10 rows of data in about 200+ columns.  I
> can only get the macro to sort the column that I edited it with.  How can 
> I
> create the macro, so each time I click on the first row of each column, 
> click
> the macro button I will assign to the toolbar, then that row will be 
> sorted,
> and do this for each column?
>
> Any help at all would be greatly appreciated,
> Jeff Garrett (user_jeff@hotmail.com) 


0
JulieD1 (2295)
11/30/2004 3:43:02 PM
Sub Sort()'where sortrange is a named range to sort a1:aa200
[SortRangeAdd].Sort Key1:=Cells(1, activecell.column), Order1:=xlAscending,
Orientation:=xlTopToBottom
End Sub
-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Jeff Garrett" <Jeff Garrett@discussions.microsoft.com> wrote in message
news:0C7E7FE2-A2BC-4F36-A680-C86C80128577@microsoft.com...
> Hi,
> I am working on a macro in Excel 2003 Pro, and everytime I go to sort a
> column of data.  There are about 10 rows of data in about 200+ columns.  I
> can only get the macro to sort the column that I edited it with.  How can
I
> create the macro, so each time I click on the first row of each column,
click
> the macro button I will assign to the toolbar, then that row will be
sorted,
> and do this for each column?
>
> Any help at all would be greatly appreciated,
> Jeff Garrett (user_jeff@hotmail.com)


0
Don
11/30/2004 3:43:53 PM
posting your code would help.
Macros do not respond to mouse clicks or other forms of 
input divices. the code replaces all these. mouse clicks 
can trigger code to run but they can't be used as code 
parameters.
you might try  in the code to sort by Activecell but i 
have never tried that and am not too sure it would work.

>-----Original Message-----
>Hi,
>I am working on a macro in Excel 2003 Pro, and everytime 
I go to sort a 
>column of data.  There are about 10 rows of data in about 
200+ columns.  I 
>can only get the macro to sort the column that I edited 
it with.  How can I 
>create the macro, so each time I click on the first row 
of each column, click 
>the macro button I will assign to the toolbar, then that 
row will be sorted, 
>and do this for each column?
>
>Any help at all would be greatly appreciated,
>Jeff Garrett (user_jeff@hotmail.com)
>.
>
0
anonymous (74722)
11/30/2004 3:47:18 PM
Sub Macro1()
    Dim varColumn As String
    Dim varNo As String
    For varColumn = "A" To "IV"
    varNo = varColumn & 1
    Range([varNo]).Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range(ActiveCell, ActiveCell.End(xlUp)).Select, 
Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Next varColumn
End Sub


Basically, I want to go from Cell B1 to Cell IV1, and call this macro to 
sort the column that is currently selected descendingly.  It will be all 
integers, some non-entries representing a 0.  The whole reason to do a macro 
is because there is like 200 entries in the worksheet.

Any and all suggestions are welcome.
0
11/30/2004 6:01:07 PM
try this to sort EACH column individually. Is that what you want?

Sub SortEachColumn()
lc = Cells(1, "iv").End(xlToLeft).Column
For i = 1 To lc
lr = Cells(Rows.Count, i).End(xlUp).Row
Range(Cells(2, i), Cells(lr, i)).Sort key1:=Cells(2, i), Order1:=xlAscending
Next i
End Sub


-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Jeff Garrett" <JeffGarrett@discussions.microsoft.com> wrote in message
news:C8A248F6-3A18-4423-B81B-BF9897DB7AE0@microsoft.com...
> Sub Macro1()
>     Dim varColumn As String
>     Dim varNo As String
>     For varColumn = "A" To "IV"
>     varNo = varColumn & 1
>     Range([varNo]).Select
>     Application.CutCopyMode = False
>     Selection.Sort Key1:=Range(ActiveCell, ActiveCell.End(xlUp)).Select,
> Order1:=xlDescending, Header:=xlGuess, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>         DataOption1:=xlSortNormal
>     Next varColumn
> End Sub
>
>
> Basically, I want to go from Cell B1 to Cell IV1, and call this macro to
> sort the column that is currently selected descendingly.  It will be all
> integers, some non-entries representing a 0.  The whole reason to do a
macro
> is because there is like 200 entries in the worksheet.
>
> Any and all suggestions are welcome.


0
Don
11/30/2004 6:38:25 PM
Yes.  Sort each column individually.  Then, iterate over each column.  Do I 
have to dim lc, lr as variables?  What kind though?  String?  Char?
0
11/30/2004 7:53:10 PM
Order1:=xlAscending
This means ascending order (lowest to highest) right?  I want descending 
order.  So I put this:
Order1:=xlDescending

Correct?
0
11/30/2004 7:55:07 PM
Yes, try it.

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Jeff Garrett" <JeffGarrett@discussions.microsoft.com> wrote in message
news:0FBA3EBA-BE38-4F3C-B369-0CAD80E857F2@microsoft.com...
> Order1:=xlAscending
> This means ascending order (lowest to highest) right?  I want descending
> order.  So I put this:
> Order1:=xlDescending
>
> Correct?


0
Don
11/30/2004 7:58:18 PM
That's worked Don.  The only problem is it's doing too much.  It should only 
sort rows 2-12 inclusive, and this is what we last came up with: 
            lr = Cells(Rows.Count, i).End(xlUp).Row
This does too much.  How do you do only sort the rows 2-12?
0
12/1/2004 8:07:06 PM
It is sorting row 2 - 12 if 12 is the last row with data. It would be 234 if
that was the last row with data. I thought you wanted it to be adaptable for
the amount of date in each column. If not, change to 12.....

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Jeff Garrett" <JeffGarrett@discussions.microsoft.com> wrote in message
news:158EAC67-3E35-4BB6-965D-E7926FA5CE62@microsoft.com...
> That's worked Don.  The only problem is it's doing too much.  It should
only
> sort rows 2-12 inclusive, and this is what we last came up with:
>             lr = Cells(Rows.Count, i).End(xlUp).Row
> This does too much.  How do you do only sort the rows 2-12?


0
Don
12/1/2004 8:36:52 PM
So lc must be the last row with acceptable data.  It makes sense now.
0
12/1/2004 8:47:08 PM
Reply:

Similar Artilces:

Creating a group of cells. Need Help Please.
Havn't used excel in a while and I need to create a group of cell corresponding to an input of a min and a max. Here are the details. On one sheet I have a box where you enter th min and a box where you enter the max. In another sheet I want column starting at A2 to output (MIN,A2+1000,A3+1000,....MAX) ho would I do this -- Thundersix ----------------------------------------------------------------------- Thundersixx's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3055 View this thread: http://www.excelforum.com/showthread.php?threadid=50207 Name the...

REQ: Can Someone Help Me With This Outlook XP Question?
Hello All: I use Word to edit my e-mail msgs in Outlook XP. I had to reinstall Office the other day and now whenever I want to start a new e-mail or reply to an e-mail I get a warning that comes up: "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected it may be a virus and you should choose 'No'" There is a box that asks for the amount of time to allow the access: 1 to 10 minutes. Do I have to have specific settings for my Outlook address book? I use the Contact area in Outlook for addresses. I have ...

IWAN & IUSR bei Crystal Reports? *help*
Hi NG, my problem is that i can see the crystal reports, but when i open one i get "more information is needed". But till yesterday i could open reports!! Now after some search i see that i have no IUSR_servane and no IWAM_servername. how could this happen? My system is AD, SQL, CRM and each of them is one a seperate 2003 server. Please let me know if you have any suggestions. Regards Nicolas F�hrs sound strange with the IWAN and IUSR. I offten have this problem. There are a techknowledge article with 13 resoluti...

Macros for worksheet copy & paste?
I have 6 different excel files. How can I combine all 6 of them in one single excel file under individual worksheets (E.g. Worksheet1 for File1, worksheet2 for File2 ....etc)? I do not wish to copy & paste it manually. Can I use a macro to take care of it? If so, where can I find further info on how to go about it? THANKS!! Assuming (1) this is a one-time need, and (2) your workbooks have only one sheet each, this would probably be faster done by hand: 1) With all the files open choose one of the files to be the receiving file. 2) Select the sheet in the subsequent books, one at...

Please help..with a formula. I don't know code.
I have a long list of numbers - values in a file X, and I want to fin and replace those values in a even larger list in a file Z an highlight those values in Z -- Message posted from http://www.ExcelForum.com Hi not really sure what you're trying to achieve. What do you want to replace, etc. You may give an example (plain text - no attachment please) >-----Original Message----- >I have a long list of numbers - values in a file X, and I want to find >and replace those values in a even larger list in a file Z and >highlight those values in Z. > > >--- >Message...

Help With Strange Error When Trying to Save Any Record
I am getting astrane error happens when try to create any new record (Customer, Item, Vendor or Account), the system gives me a message that "Save Operation Failed" and when i click "More Info" the error message is "Could not find stored procedure 'DYNAMICS.dbo.aagGetCompanyStatus'", although i have created alot of items, customers, accounts and vendors before but suddenly this error appeared. Any quick help will be highly appreciated. It would appear that the AAG has something to do with a product produced by American Association of Geographers. I...

Need a default email account for all users, need help.
I have a tablet PC running WinXP Tablet with Outlook 2003. This tablet will connect to our exchange server via VPN. How can I set it up so that everyone that logs onto their account can access one (the same) email account. The problem is that I dont know at this point all of the users however anyone using the tablet will use one generic email account. So how can I set Outlook to default to this account so that no matter who logs on they will use this account? Thanks! Shane ...

help with preview pane and "read receipts"
Hi, I'm new to Outlook administration. We run Outlook on a server wit Exchange. Is it possible to setup Outlook on client PC's so that th preview pane cannot be activated by individual users? Also, can the blocking of "read receipts" by individual PC users b prohibited within Outlook, at the server level? If not, is there an other way to do it? My reasoning for wanting to do the above two things is to make th "read receipts" function work more effectively. Thanks for your suggestions. : ----------------------------------------------- ~~ Message posted from h...

Need help with formula 01-13-10
I am trying to adapt a formula in I2 from another spreadsheet that works well, but won't in mine. I've traced the error, but I would need help to understand the help it gives! My formula is this: =IF(J2="0-Jan-00","To be advised",WORKDAY(J2,1,NWD)). I have a worksheet in the same workbook with a list of non-workdays, and defined the column of dates with the name "NWD". What I expect the formula to do is this: If J2 is Feb. 4, it would give Feb. 5 in cell I2 because Feb. 5 is NOT a non-workday in NWD. But if J2 is Feb. 5, and Feb. 6 and...

DEADLINE... PLEASE HELP! Stacked Bar chart?
I'm not even sure how to ask the question so here's what I have... 2003 2004 2005 Actual/Goal Actual/Goal Actual/Goal Me 1009/1061 591/866 658/897 Comp. A 966/1012 633/811 624/808 Comp. B 699/744 450/593 480/607 Comp. C 957/1005 642/821 665/838 I wanto to show a bar for each competitor, for each year, so there will be 4 bars for each year. Each bar showing Actual performance & Performance Goal...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Help with Do...Loop
Hi I need a check to be done to see if column a has a number in it then to check if column b has a number. If column B doesn't have a number then I need it to stop and give a msgbox, When column A doesn't have data then I need the loop to stop as we dont have to check column b The code below is what I have but when I try to run it it keeps saying LOOP WITHOUT DO. I hope someone can help me as I am not very good with loops. mykeycode = Range("B32") mysell = Range("N32") Do mykeycode = mykeycode + 1 mysell = ...

Help on Macro or Formula
Hi, i hope someone can help me. i need to create a formula that sits in a cell and looks for data. ( obvioiusly ). however, the formula needs to be in place even though the file from ehere the data comes from might not be there yet. ( i have to create a book that when a new file is created, the links are already in place ). i think it could work with an IF type formula for ( if B2="",""). here is my information. Cell description: A2 = Job no. B2 = Client Name D2 = Actual Spend on project Register!D2 = Job Description Register!H2 = Quoted Amount my path is S:\Clients\...

Excel 2000 macro security Level
Hi! I am running an ASP.NET application which ideally opens an Exce spreadsheet with a macro included in it. When I try opening spreadsheet without a macro, everything works fine, but with macros, i hangs on the opening command. I tried to go to Excel 2000 and go t Tools->Macros->Security Level->Low but it still hangs when I try t open the macro spreadsheet. All works fine with the spreadsheet no containing macros, so it really is a question of macro security. But put it to low, so I don't understand how it is still not opening!! An help would be greatly appreciated! Thanks and...

Need help with update sql plus filter
I have the following update sql (copied from the query design view) UPDATE ListQry SET ListQry.ApprovalStatusID = [Forms]![OpeningForm]![Responsibility] WHERE (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID)>300)) OR (((ListQry.ApprovalStatusID)<[Forms]![OpeningForm]![Responsibility] And (ListQry.ApprovalStatusID)>-1) AND ((ListQry.OtherStatusID) Is Null)); ApprovalStatusID is an integer OtherStatusID is an integer ListQry is the recordsource for my form. I would like to add the f...

Running an Excel Macro from an Access Command Button
Hi , Can anyone help me with this, I am trying to run an excel macro from an access command button. I want the macro to Bold the Headings on a excel spreadsheet. The code runs without any errors but the macro doesnt seem to work, I think I have got the line ExcelApp.ExecuteExcel4Macro "RUN(""PERSONAL.XLS!BoldHead"")" wrong The code: Private Sub cmdRunMac_Click() Dim ExcelApp As Excel.Application Dim ExcelBook As Excel.Workbook Set ExcelApp = Excel.Application ExcelApp.Visible = True Set ExcelBook = ExcelApp.Workbooks.Open("C:\Documents and Settings\...

New Named Range Created Each Time Data Imported into Excel via Macro
I have noticed that each time I import data into an Excel spreadsheet via a macro, a new named range (for the same range) is created. This does not pose a problem, but after a while, I'll have a huge number of named ranges that will never be used. Why does Excel name the range and how can I stop this? I noticed in the recorded macro, there was a line .Name = "drd_5". I commented this out to see what would happen, but it just renamed the range "ExternalData_5". The next one was "ExternalData_6", etc. Thanks, Carroll Rinehart You could refresh with differen...

Error Code 80070424 12-21-09
I keep getting this Windows update error every time I boot my PC. In the window, it says Windows can't check for updates. Does anyone have any ideas? Recently I installed BitTorrent so could that be my problem? "Norm" <Norm@discussions.microsoft.com> wrote in message news:D2E5CF0C-D31B-4034-B4A8-A8204763A623@microsoft.com... > I keep getting this Windows update error every time I boot my PC. In the > window, it says Windows can't check for updates. Does anyone have any > ideas? > > Recently I installed BitTorrent so could that be my proble...

vba code to enable macros
Hi, Can we write a vba code to enable macros. In most of the sites it says we cant do that . The only thing we can do is change the macro setting or work around is hide the sheet which has macros. But none of them will work in my case. Can we change an excel security setting to from macro on open so that macros are enabled. No. If we can do that with code, then so could a hacker with bad intentions. Mike F "varsha12" <varsha12@discussions.microsoft.com> wrote in message news:DBEB1614-C956-491B-B351-02DD9F711891@microsoft.com... > Hi, > Can we w...

Hyperlink File Help
I am needing some major help. I have a file with hyperlinks in column F that link to a file on our server. I am needing to test to see if the file exists and if it does, copy the file to a folder in my documents called (CapturedFiles) and if it doesn't format the cell color to red. Can VBA do this and if so how? Any help would be greatly appreciated. Thanks in advance. Fileserver or webserver ? Tim On Nov 23, 7:20=A0am, Aaron <Aa...@discussions.microsoft.com> wrote: > I am needing some major help. =A0I have a file with hyperlinks in column = F that > l...

Help please user not showing in 5.5 GAL but is in exchange 2003 GA
Up until today I have been bable to add users fine and their address would appear in both the 5.5 GAL and the exchange 2003 GAL. Is a single site with 2 5.5 servers and 1 exchange 2003 server. When I add a new user now through users and computers and put the mailbox on the new exchange 2003 server the user gets his email addresses and appears in the GAL on the 2003 server but people connected the the old 5.5 servers cannot see it. When I open the 5.5 exchange admin tool again if connected to one of the old 5.5 server I cannot see the person I just created but when connected the the 20...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

Macro Security Setting not applying ...
Help! I have a workbook that will sporadically lose it's ability to run macros even though the macro security setting is set to 'medium'. i.e. - no message box comes up when opening the file and when trying to run any macros, the "cannot run macros ..." box comes up. The only way I can fix this is to open the file, select macro-security setting "low", save the file, open it again, and then return the setting to "medium". This file is out on a shared drive and many of my users need to access this - so the quick-fix noted above really isn't...

help with a sub
Hi, can anybody tell me why the following code fails at FormatConditions.Add Private Sub CommandButton1_Click() Dim Sh As Worksheet Dim lngLastRow As Long Set Sh = ActiveWorkbook.ActiveSheet lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row Range("A4:E" & lngLastRow).Activate Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(ROW(),2)=0" Selection.FormatConditions(1).Interior.ColorIndex = 24 End Sub Thanks -- Traa Dy Liooar Jock You have an extra open paren just before MOD: &qu...

cdrom.sys corrupt in Win7
Yesterday Win7 decided to no longer show my 2 LiteOn DVDRW drives. I've tried to re-install/repair the driver (6.1.7600.16385) and everytime I get the same response = my current driver is good. BUT, then when I check with Device Manager, it shows that the drives are not working. Can anyone help me get a new cdrom.sys installed into the system32/drivers folder? Booting up with the Win7 DVD will work. But I can't find the cdrom.sys on the disk. No other repair options are there to get this fixed. Help would certainly be appreciated. I don't want to have to start all o...