Macro - Data Sort -Variable Selection

Hi all!!!

I am trying to get my macro to run a sort in a non specific cell for column 
D.....there are many steps up to this point, I will include the last couple 
in the code...here is what I have so far....

    Columns("C:C").Find(What:="x").EntireRow.Insert
    Columns("C:C").Find(What:="x").EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Instead of the range being specific for  D63, I want it to select the D 
column after if finds X to the end of the wkst.  Any ideas on how I can 
accomplish this???

Thanks in advance......

    
0
6/3/2005 4:28:10 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
421 Views

Similar Articles

[PageSpeed] 10

..Sort is pretty forgiving.  Didn't it work using D63?

I've used:

....key1:=range("d:d"), ...

And that works, too.

Frantic Excel-er wrote:
> 
> Hi all!!!
> 
> I am trying to get my macro to run a sort in a non specific cell for column
> D.....there are many steps up to this point, I will include the last couple
> in the code...here is what I have so far....
> 
>     Columns("C:C").Find(What:="x").EntireRow.Insert
>     Columns("C:C").Find(What:="x").EntireRow.Select
>     Range(Selection, Selection.End(xlDown)).Select
>     Range(Selection, Selection.End(xlToRight)).Select
>     Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> 
> Instead of the range being specific for  D63, I want it to select the D
> column after if finds X to the end of the wkst.  Any ideas on how I can
> accomplish this???
> 
> Thanks in advance......
> 
> 

-- 

Dave Peterson
0
ec357201 (5290)
6/3/2005 9:18:07 PM
Selecting "d:d" works, but it will select the whole column... I only want to 
sort the selection after I find x and sort that....

"Dave Peterson" wrote:

> ..Sort is pretty forgiving.  Didn't it work using D63?
> 
> I've used:
> 
> ....key1:=range("d:d"), ...
> 
> And that works, too.
> 
> Frantic Excel-er wrote:
> > 
> > Hi all!!!
> > 
> > I am trying to get my macro to run a sort in a non specific cell for column
> > D.....there are many steps up to this point, I will include the last couple
> > in the code...here is what I have so far....
> > 
> >     Columns("C:C").Find(What:="x").EntireRow.Insert
> >     Columns("C:C").Find(What:="x").EntireRow.Select
> >     Range(Selection, Selection.End(xlDown)).Select
> >     Range(Selection, Selection.End(xlToRight)).Select
> >     Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
> >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> > 
> > Instead of the range being specific for  D63, I want it to select the D
> > column after if finds X to the end of the wkst.  Any ideas on how I can
> > accomplish this???
> > 
> > Thanks in advance......
> > 
> > 
> 
> -- 
> 
> Dave Peterson
> 
0
6/6/2005 8:03:28 PM
Don't select column D.

But do use it in the key1:=range("d:d") portion.

Then what happens?

Frantic Excel-er wrote:
> 
> Selecting "d:d" works, but it will select the whole column... I only want to
> sort the selection after I find x and sort that....
> 
> "Dave Peterson" wrote:
> 
> > ..Sort is pretty forgiving.  Didn't it work using D63?
> >
> > I've used:
> >
> > ....key1:=range("d:d"), ...
> >
> > And that works, too.
> >
> > Frantic Excel-er wrote:
> > >
> > > Hi all!!!
> > >
> > > I am trying to get my macro to run a sort in a non specific cell for column
> > > D.....there are many steps up to this point, I will include the last couple
> > > in the code...here is what I have so far....
> > >
> > >     Columns("C:C").Find(What:="x").EntireRow.Insert
> > >     Columns("C:C").Find(What:="x").EntireRow.Select
> > >     Range(Selection, Selection.End(xlDown)).Select
> > >     Range(Selection, Selection.End(xlToRight)).Select
> > >     Selection.Sort Key1:=Range("D63"), Order1:=xlAscending, Header:=xlGuess, _
> > >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
> > >
> > > Instead of the range being specific for  D63, I want it to select the D
> > > column after if finds X to the end of the wkst.  Any ideas on how I can
> > > accomplish this???
> > >
> > > Thanks in advance......
> > >
> > >
> >
> > --
> >
> > Dave Peterson
> >

-- 

Dave Peterson
0
ec357201 (5290)
6/6/2005 9:33:05 PM
Reply:

Similar Artilces:

