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...
"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...