Formula Speed Help

[Swifty87]

Hey Guys,

I am trying to get a file to update quicker I can have >100000 rows and would like to speed up the formula, the one i am having problems with is this

I don't have any experience writing a VBA Array to complete a formula is this something that can be done? or not in this case

I have tried to speed things up doing the below but it still takes too long.

Sub Formula_ImportC()
'
' Formula_Import Macro
' Run aditional calculations on SQL Data test delete after 24/06
'
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim lastRow&
Dim rmiWS As Worksheet
Set rmiWS = Sheets("SQL DATA")

lastRow = rmiWS.Cells(rmiWS.Rows.Count, "A").End(xlUp).row

With rmiWS
    .Range("AE2:AE" & lastRow).FormulaR1C1 = "=IF(RC[-27]=0,COUNTIF(R1C32:R[-1]C[1],RC[1]),R[-1]C)"
End With

'FormulaR1C1

 Application.ScreenUpdating = True
 Application.Calculation = xlCalculationAutomatic
 
End Sub

Another option might be to limit how far back this formula will look rather than always going back to Cell AE2 once the formula gets to AE100 for eg it would move to AE3, and so on

=IF(D3=0,COUNTIF($AF$1:AF2,AF3),AE2)

Any And all help greatly appreciated, even if not possible

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet (a dozen or so rows should be enough). Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Swifty87]

Hey mumps,

please see the link below, hopefully, this works
https://app.box.com/s/ggiz96dmvj4xbzc2kkrny9uynzi8yffd

What I am trying to do is the following:

1. In AE put the following formula, if the cell on D:D = 0
2. count the number of times the value in AF repeats working back to the start of AF

So in cell AE124 it would check how many times the values in the range AF2:AF123 appears. and if D124>0 then return AE123

But with the number of rows over 200k in some cases in the main file, it is taking too long to update, I am thinking if it is possible to limit how far the formula counts back to 200 rows.
So eg. in cell AE300 it would count how many times the value in cell AF300 appears in the range AF299:AF99

I believe you can add formula via a VBA Array? and is supposed to be a lot quicker? is this possible with the above and how would one do that?

Really keen to learn as what I am creating is a file that is linked to a SQL Data lake that the user can refresh the data on but right now it is taking too long to update with the formulas updating

Thanks for your help

 

[mumps]

This macro took about 20 seconds to run on my computer on the file you posted. There may be a faster way but I'm not sure at this point.

Sub CountVals()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("D3", Range("D" & Rows.Count).End(xlUp)).Resize(, 29).Value
    For i = 2 To UBound(v)
        If v(i, 1) = 0 Then
            Range("AE" & i + 1) = WorksheetFunction.CountIf(Range("AF2:AF" & i), v(i - 1, 29))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Swifty87]

This macro took about 20 seconds to run on my computer on the file you posted. There may be a faster way but I'm not sure at this point.

Sub CountVals()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("D3", Range("D" & Rows.Count).End(xlUp)).Resize(, 29).Value
    For i = 2 To UBound(v)
        If v(i, 1) = 0 Then
            Range("AE" & i + 1) = WorksheetFunction.CountIf(Range("AF2:AF" & i), v(i - 1, 29))
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Hey Mumps!! Amazing thank you for sending this, it is almost there slight amendments if possible, I tried to alter it myself but did not quite manage!!

it looks like it is returning the count one cell too far up it should return the count on the same row where it finds a 0 in column D:D
Also, can we add in an else to return the value one cell up in column EA:EA if the Value in D:D is greater than 0 so for eg., in AE100 D100=2 so it would return AE99? As of right now, it is checking If the argument is true

If v(i, 1) = 0 Then

And skipping to Next I if D is >0.

Thanks again I really appreciate it

 

[mumps]

can we add in an else to return the value one cell up in column EA:EA if the Value in D:D is greater than 0 so for eg., in AE100 D100=2 so it would return AE99

I made a slight change to the code. After running the version below, D19 is the first cell in column D which is greater than zero. It is equal to 1. AE18 is equal to 0. Do you want AE19 to be equal to 0 ? Please clarify in detail.

Sub CountVals()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("D2", Range("D" & Rows.Count).End(xlUp)).Resize(, 29).Value
    For i = 1 To UBound(v)
        If v(i, 1) = 0 Then
            If i = 1 Then
                Range("AE2") = 0
            Else
                Range("AE" & i + 1) = WorksheetFunction.CountIf(Range("AF2:AF" & i), v(i, 29))
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Swifty87]

I made a slight change to the code. After running the version below, D19 is the first cell in column D which is greater than zero. It is equal to 1. AE18 is equal to 0. Do you want AE19 to be equal to 0 ? Please clarify in detail.

Sub CountVals()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("D2", Range("D" & Rows.Count).End(xlUp)).Resize(, 29).Value
    For i = 1 To UBound(v)
        If v(i, 1) = 0 Then
            If i = 1 Then
                Range("AE2") = 0
            Else
                Range("AE" & i + 1) = WorksheetFunction.CountIf(Range("AF2:AF" & i), v(i, 29))
            End If
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Hey,

Yes, I would be looking for AE19 to = 0. I added a column in AI that shows the values I am looking for it to return generated by the original formula

thanks again

 

[mumps]

Try:

Sub CountVals()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("D2", Range("D" & Rows.Count).End(xlUp)).Resize(, 29).Value
    For i = 1 To UBound(v)
        If v(i, 1) = 0 Then
            If i = 1 Then
                Range("AE2") = 0
            Else
                Range("AE" & i + 1) = WorksheetFunction.CountIf(Range("AF2:AF" & i), v(i, 29))
            End If
        Else
            Range("AE" & i + 1) = Range("AE" & i + 1).Offset(-1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[Swifty87]

Try:

Sub CountVals()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long
    v = Range("D2", Range("D" & Rows.Count).End(xlUp)).Resize(, 29).Value
    For i = 1 To UBound(v)
        If v(i, 1) = 0 Then
            If i = 1 Then
                Range("AE2") = 0
            Else
                Range("AE" & i + 1) = WorksheetFunction.CountIf(Range("AF2:AF" & i), v(i, 29))
            End If
        Else
            Range("AE" & i + 1) = Range("AE" & i + 1).Offset(-1)
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

I owe you a drink kind sir, that worked perfectly!!! I will try to digest what you have done and see if I can do it with another formula!

Many Thanks

 

[mumps]

My pleasure.