All macros failing part way through with different error messages
All the macros I have in various workbooks are failing - usually part-way through - with different error messages each time. When I check the debugger and try to edit the text, there seems no reason why there should be a problem. The macro has so far stopped on instructions to : - open a workbook - select a worksheet - paste a column in a different place Several times the macro has just stopped running without completing and without showing an error message. I cannot run a host of urgent daily reports without using the macros. Please help! NO way to help unless you tell us exactly...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Twist Data?...
Hi all... I've got the following in a dataset: 19 30 1200 FI FI 20030906 36 19 30 1324 FI FI 20030906 36 and I want the following result: 19 30 1200, 1324 FI FI 20030906 26 Any guess on how to do this? thanks much!... Hmmm... Not quite clear: The data is arranged in columns, I assume It looks as if both items in the same column are the same, you want just one item, otherwise the one in the top row first, then the one from the bottom row. But what about the last 36s that should yield 26? Typo? Does the dataset have more columns or more rows or both? And how big is it?...

moving payables data from open to history
Hello: A client says that someone imported data about a year or two ago into Great Plains from their AS400. Many payables documents that were imported should have been coded during the import as open, instead of history. The client knows that she can take care of this herself within two hours, by simply turning off the posting to the GL and entering and posting the payables documents to move them to history. But, she is wondering if there is a quick and easy way to do this on the back-end. I'm familiar with the open and history payables tables within GP. And, I know through a T...

macro #2
I am pretty new to using (or attempting to use) a Macro in a workbook. I am having trouble following how and what to do to even start the process; ie If there needs to be a worksheet already in progress or can I start from a new sheet. THEN where to go from there. Can I have some help in this area? Can some one supply me with a sample MACRO? Any help would be greatly appreciated Dan The best way to get started with macros is to use the Macro Recorder. Go to the Tools menu, choose Macros, then Record New Macro. Then, carry out some simple tasks and examine the code that is produced. ...

merging 2 cells without losing data?
How can I merge 2 cells without losing data from the other cell? Hi Bob Not possible I'm afraid. Try placing the dat from both cells into one and use "Center across selection" under Format>Cells>Alignment Merge cells always end up causing grief. they are best avoided. ***** Posted via: http://www.ozgrid.com Excel Templates, Training & Add-ins. Free Excel Forum http://www.ozgrid.com/forum ***** "bob" <bobree@hotmail.com> wrote in message news:%23JuOM9HGEHA.2308@tk2msftngp13.phx.gbl... > How can I merge 2 cells without losing data from the other...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Transformation of data into columns
Hi, I have the data from a flattened spreadsheet in a table in the following form: f1 f2 f3 period to: Scheme1 Scheme2 31/01/2005 Net Gross 28/02/2005 Net Gross 31/03/2005 Net Gross 30/04/2005 Net Gross 31/05/2005 Net Gross 30/06/2005 Net Gross 31/0...

How can I sort duplicate text data in excel?
I have a large list of noames that I need to make sure that none of them are duplicated. Is there a way to have excel check it quisker than me reading every name until I find a duplicate? After selecting your data go to filter Advanced filter and check "Unique records only" You can even copy it to another area all uniques entries if you want to ... "TinaScheu" <TinaScheu@discussions.microsoft.com> wrote in message news:0399D580-7E69-4DF0-A969-E7FC5F777C70@microsoft.com... >I have a large list of noames that I need to make sure that none of them >are >...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

Data Validation List not showing
I'm using Excel 2003. My data validation lists have stopped working on one sheet in my workbook. It is working on all other sheets. I have googled the problem and found the following advice: 1. Make sure freeze panes is off.... check. 2. Select "Show All" under Tools->Options->View->Objects ... check. The problem remains. Any ideas? "Stopped working" doesn't do much to describe your problem. When you select one of the "stopped working" Data Validation cells, do you see the drop-down arrow to the right of the cell? When you select t...

Macros #34
I am trying to edit and create some macros in an excel spreadsheet that was created by someone else. There are macros for this spreadsheet but for some reason when I go to Tools -> Macro -> Macros it doesn't show any in the list. When I go to Tools -> Macro -> Visual Basic Editor there is code for the macros. I have some buttons on the spreadsheet that I want to assoicate with the macros but the buttons almost seem like they are locked, when i write click them nothing happens but a shortcut menu should appear. Is there maybe some special security on there I don't...

Select all of a certain column across multiple worksheets
Is there an easy way to simultaneously select (or do a find-and-replace) on column H on every worksheet of an entire multi-sheet workbook, without having to select that column on each individual worksheet by hand? Charles Belov SFMTA Webmaster http://www.sfmta.com/webmaster Right-click on first sheet tab and "Select all sheets" In activesheet select the column............will be selected on all sheets. Gord Dibben MS Excel MVP On Mon, 24 Aug 2009 16:43:02 -0700, "Charles Belov" <invalid@invalid.invalid> wrote: >Is there an easy way to simultaneously se...

Sorting Attendee Lists in Outlook 2003
Is there any way to sort attendees by either name or attendee status in Outlook 2003? I don't know how Microsoft could be so oblivious to the need for this feature. You can't even click on the column headers to sort, let alone print out a list of people sorted by accept and decline. Microsoft's online help system recommends Alt + PrntScreen???? How archaic is that? Is there anyone out there that can help me??? Thanks! ...

Macro
Here is one for you experts - I am a novice I want to add a macro to a worksheet that will print selected information held on certain rows of the worksheet. Once the 'button' has been selected a mesage box will pop up asking the user to input which row number he/she wishes to print. On input that number is matched to the corresponding row of the worksheet and that row is copied (without any formula - just cell values) and pasted into another worksheet where it is placed into other formats and printed. Seems like a simple(!!) job - but far to difficult for me to programme Can an...

How to sort calendar entries on "size"
I would like to remove large size calendar entries by being able to sort on "size". I am able to sort Inbox items on size but not calendar. My company's exchange server has email account size quota and a 3rd of that quota is being held up by calendar entries on the server side, so I would like to get rid of the unwanted past celndar entries that occupy portion of my account quota. Any help is appreciated. Thanks. Umesh On 11/18/03 9:21 PM, in article 049b01c3ae43$d9cf01d0$a301280a@phx.gbl, "Umesh" <anonymous@discussions.microsoft.com> wrote: > I would...

macro for invoice totals
I have this macro that inserts lines and copies a invoice header. Now I want to total the invoice. In the first row after a value in B in E the word totals must be inserted, H, I and K must be summed the amount of lines differ on each invoice but there is a heading from where it must be summed. CTNS(H), QTY(I), Total(K) Thanks a lot. Public Sub Deilv2() Dim LastRow As Long Dim row_index As Long Dim rng As Range Set rng = Range("B2:K25") Application.ScreenUpdating = False LastRow = ActiveSheet.Cells(Rows.Count, "b").End(xlUp).Row For row_index = LastRow - 1 To 26 S...

Adding extra data options
Is there a way to customize CRM to allow for adding another heading? I would like to add a second field similar to topic and would like to call it type. Can you add extra data fileds and types in CRM 3.0? You can add extra data fields to an entity. Go to entities customization at setting area. -- Marco Amoedo Plain Concepts http://geeks.ms/blogs/marco/ "xxdcmast" escribió: > Is there a way to customize CRM to allow for adding another heading? I would > like to add a second field similar to topic and would like to call it type. > > Can you add extra data ...

Is there a way to sort a column without invoking a macro?
I am trying to get a feel for some probabilistic properties. For example, I am trying to look at the statistics of the intervals between randomly occurring pulses. I have no problem getting a column of uniformly distributed pulse times, but they are unsorted, Every time I recalculate with cmd-=, I get a new unsorted set of pulse times because of the use of the RAND function. Is there a way of taking this unsorted column and pasting it into another colun that would be sorted without invoking a macro? To simplify and be more specific consider the following. 1. In A1:A100 I have a set...

cell selection gone crazy on Excel 2003
All of a sudden the mouse is acting like it is held down, and will not stop selecting cells. Have tried double clicking, playing with the Function keys, all sorts of things, but to no avail... don't want to force quit. Any clues? TIA, Geri Hi Geri, See David McRitchie's notes at: http://www.mvps.org/dmcritchie/excel/ghosting.txt --- Regards, Norman "Tweedie-Vaughan" <Tweedie-Vaughan@discussions.microsoft.com> wrote in message news:438C3854-C74C-410A-BD88-DAA146172E99@microsoft.com... > All of a sudden the mouse is acting like it is held down, a...

Select All Names
We have a form with a combo box and that combo box executes a query to find all of the records matching the user's name. The query derives that value from a form based parameter called vName. This is how the process works. 1. The user starts by selecting their name via a drop down list combo box on a dialog form. 2. That selection becomes the value of vName. 3. The user clicks OK and then moves to the data entry form. 4. On the data entry form, the user then clicks on another combo box which runs the aforementined query, which in turn uses the value of the variable vName to find ...

Macro Help #18
Well I think this is a macro. I have searched my excel book and i can not seem to figure out what to do. I want to write something that if this statement is true, then the font color changes. But here is the potential problem. THe location of the text will change. So i need it to search for this text and then change the color. Does that make sense? Is this possible? If this can be done it will really help me. Thanks in advance. This is exactly what i am trying to do. You type in a persons name into cell a1. you also type a name into cell a2. These names will transfer over ...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...