VBA Evaluate Issue

[lc2189]

Hi All,

I'm having some trouble speeding up my VBA code. Below is the current code I have that works perfectly, but takes a while to fill down and calculate.

Dim lastRow As Long
Dim strFormulas(1 To 3) As Variant
Dim sh As Worksheet


lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With ThisWorkbook.Sheets("Agreement Product Print")
    strFormulas(1) = "=VLOOKUP(RC[-2],Sheet2!C[-54]:C[-53],2,FALSE)"
    strFormulas(2) = "=VLOOKUP(RC[-2],Sheet2!C[-55]:C[-54],2,FALSE)"
    strFormulas(3) = "=RC[-56]&RC[-16]"
'
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
    Range("BC16:BE16").Formula = strFormulas
End With
    Range("BF16").Select
        Selection.FormulaArray = "=MIN(IF(RC57:R100000C57=RC[-1],RC56:R100000C56))"
    Range("$BC$16:$BE" & lastRow).FillDown
    Range("$BF$16:$BF" & lastRow).FillDown
    For Each sh In ActiveWorkbook.Sheets
        sh.Calculate
    Next sh

I tried changing it to the below, but Evaluate is giving me a hard time and giving me #Value

Dim lastRow As Long
Dim strFormulas(1 To 3) As Variant
Dim sh As Worksheet

lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
With ThisWorkbook.Sheets("Agreement Product Print")
    strFormulas(1) = "=VLOOKUP(RC[-2],Sheet2!C[-54]:C[-53],2,FALSE)"
    strFormulas(2) = "=VLOOKUP(RC[-2],Sheet2!C[-55]:C[-54],2,FALSE)"
    strFormulas(3) = "=RC[-56]&RC[-16]"
'
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = True
    Range("BC16:BE16").Formula = strFormulas
End With
With Range("BF16:BF" & lastRow)
    FormulaArray = Evaluate("MIN(IF($BE$16:$B$" & lastRow & ")=C1,$BE$16:$B$" & lastRow & ",""""))")
End With
 Range("$BC$16:$BE" & lastRow).FillDown
    For Each sh In ActiveWorkbook.Sheets
        sh.Calculate
    Next sh

hoping I can get some help on this one!

 

[JLGWhiz]

I think I would put some periods before the lines of code inside the With statements. Tends to give a more accurate result. That may not be the cause of the problem you are having, but it could cause problems.

 

[lc2189]

I think I would put some periods before the lines of code inside the With statements. Tends to give a more accurate result. That may not be the cause of the problem you are having, but it could cause problems.

Thanks! I did that, but unfortunately am still having the issue. I believe it has to do with the way the Evaluate function is written. I just can't figure out what's wrong!

Updated it a bit to the below, but still no luck.

.Value = Evaluate("MIN("IF($BE$16:$BE$" & lastRow)=BE16,"$BD$16:$BD$" & lastRow)

 

[Rick Rothstein]

Thanks! I did that, but unfortunately am still having the issue. I believe it has to do with the way the Evaluate function is written. I just can't figure out what's wrong!

Updated it a bit to the below, but still no luck.

.Value = Evaluate("MIN("IF($BE$16:$BE$" & lastRow)=BE16,"$BD$16:$BD$" & lastRow)

It is hard to tell what your underlying formula should be since it appears your IF function has no True or False arguments on top of the syntax errors. Can you write out the formula as it would appear in a cell so we can see what you are trying to implement?

 

[Kenneth Hobson]

As Rick suggested, I use this method to check that my string built formula matches the manual built formula. In the comment before s, you can see what the results of Debug.Print shows after a Run in VBE's Immediate window. Obviously, the syntax of the formula string s is incorrect.

Sub Main()
  Dim lastRow As Long, FormulaArray, s As String
  lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
  'MIN(IF($BE$16:$B$5)=C1,$BE$16:$B$5,""))
  s = "MIN(IF($BE$16:$B$" & lastRow & ")=C1,$BE$16:$B$" & lastRow & ",""""))"
  Debug.Print s
  FormulaArray = Evaluate(s)
End Sub

 

[lc2189]

It is hard to tell what your underlying formula should be since it appears your IF function has no True or False arguments on top of the syntax errors. Can you write out the formula as it would appear in a cell so we can see what you are trying to implement?

Hi Rick! Below is the original formula, but I was trying to change it to take the last row into account. I don't have a false condition in place because it should never be false. I can put a blank or zero in there though if that makes it easier.

Basically the formula is checking a concatenate formula to see if there are any duplicates. If there are duplicates, it is returning the minimum date out of the duplicates.

=MIN(IF($BE16:$BE$100000=BE16,$BD16:$BD$100000))

 

[Rick Rothstein]

Hi Rick! Below is the original formula, but I was trying to change it to take the last row into account. I don't have a false condition in place because it should never be false. I can put a blank or zero in there though if that makes it easier.

Basically the formula is checking a concatenate formula to see if there are any duplicates. If there are duplicates, it is returning the minimum date out of the duplicates.

=MIN(IF($BE16:$BE$100000=BE16,$BD16:$BD$100000))

Try your original code line like this then...

.Value = Evaluate("MIN(IF(BE16:BE" & LastRow & "=BE16,BD16:BD" & LastRow & "))")

Note that I removed the $ signs as the Evaluate function does not copy formulas anywhere, there is no need for relative or absolute addressing.

 

[lc2189]

Try your original code line like this then...

.Value = Evaluate("MIN(IF(BE16:BE" & LastRow & "=BE16,BD16:BD" & LastRow & "))")

Note that I removed the $ signs as the Evaluate function does not copy formulas anywhere, there is no need for relative or absolute addressing.

THANK YOU!! It worked now, but I have one question. It is giving me the correct output in the first cell of my range, but just copying down the same value for all cells. I would like for it to evaluate each cell in the range. Do you know how I could get it to do this? I thought if I changed .Value to .Formula it might work, but it didn't. I'm sorry, I'm still new to VBA so I'm not always sure what my changes will do.