Autofill dynamic COLUMN

[ffialho]

Hi all,

Could really use a help with this.

I have use a shared spreadsheet that I have to do some simple calculatios, searches, sumifs, etc.
The example code is working fine to find the last row and column... and start the calculation but just cant
get the autofill to work.

What Im trying is to have this working without having to bother to manually have to find and declare the last
used column today can be Z, tommorow BC or AD...

thanks

Sub test()
 Dim lastrow As Long, lastcolumn As Long

Set sht = Worksheets("Base214")
Set StartCell = Range("A1")

lastrow = Range("A" & Rows.Count).End(xlUp).Row
lastcolumn = Cells(1, Columns.Count).End(xlToLeft).Column

     Range("A1").Select
     Selection.End(xlToRight).Select
     ActiveCell.Offset(0, 1).Select
         ActiveCell.FormulaR1C1 = "Class"
         ActiveCell.Offset(1, 0).Select
     ActiveCell.FormulaR1C1 = _
    "=IF(COUNTIF(Supoort!R2C1:R22C1,Base214!RC12)>=1,""NONSTANDARD"",""STANDARD"")"
    Range("lastcolumn").AutoFill Destination:=Range(lastcolumn & lastrow)

 

[MARK858]

lastcolumn in your code is a Column number not a Range. Try (untested)

Cells(2, lastcolumn + 1).AutoFill Destination:=Range(Cells(2, lastcolumn + 1),Cells(lastrow, lastcolumn +1))

 

[MARK858]

Please note that I have just edited the code that I posted as you define lastcolumn before you add the formula in the next column

 

[MARK858]

You'll probably find the below will do the same as your code, I have also qualified all the ranges with the sheet name as that would also have caused you issues (also in formula you use the sheet name Supoort, is this correct and not Support?)

Sub test()
    Dim lastrow As Long, lastcolumn As Long

    Set sht = Worksheets("Base214")

    lastrow = sht.Range("A" & Rows.Count).End(xlUp).Row
    lastcolumn = sht.Cells(1, Columns.Count).End(xlToLeft).Column + 1

    With sht.Cells(1, lastcolumn)
        .Value = "Class"
        Range(.Offset(1, 0), sht.Cells(lastrow, lastcolumn)).FormulaR1C1 = _
            "=IF(COUNTIF(Supoort!R2C1:R22C1,Base214!RC12)>=1,""NONSTANDARD"",""STANDARD"")"
    End With
End Sub

 

[ffialho]

Thanks Mark858! The 2nd answer was even better!

I tweaked it a little as I have 9 calculations! So I used your code for the first and for the 2nd, 3rd and fwd I added a +1 to the column in the With and Offset lines!

    With sht.Cells(1, lastcolumn + 1)
        .Value = "Payment_Days"
        Range(.Offset(1, 0), sht.Cells(lastrow, lastcolumn + 1)).FormulaR1C1 = _
            "=IF(RC[-3]-RC[-34]<=0,0,RC[-3]-RC[-34])"
    End With

By doing this I can have the code calculate fill the next column as all the calculations I have will have the same columns orders. So basically the code at the end will have a few more Withs but will look like the one below:

Sub test()
    Dim lastrow As Long, lastcolumn As Long

    Set sht = Worksheets("Base214")

    lastrow = sht.Range("A" & Rows.Count).End(xlUp).Row
    lastcolumn = sht.Cells(1, Columns.Count).End(xlToLeft).Column + 1

    With sht.Cells(1, lastcolumn)
        .Value = "Class"
        Range(.Offset(1, 0), sht.Cells(lastrow, lastcolumn)).FormulaR1C1 = _
            "=IF(COUNTIF(Supoort!R2C1:R22C1,Base214!RC12)>=1,""NONSTANDARD"",""STANDARD"")"
    End With

    With sht.Cells(1, lastcolumn + 1)
        .Value = "Payment_Days"
        Range(.Offset(1, 0), sht.Cells(lastrow, lastcolumn + 1)).FormulaR1C1 = _
            "=IF(RC[-3]-RC[-34]<=0,0,RC[-3]-RC[-34])"
    End With

End Sub

Working as a charm!!

 

[MARK858]

You're welcome, happy it helped

Btw, not that it makes much difference but rather than add the +1 in the With statement you could change the offset i.e.

    With sht.Cells(1, lastcolumn + 1)
        .Value = "Payment_Days"
        Range(.Offset(1, 0), sht.Cells(lastrow, lastcolumn + 1)).FormulaR1C1 = _
            "=IF(RC[-3]-RC[-34]<=0,0,RC[-3]-RC[-34])"
    End With

to

    With sht.Cells(1, lastcolumn)
        .Value = "Payment_Days"
        Range(.Offset(1, 1), sht.Cells(lastrow, lastcolumn + 1)).FormulaR1C1 = _
            "=IF(RC[-3]-RC[-34]<=0,0,RC[-3]-RC[-34])"
    End With