hi, Abdul !
I am using sumif worksheet function in sheet I would like to give an example here...
Apples 100
Apples 100
Mangos 150
Apples 10
I am using this formula in cell B5: =Sumif(A1:A4,A5,B1:B4)
I just want that when I press Ctrl + [ in my sumif holding formula
it select only those cell that meets my given criteria specification
rather select the range A1:B5. is there any way to this
(i.e.) the following sub does what you ask (selects only the matching cells in B1:B4)
you will need to assign "the sub" to any short-cut key (i.e. in your workbook_open event)
and release "the short-cut key" (i.e. in your workbook_beforeclose event) -???-
Sub myMatchingSumif()
Dim theFunction As String, theCriteria As String, theCondition As String, _
theResults As String, theRange As Range, n As Integer, Temp As String, Tmp
If ActiveCell.HasFormula Then theFunction = ActiveCell.Formula Else Exit Sub
If Mid(theFunction, 2, 5) <> "SUMIF" Then Exit Sub
Temp = Mid(theFunction, 8, Len(theFunction) - 8)
Tmp = Split(Temp, ",")
theCriteria = Tmp(LBound(Tmp))
theCondition = Tmp(LBound(Tmp) + 1)
theResults = Tmp(LBound(Tmp) + 2)
With Range(theCriteria)
For n = 1 To .Count
If .Cells(n) = Range(theCondition) Then
Set theRange = Union(IIf(theRange Is Nothing, _
Range(theResults).Cells(n), theRange), Range(theResults).Cells(n))
End If
Next
End With
If Not theRange Is Nothing Then theRange.Select: Set theRange = Nothing
End Sub
hth,
hector.
*IF* you need this sub for xl-97 (VBA5 doesn't support split-vba function)...
change this:
Tmp = Split(Temp, ",")
to this:
Tmp = Evaluate("{""" & Application.Substitute(Temp, ",", """,""") & """}")
