Formula using last cells in two different columns

[NICO1]

Hi,

I am making a Macro which will have different last rows every time I will be using it.
I have been able to write the macro so that column D and E sum in the last row +1. Now I need to write a simple formula in column F (LastRow+1), using the values in the last rows of column D and E.
If the formula was hard printed it would be =E21/D21 (where row 21 contains the last value in the spreadsheet). I do however need the formula to be dynamic and use the last values in column E and D. Any suggestions?

Range("d1").Select
Selection.EntireColumn.Insert
Range("d3").Value = "Forvaltningshonorar i kr"
LastRow = Range("a4").End(xlDown).Row
Range("d4").Select
ActiveCell.Formula = "=e4/f4"
Range("d4").Select
Range(Selection, Selection.Offset(LastRow - Range("a4").Row, 0)).Select
Selection.FillDown
Range("d4").End(xlDown).Select
LastRow = ActiveCell.Row
Cells(LastRow + 1, "d").Formula = "=SUM(d4:d" & LastRow & ")"
Range("e4").End(xlDown).Select
LastRow = ActiveCell.Row
Cells(LastRow + 1, "e").Formula = "=SUM(e4:e" & LastRow & ")"
Range("f4").End(xlDown).Select
LastRow = ActiveCell.Row

Help is highly appreciated!

 

[NICO1]

I forgot to put up the last line of my macro, where I try to write the needed formula:

Range("f4").End(xlDown).Select
LastRow = ActiveCell.Row
Cells(LastRow + 1, "f").Formula = "=Lastincolumn(e4)/Lastincolumn(d4)"

Excel returns the value #NAME?

 

[ask2tsp]

Hi NICO1, look at this code. Note that I also simplified the code by leaving out all the select/selection/activecell steps. You don't have to select a cell to change it.
Please try this on a copy of your workbook. I was not able to test the code because I do not have a sheet with testdata.

Sub dynamicRangeFormula()
    Dim lastRow As Long
    
    Range("d1").EntireColumn.Insert
    Range("d3").Value = "Forvaltningshonorar i kr"
    lastRow = Range("a4").End(xlDown).Row
    Range("d4").Formula = "=e4/f4"
    Range("d4", Range("d4").Offset(lastRow - 4, 0)).FillDown
    
    Cells(lastRow + 1, "d").Formula = "=SUM(d4:d" & lastRow & ")"
    Cells(lastRow + 1, "e").Formula = "=SUM(e4:e" & lastRow & ")"
    Cells(lastRow + 1, "f").Formula = "=e" & lastRow + 1 & "/d" & lastRow + 1
End Sub

 

[NICO1]

Hi ask2tsp,

Thank you! This worked perfectly! You have made my day
Do you mind explaining parts of the formula for me?
Final line in paragraph 1? What does the -4 mean? Why have you used -4?
Also, last line in paragraph 2, why have you used "=e & lastRow + 1 &? It does not seem logical to me, as I am asking for the last value i column e, not last + 1. What does the & stand for?

Sub dynamicRangeFormula()
Dim lastRow As Long

Range("d1").EntireColumn.Insert
Range("d3").Value = "Forvaltningshonorar i kr"
lastRow = Range("a4").End(xlDown).Row
Range("d4").Formula = "=e4/f4"
Range("d4", Range("d4").Offset(lastRow - 4, 0)).FillDown

Cells(lastRow + 1, "d").Formula = "=SUM(d4:d" & lastRow & ")"
Cells(lastRow + 1, "e").Formula = "=SUM(e4:e" & lastRow & ")"
Cells(lastRow + 1, "f").Formula = "=e" & lastRow + 1 & "/d" & lastRow + 1
End Sub

 

[NICO1]

In addition; I have several sheets where I perform the same macro (the one above). Is there a way to execute it in all sheets at once or do I need to do it in one sheet at a time?

 

[ask2tsp]

The 4 is your Range("a4").Row
The lastRow was calculated as the last row having data. The SUM formulas go below the last row hence lastrow + 1.
The & operator means concatenation, so "Hello " & "World" equals "Hello World".
Iif lastRow=22, the last expression would become "=e23/d23".

For use on multiple sheets use the following code

Option Explicit

Sub dynamicRangeFormula(ws As Worksheet)
    Dim lastRow As Long
    
    ws.Range("d1").EntireColumn.Insert
    ws.Range("d3").Value = "Forvaltningshonorar i kr"
    lastRow = ws.Range("a4").End(xlDown).Row
    ws.Range("d4").Formula = "=e4/f4"
    ws.Range("d4", ws.Range("d4").Offset(lastRow - 4, 0)).FillDown
    
    ws.Cells(lastRow + 1, "d").Formula = "=SUM(d4:d" & lastRow & ")"
    ws.Cells(lastRow + 1, "e").Formula = "=SUM(e4:e" & lastRow & ")"
    ws.Cells(lastRow + 1, "f").Formula = "=e" & lastRow + 1 & "/d" & lastRow + 1
End Sub

Sub multipleSheets()
    Dim sht         As Worksheet
    Dim shtNames    As Variant
    Dim i           As Integer
    
    shtNames = Array("Blad1", "Blad2", "Blad6", "Blad8") 'Here fill in your sheet names
    
    For i = LBound(shtNames) To UBound(shtNames)
        Set sht = ThisWorkbook.Worksheets(shtNames(i))
        Call dynamicRangeFormula(sht)
    Next i
End Sub

fill in your own list of sheet names and run the multipleSheets macro.

 

[NICO1]

You are a genius! thank you so much. You saved me from wasting A LOT of time