VB script for Looping Formula

[dawsman]

I have been compiling a large excel db of data which currently stands at 365k rows and will grow substantially.

I am trying to copy a formula in a new column and understandably its crashing excel, so I thought creating a macro and getting it to copy the formula in smaller parts wouldn't stress it out too much (I could also set and forget it). Macro completed but I cant figure out how to loop it, I've read the MS guide and as a complete novice I cant figure it out. I have ordered a book from Amazon (on VBA and Access) but need this data ready this week.

Sub Macro3()
'
' Macro3 Macro
'

'
ActiveCell.Offset(-25, 0).Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Range("A1:B1").Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:B63"), Type:= _
xlFillDefault
ActiveCell.Range("A1:B63").Select
End Sub

The macro is looking for an empty cell in column S&T and is copying two formulas down, be good for it to loop up until there is a space column R or by repetition (say 10 times).

Any help would be greatly received.

 

[gaj104]

Wouldn't a database solution be a better idea, such as Access, especially if your records are due to grow?

 

[sous2817]

Stil getting a '424' error saying object required. I even renamed the tab sheet 1.

I get the feeling excel just doesn't want to work with me on this

Try this:

Sub test()
Dim LR As Long, i As Long

With Application
    .ScreenUpdating = False
End With

LR = ActiveSheet.UsedRange.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

For i = 2 To LR
    
    With Sheets("[B][I]YOUR SHEET NAME HERE BETWEEN THE QUOTES[/I][/B]")
        .Range("S" & i).Value = .Evaluate("IF(SUMPRODUCT(($B$2:$B" & i & "=A" & i & ")*($T$2:$T" & i & "=S" & i & "))>1,0,1)")
    End With
Next i

With Application
    .ScreenUpdating = True
End With
End Sub