I am trying to write a code that will sum varying ranges of data. I have it so close but am getting stuck on one thing. I need the code to put the formula in the last cell with a value vs the first blank cell.
Here is the code I'm using and attached is an example of the data I'm working with. I'm new to VBA so I'm likely missing something very obvious, at least I hope so
Sub SumF()
Dim y As Variant
Dim firstRow As Variant
Dim lastRow As Variant
lastRow = Range("F" & Rows.Count).End(xlUp).Row
firstRow = Cells(lastRow, 6).End(xlUp).Row
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 6).Formula = "=SUM(F" & firstRow & ":F" & lastRow & ")"
End If
For y = firstRow To 6 Step -1
lastRow = Cells(y, 6).End(xlUp).Row
firstRow = Cells(lastRow, 6).End(xlUp).Row
If firstRow < 1 Then firstRow = 1
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 6).Formula = "=SUM(F" & firstRow & ":F" & lastRow & ")"
End If
y = firstRow
If firstRow = 6 Then Exit Sub
Next y
End Sub
Here is the code I'm using and attached is an example of the data I'm working with. I'm new to VBA so I'm likely missing something very obvious, at least I hope so
Sub SumF()
Dim y As Variant
Dim firstRow As Variant
Dim lastRow As Variant
lastRow = Range("F" & Rows.Count).End(xlUp).Row
firstRow = Cells(lastRow, 6).End(xlUp).Row
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 6).Formula = "=SUM(F" & firstRow & ":F" & lastRow & ")"
End If
For y = firstRow To 6 Step -1
lastRow = Cells(y, 6).End(xlUp).Row
firstRow = Cells(lastRow, 6).End(xlUp).Row
If firstRow < 1 Then firstRow = 1
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
Cells(lastRow + 1, 6).Formula = "=SUM(F" & firstRow & ":F" & lastRow & ")"
End If
y = firstRow
If firstRow = 6 Then Exit Sub
Next y
End Sub