×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Excel VBA - array match with multiple criteria

Excel VBA - array match with multiple criteria

Excel VBA - array match with multiple criteria

(OP)
I have a large table, from which I want to pull out the maximum value from a particular column, only if the values in other rows match up to certain criteria. As an example, I want to pull out the maximum value in "COLF", but only for rows that match the following criteria:

"COLA" value begins with 'BS'...I don't care about the last characters.
"COLB" value is equal to 'DW'.
...so, for the table below, I want to get back 0.637 as a result:

COLA____COLB____COLC____COLD________COLF
----------------------------------------
BNA1____0.75____DC____LinStatic________8.105
BNA1____2.44____DC____LinStatic________8.105
BSA1____2.44____DC____LinStatic________3.581
BSA1____3.66____DC____LinStatic________3.581
BNA2____4.88____DC____LinStatic________3.581
BNA3____0.75____DW____LinStatic________1.307
BNA3____2.44____DW____LinStatic________1.307
BSA1____2.44____DW____LinStatic________0.637
BNA1____3.66____DW____LinStatic________0.637
BNA1____4.88____DW____LinStatic________0.637

In an attempt to do this, I threw together a function that's based on simpler code that someone gave to me, but I am extremely novice when it comes to VBA and am having problems:
------------------------------
Function Maxifandif(value1 As String, value2 As String)
'
Application.Volatile True ' Ensure that function will automatically recalculate
'
' Declare the Variables to be used
'
Dim lookrange1 As Range ' this is a single column array that the function looks for matches in
Dim lookrange2 As Range ' this is a single column array that the function looks for matches in
Dim returnrange As Range ' this is the single column array that the result is returned from
Dim lngCount As Long
Dim varArray()
'
' Assign Values to Required Variables
'
Set lookrange1 = Sheets("Test1").Range("C1:C10")
Set lookrange2 = Sheets("Test1").Range("A1:10")
Set returnrange = Sheets("Test1").Range("F1:F10")
'
' Assemble a temporary Array from rows that match the criteria (below)
'
ReDim varArray(1 To Application.WorksheetFunction.CountIf(lookrange1, value1))
lngCount = 1
For Each acell In returnrange
If lookrange1.EntireColumn.Cells(acell.Row) = value1 _
And lookrange2.EntireColumn.Cells(acell.Row) = value2 _
Then
varArray(lngCount) = acell
lngCount = lngCount + 1
End If
Next acell
Maxifandif = Application.WorksheetFunction.Max(varArray)
End Function
------------------------------
Due to the size of my actual table and the variation in the criteria that I have to look at, I am trying to make a user-defined function to do this. But I'm running into a wall...

If I set "value2 = BSA1", then the function works fine, but I can not figure out how to make the function work when "value2 = BS*" (where * would be some sort of wildcard character) and I can't figure out how to chop-off the trailing characters from the value of "acell" within the "For Each" loop.

Any input or suggestions would be appreciated...

RE: Excel VBA - array match with multiple criteria

I don't think you need to resort to VBA to achieve this.
An "array expression" should be able to be used.
Something along the lines of
=MAX((LEFT(A8:A19,2)="BS")*(C8:C19="DC")*F8:F19)
entered as an "array formula" [Ctrl-Shift-Enter].

(But it gets more complicated if you can have non-positive numbers in column F.)

RE: Excel VBA - array match with multiple criteria

(OP)
I was trying to avoid using the built-in functions, because I was working across multiple worksheets (plus I am working with both positive and negative values) and this was making the equations extremely long and difficult to review/update.

And FYI, I actually solved my original post by using the "like" operator instead of "=":

If lookrange1.EntireColumn.Cells(acell.Row) Like value1 _
And lookrange2.EntireColumn.Cells(acell.Row) Like value2 _

...that seems to have done the trick.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close