Autofill Issues in a macro- Excel 2010/Win 7

[aziska]

Hello, I'm new to the board and it would seem Excel by looking at the advanced nature of most of the post regarding my issue. So, please bear with me while I play catch up.

I'm having issues with a macro that i've recorded in my personal workbook that I intend on using on variable lenths of datasheets (~rows of data) with the same number of columns. In the macro, I type an If statement, copy/drag it to fill 9 columns to the right (copy of AF to fill AG:AO) then I double click the auto fill tool on the lower right hand of the cell to copy to the end of the sheet. When i run this on a sheet with a differing number of rows of data, it returns unwanted results for what i do next with the spreadsheet.

I am uncertain of how to post the code like I've seen in other posts, and I understand this topic has been breached in another thread, however it is locked. If you could please help me out with this I'd greatly appreciate it. Don't feel as if you'll be condecending if you dumb this down to a 4 yr old's level. I'm quite fresh at this stuff.

Notes from other posts in the previous thread that may help:
1) All columns have identicle numbers of rows
2) the if statement is: =IF(AE2-"0","0",(IF(AE2="1","0",(IF(AE2="2","2",(IF(AE2="3","2","Error")))))))

Regards - aziska

 

[Andrew Poulsom]

Welcome to MrExcel.

Post your code by copying it in the Visual Basic Editor and pasting it into a reply. Please surround your code with Code tags:

http://www.mrexcel.com/forum/misc.php?do=bbcode#code

 

[aziska]

Thank you Mr. Poulsom,

Here is the code (I believe).

 Range("AF2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]=""0"",""0"",(IF(RC[-1]=""1"",""0"",(IF(RC[-1]=""2"",""2"",(IF(RC[-1]=""3"",""2"",""Error"")))))))"
    Range("AF2").Select
    Selection.AutoFill Destination:=Range("AF2:AO2"), Type:=xlFillDefault
    Range("AF2:AO2").Select
    Selection.AutoFill Destination:=Range("AF2:AO18402")
    Range("AF2:AO18402").Select

-Aaron

 

[Andrew Poulsom]

Can column AE be used to determine that last non-blank row?

    Dim LastRow As Long
    With Range("AF2")
        .FormulaR1C1 = _
            "=IF(RC[-1]=""0"",""0"",(IF(RC[-1]=""1"",""0"",(IF(RC[-1]=""2"",""2"",(IF(RC[-1]=""3"",""2"",""Error"")))))))"
        .AutoFill Destination:=Range("AF2:AO2"), Type:=xlFillDefault
    End With
    LastRow = Range("AE" & Rows.Count).End(xlUp).Row
    Range("AF2:AO2").AutoFill Destination:=Range("AF2:AO" & LastRow)

If not use one that can.

 

[aziska]

My thanks again Mr. Poulsom!

Worked like a charm.

Now, the old addage, "give a man a fish..." Would you be able to explain exactly what your code is doing vs what mine did and how it works differently? Or, perhaps point me in a direction of where to view the different functions to read up on them to better understand it, please? Thank you for taking the time to help me out, it is very much appreciated!

-Aaron

 

[Andrew Poulsom]

Your code had a fixed last row for AutoFill - 18402. My code finds the last row in column AE by going End Up from the bottom. That row is assigned to a variable which is used as the destination row, by concatenation.