VBA formula in first blank and filled down to end of data

[Orfevre]

Hi, 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

 

[kevin9999]

What row does your data start in? In other words, is row 1 a header row, with your data starting in row 2?

 

[Orfevre]

What row does your data start in? In other words, is row 1 a header row, with your data starting in row 2?

the data starts in row 3

 

[kevin9999]

Try this on a copy of your data:
EDITED

Option Explicit
Sub Indiv_Formulas()
    Dim ws As Worksheet
    Set ws = Worksheets("Indiv")
    
    Dim LRow As Long
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    
    Dim c As Range, i As Long
    For Each c In ws.Range(ws.Cells(3, 6), ws.Cells(LRow, 6))
        If c = "" Then
            i = c.Row
            Exit For
        End If
    Next c
    
    If i > 0 Then
        With ws.Range(ws.Cells(i, 6), ws.Cells(LRow, 6))
            .FormulaR1C1 = "=ROUND(RC5*RC7,2)"
            .Value = .Value
        End With
    End If
End Sub

 

[Orfevre]

Try this on a copy of your data:
EDITED

Option Explicit
Sub Indiv_Formulas()
    Dim ws As Worksheet
    Set ws = Worksheets("Indiv")
   
    Dim LRow As Long
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
   
    Dim c As Range, i As Long
    For Each c In ws.Range(ws.Cells(3, 6), ws.Cells(LRow, 6))
        If c = "" Then
            i = c.Row
            Exit For
        End If
    Next c
   
    If i > 0 Then
        With ws.Range(ws.Cells(i, 6), ws.Cells(LRow, 6))
            .FormulaR1C1 = "=ROUND(RC5*RC7,2)"
            .Value = .Value
        End With
    End If
End Sub

Absolutely brilliant thank you, I was playing around with the FormulaR1C1 but was struggling with it.

 

[kevin9999]

Absolutely brilliant thank you, I was playing around with the FormulaR1C1 but was struggling with it.

Happy to help and thanks for the feedback

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA formula in first blank and filled down to end of data
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.