malleshg24
10-03-2019, 08:05 PM
Hi Team,
Need your help for making countif formula dynamic.Criteria will be from Range("E2:E" & LastRow)
Range is Column A Which Contains products list.
Below line works.
MsgBox Application.Sum(Application.CountIf(r, Array("FX Option", "Forward")))
This line is not working.
'MsgBox Application.Sum(Application.CountIf(r, Array(" & arr & "))
Below are my attempted Code.
Sub Test()
Dim r As Range
Dim lastrow As Long
Dim arr As Variant
'arr = Array("FX Option", "Forward")
lastrow = Range("e1000").End(xlUp).Row
arr = Range("E2:E" & lastrow).Value
Set r = Sheet1.Range("A1", Range("A" & Rows.Count).End(xlUp))
'MsgBox Application.Sum(Application.CountIf(r, Array(" & arr & "))
MsgBox Application.Sum(Application.CountIf(r, Array("FX Option", "Forward")))
End Sub
Plz help, Thanks in advance for your precious time.
Regards,
mg
Need your help for making countif formula dynamic.Criteria will be from Range("E2:E" & LastRow)
Range is Column A Which Contains products list.
Below line works.
MsgBox Application.Sum(Application.CountIf(r, Array("FX Option", "Forward")))
This line is not working.
'MsgBox Application.Sum(Application.CountIf(r, Array(" & arr & "))
Below are my attempted Code.
Sub Test()
Dim r As Range
Dim lastrow As Long
Dim arr As Variant
'arr = Array("FX Option", "Forward")
lastrow = Range("e1000").End(xlUp).Row
arr = Range("E2:E" & lastrow).Value
Set r = Sheet1.Range("A1", Range("A" & Rows.Count).End(xlUp))
'MsgBox Application.Sum(Application.CountIf(r, Array(" & arr & "))
MsgBox Application.Sum(Application.CountIf(r, Array("FX Option", "Forward")))
End Sub
Plz help, Thanks in advance for your precious time.
Regards,
mg