Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dataform and sorting macros
I created a dataform macro in Excel with the Auto_Open
name. I need the dataform to be for just the headings in columns A-G, although there are column headings in A-P, H- P aren't needed for the average data entry person. Does anyone have any suggestions? I also need to have a column G sort ascending upon close, Auto_Close macro. I got it started, but since Row 1 is a merged cell, and isn't included in the macro, I keep getting a debugging error. Need to sort G2--end of data. Any suggestions? I can provide copies of the macro wording that I tried to use if you need to see it. Thanks! Beth |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dataform and sorting macros
When working on the dataform macro, I get an error -
debugging message - when I run this at the line: Set myRng = Worksheets("sheet1").Range("a:g") Any suggestions? Thanks, Beth -----Original Message----- If you name your range, excel will look there to find your database: Option Explicit Sub testme() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("a:g") myRng.Name = "database" Worksheets("sheet1").ShowDataForm End Sub In your auto_close, maybe you could sort your data with headers:=xlno. (just start with the real data--row 2???). with worksheets("sheet1") .range("a2:g" & .cells(.rows.count,"A").end (xlup).row).sort ..... end with I used the last cell in column A to find the end of data. And I only sorted through G. Fix both of those to match your data.) Beth Mc wrote: I created a dataform macro in Excel with the Auto_Open name. I need the dataform to be for just the headings in columns A-G, although there are column headings in A-P, H- P aren't needed for the average data entry person. Does anyone have any suggestions? I also need to have a column G sort ascending upon close, Auto_Close macro. I got it started, but since Row 1 is a merged cell, and isn't included in the macro, I keep getting a debugging error. Need to sort G2--end of data. Any suggestions? I can provide copies of the macro wording that I tried to use if you need to see it. Thanks! Beth -- Dave Peterson . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dataform and sorting macros
Okay, I'm confused on the sorting macro wording that you
listed. What is headers-xlno? I'm fairly new to macros, so spelling it out like you did for the dataform really helps me. My sort at auto_close is now sorting the data, but it is moving the header row of A2:G2 below the data when it sorts. So instead of A2:G2 showing the column headers, the column headers are moved to A11:G11 and the data is sorted (correctly) in A2:G10. The column headers for H2:P2 stay in the same place. Thanks for the help! Beth -----Original Message----- If you name your range, excel will look there to find your database: Option Explicit Sub testme() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("a:g") myRng.Name = "database" Worksheets("sheet1").ShowDataForm End Sub In your auto_close, maybe you could sort your data with headers:=xlno. (just start with the real data--row 2???). with worksheets("sheet1") .range("a2:g" & .cells(.rows.count,"A").end (xlup).row).sort ..... end with I used the last cell in column A to find the end of data. And I only sorted through G. Fix both of those to match your data.) Beth Mc wrote: I created a dataform macro in Excel with the Auto_Open name. I need the dataform to be for just the headings in columns A-G, although there are column headings in A-P, H- P aren't needed for the average data entry person. Does anyone have any suggestions? I also need to have a column G sort ascending upon close, Auto_Close macro. I got it started, but since Row 1 is a merged cell, and isn't included in the macro, I keep getting a debugging error. Need to sort G2--end of data. Any suggestions? I can provide copies of the macro wording that I tried to use if you need to see it. Thanks! Beth -- Dave Peterson . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with dataform and sorting macros
Actually, the "s" was a typo.
When you recorded a sort macro, what did the code look like? For example (in xl2002), I got this when I recorded a macro: Range("A2:W26").Select Selection.Sort Key1:=Range("G2"), Order1:=xlAscending, Key2:=Range("C2") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom The Header:= can be set to xlYes (it has headers), xlNo (no headers) and xlGuess (let xl decide). If I know, then I won't let excel guess. I used two sort fields, G and C. (If you don't need two, then don't specify them when you're recording the macro.) And I wasn't sure what columns you wanted sorted and what end of data meant. But if I assumed that column G always had something in it (so I could use that to determine the end), I could modify my recorded code to look more like: dim myRng as range dim LastRow as long with worksheets("sheet1") '<--- fix this again! lastrow = .cells(.rows.count,"G").end(xlup).row set myrng = .range("a1:P" & lastrow) 'adjust P to be last column 'now comes the recorded part myrng.sort Key1:=.Range("G2"), Order1:=xlAscending, _ Header:=xlNo, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom end with Watch out for the sheet name and last column. And if you have a better column that can define the lastused row, modify that "Lastrow = " line, too. If this doesn't work, post what you used. And be careful with your sort. If you don't include H:P in the range sorted, then those cells won't move. I'm not sure if that portion needed to be sorted. Beth Mc wrote: Okay, I'm confused on the sorting macro wording that you listed. What is headers-xlno? I'm fairly new to macros, so spelling it out like you did for the dataform really helps me. My sort at auto_close is now sorting the data, but it is moving the header row of A2:G2 below the data when it sorts. So instead of A2:G2 showing the column headers, the column headers are moved to A11:G11 and the data is sorted (correctly) in A2:G10. The column headers for H2:P2 stay in the same place. Thanks for the help! Beth -----Original Message----- If you name your range, excel will look there to find your database: Option Explicit Sub testme() Dim myRng As Range Set myRng = Worksheets("sheet1").Range("a:g") myRng.Name = "database" Worksheets("sheet1").ShowDataForm End Sub In your auto_close, maybe you could sort your data with headers:=xlno. (just start with the real data--row 2???). with worksheets("sheet1") .range("a2:g" & .cells(.rows.count,"A").end (xlup).row).sort ..... end with I used the last cell in column A to find the end of data. And I only sorted through G. Fix both of those to match your data.) Beth Mc wrote: I created a dataform macro in Excel with the Auto_Open name. I need the dataform to be for just the headings in columns A-G, although there are column headings in A-P, H- P aren't needed for the average data entry person. Does anyone have any suggestions? I also need to have a column G sort ascending upon close, Auto_Close macro. I got it started, but since Row 1 is a merged cell, and isn't included in the macro, I keep getting a debugging error. Need to sort G2--end of data. Any suggestions? I can provide copies of the macro wording that I tried to use if you need to see it. Thanks! Beth -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros for sorting data | Excel Worksheet Functions | |||
How to activate "new" tab in Dataform in EXCEL through VB | Excel Discussion (Misc queries) | |||
How to activate "New" tab in Dataform through VB Code | Excel Discussion (Misc queries) | |||
sorting with macros | Excel Discussion (Misc queries) | |||
custom dataform | Excel Programming |