Formula in first blank and filled down to end of data-CodePudding

I have the below code where in all other columns there is many populated rows, what I need this formula to do in column F is to find the first blank, then place the formula in it and fill it down to the last row.

What is currently happening is I have the range as F26 as this is usually first blank but this could change and I want the code to identify this and also have the formula dynamically know what row it is on, so for example if one month the first blank was in cell F30 the range would find it and the formula would start as E30*G30.

Any help would be greatly appreciated.

Private Sub calc()
    Dim lastrow As Long
    Dim rng As Range
    lastrow = ThisWorkbook.Worksheets("Indiv").Cells(Rows.Count, 1).End(xlUp).Row
    Set rng = Range("F26:F" & lastrow)
    rng.Formula = "=Round((E26*G26),2)"
End Sub

CodePudding user response：

You need to find the first free row in column F and then bulid your formula with this row:

Option Explicit

Private Sub calc()
    Dim ws As Worksheet  ' define worksheet
    Set ws = ThisWorkbook.Worksheets("Indiv")
    
    Dim LastRowA As Long  ' find last used row in column A
    LastRowA = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    Dim FirstFreeRowF As Long  ' find first free row in column F (if first 2 rows have data)
    FirstFreeRowF = ws.Cells(1, "F").End(xlDown).Row   1
    
    ' fix issue if first or second row is empty
    If FirstFreeRowF = ws.Rows.Count   1 Then
        If ws.Cells(1, "F").Value = vbNullString Then
            FirstFreeRowF = 1
        ElseIf ws.Cells(2, "F").Value = vbNullString Then
            FirstFreeRowF = 2
        End If
    End If
    
    ' define range to add formula
    Dim Rng As Range
    Set Rng = ws.Range("F" & FirstFreeRowF, "F" & LastRowA)
    
    ' add formula
    Rng.Formula = "=Round((E" & FirstFreeRowF & "*G" & FirstFreeRowF & "),2)"
End Sub

So this will consider F5 the first free row and fill in the formula in the selected range as seen below:

CodePudding user response：

I think you should find the last used row in F column so that you could know the next row is blank

lastrowF=sheets(sheetname).range("F" & rows.count).end(xlup).row

⇒So the next row is like range("F" & lastrowF 1).formula="Round((E" & lastrowF 1 & "*G" & lastrowF 1 & ",2)"

Hope this help ^^