Insert formula in last column macro

[doriannjeshi]

Hi,
I need to insert a formula in the last dynamic column in row 2, of a range that starts at A1.
This is what I have static but I need it dynamic last column:

ActiveCell.FormulaR1C1 = "shuma"
Range("AY2").Select
Application.CutCopyMode = False
Selection.FormulaR1C1 = "=SUM(RC[-35]:RC[-1])"
Selection.AutoFill Destination:=Range("AY2:AY" & Range("a" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Micron]

All of this is untested:

Dim Lcol As Long

With ActiveSheet
    Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Cells(2,Lcol).Formula = "=SUM(RC[-35]:RC[-1])"
End With

That should resolve your request but I can't tell if you need the autofill part to refer to the last column with data also. If so, you can swap AY2 with Lcol as I did. However, it might be more elegant to create a range variable and assign it the concatenated range.

With ActiveSheet
    rng = .Cells(2,Lcol) then you'd write
    rng.Formula = "=SUM(RC[-35]:RC[-1])"
End With

and
Range(rng & Range("a" & Rows.Count).End(xlUp).Row)

except I confess I'd have to look up how to autofill without making a selection, if that's possible. Usually I try to avoid selecting anything because it's almost never required. You can see that I didn't select the cell in order to give it a formula. Sometimes it's better to use a specific sheet reference rather than ActiveSheet.
HTH

 

[doriannjeshi]

All of this is untested:

Dim Lcol As Long

With ActiveSheet
    Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Cells(2,Lcol).Formula = "=SUM(RC[-35]:RC[-1])"
End With

That should resolve your request but I can't tell if you need the autofill part to refer to the last column with data also. If so, you can swap AY2 with Lcol as I did. However, it might be more elegant to create a range variable and assign it the concatenated range.

With ActiveSheet
    rng = .Cells(2,Lcol) then you'd write
    rng.Formula = "=SUM(RC[-35]:RC[-1])"
End With

and
Range(rng & Range("a" & Rows.Count).End(xlUp).Row)

except I confess I'd have to look up how to autofill without making a selection, if that's possible. Usually I try to avoid selecting anything because it's almost never required. You can see that I didn't select the cell in order to give it a formula. Sometimes it's better to use a specific sheet reference rather than ActiveSheet.
HTH

I run this nothing happens. Am I supposed to add code to it ?

With ActiveSheet
Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Cells(2,Lcol).Formula = "=SUM(RC[-35]:RC[-1])"
End With

 

[Micron]

You're supposed to put that in your own existing code. It is meant to be a replacement for anything that refers to the static range you mentioned; e.g. Range("AY2").Select
or anything that refers to it afterwards buty by way of selection; e.g. Selection.FormulaR1C1 = "=SUM(RC[-35]:RC[-1])"
I see that my prior posted code was not properly contained within code tags as there's a comment there.
If you need further help, maybe post the entire sub and ID the range reference that is static that you want to avoid using. Please post code within code tags (use vba button on posting toolbar and paste code within those tags) to maintain indentation and readability.

 

[doriannjeshi]

The range is A1:AY2 but I need the last column to be dynamic and the formula needs to go in the row 2 of the last column and copied to the end of the range.

ActiveCell.FormulaR1C1 = "shuma"
    Range("AY2").Select
    Application.CutCopyMode = False
    Selection.FormulaR1C1 = "=SUM(RC[-35]:RC[-1])"
    Selection.AutoFill Destination:=Range("AY2:AY" & Range("a" & Rows.Count).End(xlUp).Row)
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 

[MARK858]

Try the code below if you have no hidden rows or columns

Sub doriann()
    Dim lCol As Long, lRow As Long

    With ActiveSheet

        lCol = .Rows(1).Find("*", , xlValues, , xlByColumns, xlPrevious).Column
        lRow = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

        With Range(.Cells(2, lCol), .Cells(lRow, lCol))
            .FormulaR1C1 = "=SUM(RC[-35]:RC[-1])"
            .Value = .Value
        End With

    End With

    
End Sub

Edited as left off the End Sub

 

[Micron]

The range is A1:AY2 but I need the last column to be dynamic

A1:AY2 is specific/static, not dynamic. Dynamic would be "A1 to whatever the last column with data is".
MARK858 can take over as he/she seems to have interpreted exactly what you're after.

 

[doriannjeshi]

Try the code below if you have no hidden rows or columns

Sub doriann()
    Dim lCol As Long, lRow As Long

    With ActiveSheet

        lCol = .Rows(1).Find("*", , xlValues, , xlByColumns, xlPrevious).Column
        lRow = .Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row

        With Range(.Cells(2, lCol), .Cells(lRow, lCol))
            .FormulaR1C1 = "=SUM(RC[-35]:RC[-1])"
            .Value = .Value
        End With

    End With

   
End Sub

Edited as left off the End Sub

I run this nothing happens

 

[doriannjeshi]

A1:AY2 is specific/static, not dynamic. Dynamic would be "A1 to whatever the last column with data is".
MARK858 can take over as he/she seems to have interpreted exactly what you're after.

it turns out I didn't check the cell but it was puting the formula where it needed. The only thing missing is to copy down the formula

With ActiveSheet
    Lcol = .Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    Cells(2,Lcol).Formula = "=SUM(RC[-35]:RC[-1])"
End With

 

[MARK858]

it turns out I didn't check the cell but it was puting the formula where it needed. The only thing missing is to copy down the formula

The code puts the formula directly in the cells to the last row with data, there is no need to copy anything down.
Comment out the .Value = .Value line to see where the formula goes down to