Using a Formula with VBA and setting the formula to expand to new rows of data when added

[GRL87]

Hi

I've got a VBA formula which works fine from O2-O700 but not being an experienced VBA writer I'm struggling to find out how to make the formula expand to rows O701 and beyond once new data has been added, is anyone able to help please? Thank you

Sub FloodFormula()
'
' FloodFormula Macro
'

'
Sheets("Student List").Select
Range("O2").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(UPN,UPN_list)"
Range("O2").Select
Selection.AutoFill Destination:=ActiveSheet.Range("$O$2:$O$700")
ActiveSheet.Range("$O$2:$O$700").Select

End Sub

 

[Cubist]

Which columns are the new data being added to ?

 

[GRL87]

Column O, I'd got it as far as to autofill column O with the right formula - that works but I don't know how to make it go to the last row each time its ran

 

[Cubist]

Yes, I see you're auto-filling column O, but that's not what I asked. You said new data are being added and you're autofilling to match the last row of the new data. I'm asking which columns are the new data being added.

 

[GRL87]

A-N are being added

 

[Cubist]

Try this on a copy.

Sub FloodFormula()
    Dim ws As Worksheet
    Dim LastCell As Range
    
    Set ws = ThisWorkbook.Sheets("Student List")
    
    Set LastCell = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
    ws.Range("O2").FormulaR1C1 = "=COUNTIF(UPN,UPN_list)"
    
    ws.Range("O2").AutoFill Destination:=ws.Range("O2:O" & LastCell.Row)
End Sub

 

[GRL87]

Thank you, this states 'Error 9 : Subscript out of range' - any idea?

 

[Cubist]

Which line did it highlight in yellow and stop the code?

 

[GRL87]

It now stops it on the code prior to the one you'd just given, this is the whole thing:

Sub CopyData()
'
' CopyData Macro
'

'
Sheets("data").Select
Columns("A:N").Select
Selection.Copy
Sheets("Student List").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Range("$A$1:$N$2594").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6 _
, 7, 8, 9, 10, 11, 12, 13, 14), Header:=xlYes
End Sub

Sub FloodFormula()
Dim ws As Worksheet
Dim LastCell As Range

Set ws = ThisWorkbook.Sheets("Student List")

Set LastCell = ws.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

ws.Range("O2").FormulaR1C1 = "=COUNTIF(UPN,UPN_list)"

ws.Range("O2").AutoFill Destination:=ws.Range("O2:O" & LastCell.Row)
End Sub

 

[Cubist]

Ok, if it stops before the new code then it's not related to the new code I've given.
Re-run the first one again. Hit debug. See which line highlights yellow.