End-Down type function?

[smashclash]

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).

 

[smashclash]

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

 

[smashclash]

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

 

[Joe4]

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

'   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

 

[smashclash]

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

'   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.

 

[Joe4]

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.

'   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.

 

[smashclash]

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.

'   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?

 

[Joe4]

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.

 

[smashclash]

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!

 

[smashclash]

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?

    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