Last Row in Excel VBA Evaluate

Question

I am using the below code:

Sub Evaluation_Formula()
    Dim i As Long
    With Worksheets("Sheet1")
        i = .Evaluate("MIN(IF((LEFT($B$1:$B$89,5)*1)=C1,$B$1:$B$89,""""))")
        .Range("F3").Value2 = i
    End With
End Sub

However, the formula is limited to B89, how can I use Last Row in Column B in the formula ?

You know how to use an esoteric function like `evaluate` but can't find last row O.o — findwindow, Dec 15 '15 at 16:15
[Find](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) the last row and store it in a variable like `lastrow` then: `i = Evaluate("MIN(IF((LEFT($B$1:$B$" & lastrow & ",5)*1)=C1,$B$1:$B$" & lastrow & ",""""))")`. Edit there is no `.` before `Evaluate` — Scott Craner, Dec 15 '15 at 16:16
Hi Scott, Thank you very much, I do know LR thingy but just couldn't understand how to use it in, as FindWindow said: esoteric, evaluate formula. Sub Evaluation_Formula_w_LR() Dim i As Long Dim LR As Long With Worksheets("Sheet1") LR = .Cells(Rows.Count, 2).End(xlUp).Row i = Evaluate("MIN(IF((LEFT($B$1:$B$" & LR & ",5)*1)=C1,$B$1:$B$" & LR & ",""""))") .Range("F10").Value2 = i End With End Sub — StarShines, Dec 15 '15 at 16:32
Yes, it works *like* a charm. How can I add code in replies here on SF btw ? I couldn't post my new code :( properly — StarShines, Dec 15 '15 at 16:35
You can't add them to the comments, either add it as and answer or back on the original post. — Scott Craner, Dec 15 '15 at 16:37

score 3 · Answer 1 · answered Dec 15 '15 at 17:14

For all intents and purposes you are really only concerned with the last number in column B, not specifically the last row. To do that in a worksheet formula you would use something like this array formula.

=MIN(IF(--LEFT(B1:INDEX(B:B, MATCH(1E+99,B:B )),5)=C1, B1:INDEX(B:B, MATCH(1E+99,B:B))))

That can translate into your VBA Evaluate method like the following.

Sub Evaluation_Formula()
    Dim i As Long
    With Worksheets("Sheet1")
        i = .Evaluate("MIN(IF(--LEFT(B1:INDEX(B:B, MATCH(1E+99,B:B )),5)=C1, B1:INDEX(B:B, MATCH(1E+99,B:B))))")
        .Range("F3").Value2 = i
    End With
End Sub

The double unary (aka double-minus or --) does the same job as multiplying the text result from the LEFT function by 1. There is no need to pass a zero-length string (e.g. "") in as the FALSE is sufficient for non-matches in the IF function. Since you are evaluating text into a formula, there is no need for the $ absolute markers.

Keep the . in .Evaluate or add the worksheet name to the cell references in the formula. Without it you run the risk of evaluating another worksheet's B1:B89 and C1 cells if Sheet1 does not hold the workbook's ActiveSheet property.

Aaahhh lovely....thanks a million Jeeped, that's one comprehensive answer. God Bless You :) — StarShines, Dec 15 '15 at 17:22
@StarShines - and thank YOU for asking a decent question that showed both research and effort on your part. — , Dec 15 '15 at 17:25

score 1 · Accepted Answer · edited Dec 15 '15 at 16:42

1

I guess this will post proper code:

Sub Evaluation_Formula_w_LR()
Dim i As Long
Dim LR As Long
With Worksheets("Sheet1")
    LR = .Cells(.Rows.Count, 2).End(xlUp).Row
    i = Evaluate("MIN(IF((LEFT($B$1:$B$" & LR & ",5)*1)=C1,$B$1:$B$" & LR & ",""""))")
    .Range("F10").Value2 = i
End With
End Sub

edited Dec 15 '15 at 16:42

Scott Craner

148,073
10
49
81

answered Dec 15 '15 at 16:38

StarShines

75
1
2
11

I made a little edit. When you can mark it as correct. – Scott Craner Dec 15 '15 at 16:43
May I know how to do that please ? :) – StarShines Dec 15 '15 at 16:46
Eventually there will be a green check mark beside the answer. click on it. – Scott Craner Dec 15 '15 at 16:47
1

btw, the empty quotes in the latter half of the IF formula are completely unnecessary and only serve to confuse the issue at hand. You can mark a self-answered post as the accepted answer after 48 hours. – Dec 15 '15 at 16:49
Which quotes you are talking about ? ...LR & ",""""))")...If I remove two of them, it gives me Type Mismatch error. – StarShines Dec 15 '15 at 17:02
The Third criteria in the if statement is not needed, If it is false then it is going to through an error regardless of the third criteria. So `Evaluate("MIN(IF((LEFT($B$1:$B$" & LR & ",5)*1)=C1,$B$1:$B$" & LR & "))")` – Scott Craner Dec 15 '15 at 17:06
Oh I got it now. Thanks for your help Scott and Jeeped :) – StarShines Dec 15 '15 at 17:14

Last Row in Excel VBA Evaluate

2 Answers2

Related