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.

 

[dawsman]

Well I tried leaving the machine on overnight running the macro but it just freezes up (not responding), happened both on the 350k row data set and a 150k row data set.

Even tried it on my work machine which is a beast and can handle (I thought) anything, I guess the fault is with excel rather than the machine.

So, I'm not sure of a way around this other than setting up an access/mysql DB. Real shame as counting uniques in the PT would have been killer for a dashboard I'm building.

Thanks for your help though guys.

 

[sous2817]

What's the formula you're using? Rather than trying the fill down function, maybe it'd be better to apply the formula all at once, or perhaps there's another way to accomplish what you're trying to do without applying 350k+ formulas.

 

[dawsman]

The formula is

=IF(SUMPRODUCT(($B$2:$B2=A2)*($T$2:$T2=S2))>1,0,1)

This way I can add the column to my PT and count the unique items. It works perfectly on smaller ranges!

I have tried copying the formula in one go but that makes excel fall over as well.

 

[sous2817]

And that formula goes in S2 and is copied down?

 

[dawsman]

Yes, column B contains the month (each row is 1 day starting from Jan 1st 2010) and column T contains the items that I need count.

The same item appears more than once throughout the month so the formula is to just count individual items once within that period.

E.g. if the item appeared every day within the data for Jan the PT counts it as 31, with this formula I can count it as just once.

Hope that makes sense.

 

[sous2817]

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 Sheet1
        .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

On my machine, it went thorough 109k rows in just under 38 minutes. Maybe try it on a subset (like 50k rows) to see if it gives the intended results.

Let me know if it helps at all.

 

[dawsman]

Hi sous2817, I'm getting a 424 run time error its stating that an objects-required?

 

[sous2817]

Try changing Sheet1 to whatever sheet the formula needs to run on.

 

[dawsman]

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

 

[sous2817]

Man that is frustrating! If you're able to, PM me and I'll send you my email address. If you can send me a representative sample of your data, I'll try and get it to work on my end.