CODEWITHSUNDEEP
excelvba
Selrac
Selrac

Reputation: 2293

VBA get the max number based on condition

I have some data in Excel where I have to find the max number in a column so that I can increment it by one. The the numbers are as text like 001. There are also three reference columns I have to consider to identify the max number.

Here is an example of the data

A    K    L    N   <-These are the columns 
Ref1 Ref2 Ref3 ID  <-These are the headers
xxx  I    17   001 <-Column N is also text 
xxx  I    17   002
yyy  J    15   001
xxx  I    17   005 <-The numbers might not be consecutive. Instead of 003 jumps to 005

Based on the reference XXX-I-17 I have to find that the next ID is 006. Note that the reference is not always consecutive as indicated above.

What I'm doing is first to filter the data like:

LastRow = ActiveSheet.Range("A1").Offset(ActiveSheet.Rows.Count - 1, 0).End(xlUp).Row
ActiveSheet.Range("A1:L" & lastRow).AutoFilter Field:=1, Criteria1:="xxx"
ActiveSheet.Range("A1:L" & lastRow).AutoFilter Field:=11, Criteria1:="I"
ActiveSheet.Range("A1:L" & lastRow).AutoFilter Field:=11, Criteria1:="17"

I'm not sure if filtering the data is the best approach, but what can not figure out is how to find the max value in column N so that I can increment it.

Any ideas? Would it work better with formulas?

Upvotes: 1

Views: 2367

Answers (2)

A.S.H
A.S.H

Reputation: 29332

In VBA, you can do it this way:

Function NextN(a As String, k As String, l As Long) As Long
NextN = 1 + Application.Evaluate("MAX(IF((A:A=""" & a & """)*(K:K=""" & k & """)*(L:L=" & l & "),INT(N:N)))")
End Function

You can also use that formula directly in excel after some syntax adjustment, and using it as a array formula (CSE). Otherwise you can also use this function as as a User-defined function.

You can also make it faster by limiting the columns to some size (i.e. A1:A1000 instead of A:A etc..)

Sub ExampleTest()
    dim n as long
    n = NextN("XXX", "I", 17)
    Debug.Print n ' Should print 6 with your OP's data
End Sub

Upvotes: 1

Selrac
Selrac

Reputation: 2293

Thanks to Scott comment I found what I was looking for. In case it helps someone else, with the following formula I can find the next incremental text value

=TEXT(COUNTIFS($A$1:A2,A2,$K$1:K2,K2,$L$1:L2,L2)+1,"000‌​")

Upvotes: 0

Related Questions