Page 1 of 2 12 LastLast
Results 1 to 10 of 17

End-Down type function?

This is a discussion on End-Down type function? within the Excel Questions forums, part of the Question Forums category; I want to create a macro for a group of workbooks that will copy data in a cell and then ...

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default End-Down type function?

    I want to create a macro for a group of workbooks that will copy data in a cell and then paste that data down in an end down type style. I know how to write a macro to do this. The problem I will run into is when a workbook has only 1 row of data that needs to be changed. If I use the end down function when I have only 1 row of data it will copy a useless formula all the way down to the total at the bottom of the column. Is there a way around using an end-down function to do this?

    Below is the code for a sheet with only 1 row: (an end-down function won't work here).

    [img] ******** ******************** ************************************************************************>
    Microsoft Excel - test2.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    7
    Days in the Month 31
    8
    Hours in Month 177.14
    9
    10
    11
    Job Job Code Prod N/P
    12
    Code Description Hours Hours
    13
    14
    1222 Transporter - Nursing 720.37 121.67
    15
    Overtime Premium
    16
    Double-Time Premium
    17
    PIP
    18
    Shift Differential
    19
    20
    720.37 121.67
    jan

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    [/img]

  2. #2
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Re: End-Down type function?

    Here is a version where an end down function will work:

    [img]******** ******************** ************************************************************************>
    Microsoft Excel - test.xls___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    1
    SJH
    2
    SALARY VARIANCE ANALYSIS
    3
    B 6011 - NURSING ADMINISTRATION
    4
    JANUARY 31, 2004
    5
    6
    7
    Days in the Month 31
    8
    Hours in Month 177.14
    9
    10
    11
    Job Job Code Prod N/P
    12
    Code Description Hours Hours
    13
    14
    1007 Nurse Supervisor 344.07 34.93
    15
    1008 Baylor Nurse Supervisor 175.26 0.00
    16
    1110 RN 49.60 0.00
    17
    1210 Unit Clerk (12.50) 43.43
    18
    Overtime Premium
    19
    Double-Time Premium
    20
    PIP
    21
    Shift Differential
    22
    23
    556.43 78.36
    jan

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
    [/img]

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Re: End-Down type function?

    The cells I'm speaking of are in E14 and G14.

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,962

    Default Re: End-Down type function?

    You should be able to incorporate this snippet of code into your macro. If there is only one row and it goes all the way to the bottom, if will come back up into the original cell
    Code:
    '   Capture current cell
        Dim myCurrentCell As Range
        Set myCurrentCell = ActiveCell
        
    '   Go to last row
        ActiveCell.End(xlDown).Select
        
    '   Check to see if in last row in Excel and go back to first cell, if necessary
        If ActiveCell.Row = 65536 Then myCurrentCell.Select
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Re: End-Down type function?

    Quote Originally Posted by jmiskey
    You should be able to incorporate this snippet of code into your macro. If there is only one row and it goes all the way to the bottom, if will come back up into the original cell
    Code:
    '   Capture current cell
        Dim myCurrentCell As Range
        Set myCurrentCell = ActiveCell
        
    '   Go to last row
        ActiveCell.End(xlDown).Select
        
    '   Check to see if in last row in Excel and go back to first cell, if necessary
        If ActiveCell.Row = 65536 Then myCurrentCell.Select

    I don't think that will work because I have a column total at the bottom of each column. The end down will never go to row 65536. Also, the number of Job Code rows will vary on every template. Some have 1 row, others could have 10.

  6. #6
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,962

    Default Re: End-Down type function?

    Very important details! Changes the complexion of the problem.

    This following line will replace the previous. What this does is simply check the column in the next row for data. If there is, then it will do the down function. If there isn't, it assumes there is only one row of data, so it won't go anywhere.

    Code:
    '   Only do down function if the next column has data in it
        If Len(ActiveCell.Offset(1, 0)) > 0 Then ActiveCell.End(xlDown).Select
    If my assumptions are incorrect, you will need to lay out the rules for determining what constitutes a new section.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Re: End-Down type function?

    Quote Originally Posted by jmiskey
    Very important details! Changes the complexion of the problem.

    This following line will replace the previous. What this does is simply check the column in the next row for data. If there is, then it will do the down function. If there isn't, it assumes there is only one row of data, so it won't go anywhere.

    Code:
    '   Only do down function if the next column has data in it
        If Len(ActiveCell.Offset(1, 0)) > 0 Then ActiveCell.End(xlDown).Select
    If my assumptions are incorrect, you will need to lay out the rules for determining what constitutes a new section.
    I think we are on the right track now. However, there is a chance the cell could be 0 or less than 0. is there something I can put there to say "is not null" instead of the > 0?

  8. #8
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    25,962

    Default Re: End-Down type function?

    Code:
    I think we are on the right track now. However, there is a chance the cell could be 0 or less than 0. is there something I can put there to say "is not null" instead of the > 0?
    Look closely. I am NOT checking the value in the cell, I am checking the length. If the cell is blank, empty, etc. it will have a zero length. If it has the value of 0 in it, its length is 1.

    Therefore, it should work as written.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Re: End-Down type function?

    Quote Originally Posted by jmiskey
    Code:
    I think we are on the right track now. However, there is a chance the cell could be 0 or less than 0. is there something I can put there to say "is not null" instead of the > 0?
    Look closely. I am NOT checking the value in the cell, I am checking the length. If the cell is blank, empty, etc. it will have a zero length. If it has the value of 0 in it, its length is 1.

    Therefore, it should work as written.

    my mistake. I'm going to put together a macro now and give it a shot. thanks for your help!

  10. #10
    Board Regular
    Join Date
    Nov 2003
    Location
    South Jersey
    Posts
    112

    Default Re: End-Down type function?

    Below is my current code. Everything works great when the worksheet has more than one row. However, I'm getting an error message '1004' when there is only 1 row. I guess I need to tell excel what to do if the cell below is null. Any clue what code i need to add?

    Code:
        Range("E14").Select
        ActiveCell.FormulaR1C1 = _
            "=+jan!RC+feb!RC+mar!RC+apr!RC+may!RC+june!RC+july!RC+aug!RC+sept!RC+oct!RC+nov!RC+dec!RC"
        Range("E14").Select
        If Len(ActiveCell.Offset(1, 0)) > 0 Then ActiveCell.Copy
      
         
        Range(Selection, Selection.End(xlDown)).Select
        ActiveSheet.Paste
      
      
        Application.CutCopyMode = False
    End Sub

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com