Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Variable Ranges in Excel

    Here's the movie: An Excel spreadsheet. A column with FirstName and MI. Task: Separate first name and MI into separate columns. The column order in the file is not always the same.
    I have written a function that will take out the firstname, and one that will take out the MI. I also have a function with FillDown that will copy the formula down a column adjacent to the source column (Code attached in Word file). I need to stop the FillDown at the end of the source column( when Range(variable)=""). Also, rather than extracting the first name, maybe it would be better to extract the MI and delete it from the source column? Should I use Offset?
    This is all part of my major confusion with Excel VBA: how do you refer to locations in the spreadsheet when the values are not absolute.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    Hi,

    firstly, functions are designed to return values, and not to carry out operations like Copy and Paste, which is in effect what a filldown is. To do what you need requires a subroutine, also called a macro or procedure. The following procedure will apply a filldown on column D for whatever number of rows are in A. It does of course require that some formula or value be in D1. The "A1" can be changed to suit any starting cell you require <pre>Sub FillDown()
    Range("A1", Selection.End(xlDown)).Offset(0, 3).Select
    Selection.FillDown
    Range("A1").Select
    End Sub</pre>

    The macro must be run and cannot be entered in a cell like a function. Unless you need to do this procedure as part of some automation, a simpler solution by be to select D1 and just give a quick double click on the fill handle of the active cell indicator (the small square at the bottom right corner).

    Hope that is some help to you.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    The easy way would be to select the column containing the first name and middle initial. Then in the Data menu click on "Text to columns." In the wizard, select "Delimited", press Next, Select Space or whatever sepatates the first name and middle initial, then click on finish.
    Legare Coleman

  4. #4
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    Hi Andrew. Thanks for your response.
    I copied your code, put some sample names in column A1 and my function in cell D1. Then I ran the sub. It selected columns D,E,F,G, but did not copy the function.

  5. #5
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    Thanks for your response, Legare. This would work very well if there were no double first names. I wrote the code to take care of that possibility.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    Hi,
    Sorry, I should have made it clearer. The macro works as follows :-

    You have some data in column A. Enter your Formula in in DX where X is the first row number of the data in A. Select the first cell in A that has data. Run the macro. It should then figure out how many times to copy the formula in D1 down, depending on the number of items in A. If there is only one item in A, you might have problems as it will copy the formula all the way down in D. I have changed it a bit as it was hard coded for A1. I am attaching a sheet as a sample for you to try. It should work for any column and apply the filldown to the 3rd column to the right.

    Andrew

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    I seem to have had a problem attaching the file so here it is now. If you have problems or questions let me know.

    By the way, if you are just klooking for a quick way to do a filldown, select the cell with the formula. The selection indicator has a handle in the bottom right corner and if you double click on that the formula will fill down the number of rows used in an adjacent column.
    Attached Files Attached Files

  8. #8
    Lounger
    Join Date
    Apr 2001
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    Hi Andrew. This works! Thanks very much. Now, since I am never satisfied, how can I make this easy for a person who can't open the VBA editor and run the sub? <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Variable Ranges in Excel

    You can place a custom button on the toolbar which when clicked will run the macro. To do this go to Tools, Customize and on the dialog box that results select the Commands tab. Scroll down the categories until you see Macros which you should select. On the right hand pane you should see a a yellow smilie button. Drag that to a suitable place on one of your toolbars. When you have it in place right click on it and select the Assign macro option. You should then be able to select the macro you want. You can also elect to change the button image if you want.

    The workbook that contains the macro you want should be open before you go through the above procedure. be aware that that macro is fairly specific and should only be used for the scenario you first posted on.

    Hope you can follow the above

    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •